sqlite and CPU Usage

[Keith McLeod]

I am using sqlite to perform a query within an xlate.  I perform a single query per message to minimize access to the database.  What I am finding is that the CPU usage goes up to 98% with the query when processing many messages.  If I take the result of a query and manually populate the @queryresult variable and then deactivate the query.  CPU usage is nearly non existent.

Here is the skinny of the commands used.  Should I be doing something else to prevent such CPU usage?

sqlite oeDB $dbPath

set qryValue [oeDB eval “SELECT * FROM `[lindex $xlateInVals 3]` WHERE Acct_Nbr ='[lindex $xlateInVals 0]’ and Transcribed_Dt = ‘$t_date’ and Transcribed_Tm = ‘$t_time'”]

oeDB close

Topic

[David Barr]

Do you have an index on your table?

[Jeff Dinsmore]

How many rows in your table?

I’ve used SQLite from TCL for many years and it’s really quite efficient.

I’m currently doing a lookup from a 17,000 row table for each message going through several interfaces.

The database is opened, queried, and closed for each message and I’ve not seen any appreciable increase in CPU usage over the previous CL Table lookup.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Keith McLeod]

There were 655019 records in the database.  I do not have it index.  Still rusty on sqlite.  I was just happy to get the results I did until we add any kind of load which apparently didn’t take much.  I sure could use some help on this to improve the performance.  Thanks…

[Keith McLeod]

Thanks Jim

I ran the following on the database external to the xlate or proc.

CREATE INDEX  raaz ON star (Acct_Nbr,Transcribed_Dt,Transcribed_Tm);

I then changed my command to used in the xlate to include text in red.

set qryValue [oeDB eval “SELECT * FROM `[lindex $xlateInVals 3]` INDEXED BY raaz WHERE Acct_Nbr ='[lindex $xlateInVals 0]’ and Transcribed_Dt = ‘$t_date’ and Transcribed_Tm = ‘$t_time'”]

I saw an incredible performance difference.  I did not see any CPU usage for hcixlttest.  This may have done the trick

[Peter Heggie]

When I added an index to my SQLite table I also saw a significant improvement in performance, but I am not using the ‘INDEXED BY’ clause, and I am just doing a simple insert. Does anyone know if using the INDEXED BY clause is necessary or desirable?

Pete

Peter Heggie
PeterHeggie@crouse.org

[David Barr]

I don’t think that “indexed by” is necessary. I’ve got a query that I sped up by adding an index without using “indexed by”.

I wouldn’t have thought that an index would help with an insert. It helps with searching.

[Peter Heggie]

Yes, of course you are right! I guess I was just thinking about the select queries I was doing later.. once I got over a few thousand rows the select was slowing down, plus I was interfering with the real time inserts.

Pete

Peter Heggie
PeterHeggie@crouse.org

[Author]

Replies