<stab lighting="dark"> Not being faniliar with the ODBC wrapper, I can't say for sure, but could it be that the ODBC wrapper is reading the entire result set for the grid, perhaps using sqlite_get_table(), rather than just what is required? Would certainly explain the linear degredation in performance with increasing database size if the result set was reread everytime the grid was moved.
Access probably uses something more akin to the sqlite_step() functions, which would not read the entire result set if the visible grid is fully populated. </stab> Christian On Thu, 17 Jun 2004, Richard Kuo wrote: >Tim, > >We have been running into many issues with corrupt MDAC installations. >Switching to SQLite fixed this admirably. However, database access is >now visibly slower for all our users. > >I have been paying careful attention to use only one index in queries >and to make very simple queries whenever possible. I cannot imagine our >query could possibly be the source, as we are actually querying rows out >strictly by rowid only in this particular case, having moved the actual >lookup of needed rowid's outside SQLite in order to try and isolate the >performance hit that SQLite was introducing. > >I suspect some unnecessary disk access has to be the problem...despite >the small amount of new guide information being queried out, disk bytes >read is several times higher than with MS access and scrolling back over >previously accessed areas of data is visibly faster...indicating that >the disk cache is very favorably impacting the speed of the queries. > >Richard > >-----Original Message----- >From: Tim Anderson [mailto:[EMAIL PROTECTED] >Sent: Wednesday, June 16, 2004 10:33 AM >To: Richard Kuo >Subject: RE: [sqlite] SQLite performance with mid-size databases > > >> -----Original Message----- >> From: Richard Kuo [mailto:[EMAIL PROTECTED] >> Sent: 16 June 2004 05:04 >> To: [EMAIL PROTECTED] >> Subject: [sqlite] SQLite performance with mid-size databases >> >> Hi. We are using SQLite to store and retrieve data rows >> where each row is roughly 2K total in size and in a table of >> 15 columns. The total size of the database ranges from 100-300 MB. >> >> The problem we are seeing is that query and insert >> performance is unusually bad and scales up linearly with >> database size. Compared to MS Access, the query times are >> several times slower. > >Richard, > >I've done extensive comparision of SQLite vs Access and in general I'd >say SQLite is faster. But you can't get meaningful results without being >much more specific, and also testing where exactly the slowdown occurs. >I'm sure there are some queries where Access can use indexes, but SQLite >will scan the entire table or tables; obviously that's a huge perf. hit. >With careful SQL tuning you can usually find a way around it. Access >isn't a bad db engine either, as long as it's not used over a network >:-) The main advantages of SQLite are that it is: > >- smaller >- more customisable since you have the source >- no dependencies to speak of, whereas Access needs MDAC etc. >- cross-platform > >Tim > > >--------------------------------------------------------------------- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
