Friday, 15 April 2011

c# - Fetching row from SQLite database consumes 100% of processor time -


There is a DDL script that we found for a table:

  create table [ Stat_ping] ([lid] INTN CONFLICT is not at INNORE, [ping] NUMERIC (6, 2) MOU does not tap on MOU, [Tipping] DATETIME IGNORE is not a faucet on compromise;) Sample of data for the table: I »¿ 
  Lid ping tapping 2036 1 2013-02-26 10: 46: 27.000 2002 1 2013- 02- 26 10: 46: 27.000 2006 1 2013-02-26 10: 46: 27.000 2015 1 2013-02-26 10: 46: 27.000 200 9 1 2013-02-26 10: 46: 27.000 2000 1 2013-02 -26 10: 46: 27.000 2016 1 2013-02-26 10: 46: 27.000 2031 1 2013-02-26 10: 46: 27.000 2016 1 2013-02-26 10: 46: 27.000 2018 1 2013-02-26 10:46: 27.000 2012 1 2013-02-26 10: 46: 27.000 2008 1 2013-02-26 10: 46: 28.000 201 9 1 2013-02-26 10: 46: 29.000 2001 0 2013-02-26 10 : 46: 29.000 2005 1 2013-02-26 10: 46: 29.000 113 1 2013-02-26 10: 46: 30.000   

We can use this type of query to Calculates a percentage of the availability of services by:

  rhythm (Logger._lockObj) {(SQLiteConnection sql = new SQLiteConnection (configuration Mandkkknekshn strings [ "SQLite"]. ToString ())) {sql.Open (); Foreign currency (different items in cake) {item.ping.stconn = 0; Select the string sqlquery = "stat_ping (sum (s.ping) * 100 / count (s.timeping)) where datetime ('+ DateTime.Now.ToString (" yyyy-MM-dd HH: mm: ss "+ '', '- 1 day') and day-time ('' + date time.Now toastring (" yyyy-MM-dd HH: mm: ss ") +" ') and s.lid = " + Item.lid; (SQLiteCommand comm = New SQLiteCommand (using sqlquery, sql)) {item.ping.stconn = convert toByte (comm.ExecuteScalar ()); }}}}   

We use Devart SQLite data provider for contact, table stat_ping has approximately 100000 rows, so for 5 items it is calculated to be about 6- Takes 7 seconds and takes 100% processor.

I know that we should use separate stored procedure to calculate percentages for all items for just one of the stored procedures / functions, but by using a real performance and opportunity experience Could? > Another person will use my project SQLite database as the container of SOAP message logging. I think that the database can take many place, because a couple of requests and the response takes 150KB.

Thanks, guys!

Have you tried to use some index to speed up your query? I also propose to use a prepared and parameter query because it is too heavy to generate new commands in the loop.

In addition, from my experience, the SQLite database becomes very slow when there are many rows in it. We used to clean the SQLite database of old data, we used to store it in another database

BTW, MS SQL Server CE (this is an embedded in-cock database, no setup required) only Behaves very accurately when there are so many data in the table We have used the Windows service as a caching database, which has been dealt with by thousands of clients. When the size of the database exceeds 1 GB, it starts slowing down.

No comments:

Post a Comment