sqlite and CPU Usage

This topic has 7 replies, 4 voices, and was last updated 14 years, 11 months ago by Peter Heggie.

Creator

Topic
May 12, 2011 at 11:37 am #52477
Keith McLeod
Participant
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
Creator

Topic

Viewing 6 reply threads

Author

Replies
- May 12, 2011 at 6:16 pm #74373
  David Barr
  Participant
  Do you have an index on your table?
- May 13, 2011 at 6:16 pm #74374
  Jeff Dinsmore
  Participant
  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
- May 15, 2011 at 2:51 pm #74375
  Keith McLeod
  Participant
  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…
- May 15, 2011 at 8:02 pm #74376
  Keith McLeod
  Participant
  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
- May 16, 2011 at 1:16 pm #74377
  Peter Heggie
  Participant
  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
- May 16, 2011 at 5:04 pm #74378
  David Barr
  Participant
  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.
- May 16, 2011 at 5:19 pm #74379
  Peter Heggie
  Participant
  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

Viewing 6 reply threads

The forum ‘Cloverleaf’ is closed to new topics and replies.