Thanks for the reply! I'm not really trying to blame SQLite here, as I know there're limits on just how fast it can prepare a statement, execute it and give me the results - and it's fast, I'm just looking for ways to make it faster.
The reason that such a huge amount of statements needs to be executed so many times very quickly is that we have a tree built up based on those statements and that tree needs to be pruned if the results of statements are empty in real-time as the user is typing a string (a search string basically). Each node in the tree has (in my test scenario) from 1000 to 2000 children and each child has a few (up to 10-20 children of their own). There're quite a few optimizations that I've already done in the application so that unnecessary statements are not executed, but there're still 2000-3000 statements that need to be executed. And my test scenario is not that big actually - the real application is expected to handle at least 2-4 times more data regularly. I know it's pushing everything to the limits, but that's what we need to implement. Thank you for the suggestion about prepared statements - that's one thing I have looked at yet and I'll go and do some reading on it now. I'll get back with results if I succeed in implementing it. Regards, Dennis > -----Original Message----- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 27, 2007 11:31 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Questions on views > > --- Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > > Yes, after timing both I found that SELECT EXISTS is > actually a tiny > > bit faster, which does matter when multiplied by thousands of > > executions in a row. > > > > Unfortunately, I still cannot get it as fast as I want - it takes > > approximately 1500-2000 ms per approximately 2000-3000 > executions. Is > > there any way to speed this up even further somehow? The > scenario is > > that I have a table full of SQL statements that need to be > reexecuted > > often to check whether they return any results or not (from other > > tables). I have all those SQL statements in memory in the > application, > > so that saves a bit of time, but can I do anything else? > > 0.6 milliseconds per query is not fast enough? Wow! > What's your system doing that it needs to poll the database so often? > > Unless you want to redesign your application, there's not > much you can do except eliminate the parsing overhead. > > In the table where you store the SQL statements, create a > column to hold the MD5 hash value of the SQL and use that as > a key to an in-memory hash map of prepared statements, where > you create the prepared statement and insert it into the map > with the MD5 value as its key if it does not exist. Keep in > mind that your prepared statements are tied to the connection > on which they were created, so if you have many connections > you will need many maps. Use sqlite3_prepare_v2(). > > > > ______________________________________________________________ > ______________________ > TV dinner still cooling? > Check out "Tonight's Picks" on Yahoo! TV. > http://tv.yahoo.com/ > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
