Oops - that last sentence I wrote actually doesn't make sense :) I know what prepared statements are as I'm using them (doh!). I might have a problem that I need to add more WHERE conditions to those "basic" statements, which wouldn't work probably with storing them, as I can't possible know all possible combinations. Still - I'll give this some thought to see if I can find something in this direction.
Thanks again. > -----Original Message----- > From: Dennis Volodomanov > Sent: Tuesday, March 27, 2007 11:43 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Questions on views > > 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] > -------------------------------------------------------------- > --------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
