Re: [sqlite] Questions on views
"Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > Ok, thank you for clarifying those points to me. > > At the moment I'm struggling to understand why running a > > SELECT ROWID FROM TableA WHERE colA='a' > > Takes almost twice as long as > > SELECT ROWID FROM TableA WHERE colA='b' > > Running on the same table with the same data with colA indexed. > Unfortunately, the real queries are not as simple, although still the > same between each other, just different data. I'm still in the process > of singleing out the query that is different (using EXPLAIN), so as soon > as I know which it is I'll be able to study it and see how can I change > it. > Run ANALYZE then look at the lines of sqlite_stat1 that pertain to TableA. I'm guessing you will find that colA is not very selective. That is to say, a large fraction (perhaps half) of the rows in tableA have 'a' as their value for colA. If requesting more than just ROWID in the return set, this can result in a significant slowdown. Running ANALYZE might give the query optimizer enough clues to figure that out, by the way. So try rerunning the query after you run ANALYZE and see if it helps. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
Ok, thank you for clarifying those points to me. At the moment I'm struggling to understand why running a SELECT ROWID FROM TableA WHERE colA='a' Takes almost twice as long as SELECT ROWID FROM TableA WHERE colA='b' Running on the same table with the same data with colA indexed. Unfortunately, the real queries are not as simple, although still the same between each other, just different data. I'm still in the process of singleing out the query that is different (using EXPLAIN), so as soon as I know which it is I'll be able to study it and see how can I change it. Regards, Dennis > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 10:59 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > "Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > > Thank you for the reply. > > > > > > One more question that I was pondering upon - does the > > > order of rows > > > > and their placement in the table matter at all when > using indexes > > > > (provided indexes are used correctly)? > > > > > > I don't really understand the question... > > > > What I meant was - if I have a table like: > > > > CREATE TABLE TableA (colA, colB, colC, ..., colZ) > > > > And I do a: > > > > SELECT ROWID FROM TableA WHERE colZ='z' > > > > Would it be faster if I had instead: > > > > CREATE TABLE Table (colA, colZ, colB, colC, ...) > > Yes. SQLite has to scan through all the tables in order > until it finds the one you asked for. So put all of your > large strings and big BLOBs at the end so that you won't have > scan past them to find that boolean you were looking for. > > > > > And if yes, would it still matter if colZ was indexed? > > Not in this case, no. > > > > > Would it make sense (if I understood correctly) to create several > > indexes with different column combinations, such as: > > > > CREATE INDEX IndexA ON TableA (colA, colB) CREATE INDEX IndexB ON > > TableA (colB, colC) CREATE INDEX IndexC ON TableA (colA, colB, colC) > > IndexA is not needed. IndexC does everything that IndexA can do. > > > > > So that would cover all possible lookups such as: > > > > SELECT EXISTS (SELECT ROWID FROM TableA WHERE colA='a' AND colC='c') > > This would use IndexC on the colA='a' constraint only. But > because all the information it needs is contained in indexC, > it would never have to reference TableA. > > > SELECT EXISTS (SELECT ROWID FROM TableA WHERE colB='b' AND colC='c') > > This will use IndexB on both colB='b' and colC='c'. And > tableA is never consulted. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
"Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > Thank you for the reply. > > > > One more question that I was pondering upon - does the > > order of rows > > > and their placement in the table matter at all when using indexes > > > (provided indexes are used correctly)? > > > > I don't really understand the question... > > What I meant was - if I have a table like: > > CREATE TABLE TableA (colA, colB, colC, ..., colZ) > > And I do a: > > SELECT ROWID FROM TableA WHERE colZ='z' > > Would it be faster if I had instead: > > CREATE TABLE Table (colA, colZ, colB, colC, ...) Yes. SQLite has to scan through all the tables in order until it finds the one you asked for. So put all of your large strings and big BLOBs at the end so that you won't have scan past them to find that boolean you were looking for. > > And if yes, would it still matter if colZ was indexed? Not in this case, no. > > Would it make sense (if I understood correctly) to create several > indexes with different column combinations, such as: > > CREATE INDEX IndexA ON TableA (colA, colB) > CREATE INDEX IndexB ON TableA (colB, colC) > CREATE INDEX IndexC ON TableA (colA, colB, colC) IndexA is not needed. IndexC does everything that IndexA can do. > > So that would cover all possible lookups such as: > > SELECT EXISTS (SELECT ROWID FROM TableA WHERE colA='a' AND colC='c') This would use IndexC on the colA='a' constraint only. But because all the information it needs is contained in indexC, it would never have to reference TableA. > SELECT EXISTS (SELECT ROWID FROM TableA WHERE colB='b' AND colC='c') This will use IndexB on both colB='b' and colC='c'. And tableA is never consulted. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
Thank you for the reply. > > One more question that I was pondering upon - does the > order of rows > > and their placement in the table matter at all when using indexes > > (provided indexes are used correctly)? > > I don't really understand the question... What I meant was - if I have a table like: CREATE TABLE TableA (colA, colB, colC, ..., colZ) And I do a: SELECT ROWID FROM TableA WHERE colZ='z' Would it be faster if I had instead: CREATE TABLE Table (colA, colZ, colB, colC, ...) And if yes, would it still matter if colZ was indexed? > > CREATE INDEX indexA on tableA (columnA, columnB, columnC) SELECT > > EXISTS (SELECT ROWID from tableA WHERE columnA='a' AND > > columnB='b') > > > > The indexA index will be used to resolve the query above, and > nothing else. The tableA table is never consulted. > > To resolve the query above, SQLite does a binary search > through indexA looking for an entry that matches columnA and columnB. > It then returns 1 or 0 on success or failure. tableA does > not come into plan. Would it make sense (if I understood correctly) to create several indexes with different column combinations, such as: CREATE INDEX IndexA ON TableA (colA, colB) CREATE INDEX IndexB ON TableA (colB, colC) CREATE INDEX IndexC ON TableA (colA, colB, colC) So that would cover all possible lookups such as: SELECT EXISTS (SELECT ROWID FROM TableA WHERE colA='a' AND colC='c') SELECT EXISTS (SELECT ROWID FROM TableA WHERE colB='b' AND colC='c') Or is that redundant and wouldn't work properly (size on disk and insert/delete speed doesn't matter to me)? Thank you! Dennis > -Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 10:31 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > "Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > > One more question that I was pondering upon - does the > order of rows > > and their placement in the table matter at all when using indexes > > (provided indexes are used correctly)? > > I don't really understand the question... > > > > > Oh, and another :) Would such a statement use both indexes > or just one? > > > > CREATE INDEX indexA on tableA (columnA, columnB, columnC) SELECT > > EXISTS (SELECT ROWID from tableA WHERE columnA='a' AND > > columnB='b') > > > > The indexA index will be used to resolve the query above, and > nothing else. The tableA table is never consulted. > > To resolve the query above, SQLite does a binary search > through indexA looking for an entry that matches columnA and columnB. > It then returns 1 or 0 on success or failure. tableA does > not come into plan. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
"Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > One more question that I was pondering upon - does the order of rows and > their placement in the table matter at all when using indexes (provided > indexes are used correctly)? I don't really understand the question... > > Oh, and another :) Would such a statement use both indexes or just one? > > CREATE INDEX indexA on tableA (columnA, columnB, columnC) > SELECT EXISTS (SELECT ROWID from tableA WHERE columnA='a' AND > columnB='b') > The indexA index will be used to resolve the query above, and nothing else. The tableA table is never consulted. To resolve the query above, SQLite does a binary search through indexA looking for an entry that matches columnA and columnB. It then returns 1 or 0 on success or failure. tableA does not come into plan. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
One more question that I was pondering upon - does the order of rows and their placement in the table matter at all when using indexes (provided indexes are used correctly)? Oh, and another :) Would such a statement use both indexes or just one? CREATE INDEX indexA on tableA (columnA, columnB, columnC) SELECT EXISTS (SELECT ROWID from tableA WHERE columnA='a' AND columnB='b') (reading the presentation slides at the moment) > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 1:17 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > I might not go down the threading path at all, as in theory it > > wouldn't gain me that much - I'd still need to lock/unlock the > > database for each of the threads and I think the overhead > of that plus > > the fact that it will have to wait for the other thread to do its > > stuff wouldn't gain me much if anything. > > > > I found that my indexes are not used correctly, eventhough the SQL > > statements have no inequalities on the left and all fields > are indexed. > > I guess I'll have to use the EXPLAIN to dig in and see > what's wrong. > > > > > Dennis, > > I wouldn't give up on the multiple thread idea so fast if you > have multiple CPU's to execute the queries on since you can > have multiple read queries executing in parallel on different > CPUs. Each of these readers would write their output to a > queue for a single writer thread which would run after all > the readers have finished. The writer would execute all the > database updates sequentially in a single transaction. > > If the queries take half the current execution time and the > updates take the other half, then using 4 CPUs could cut the > query time by 4 and eliminate 3/8 of the execution time. > However, if you don't have multiple CPUs the overhead will > probably slow things down overall. > > Using the correct indexes (or indicies) is very important. It > impacts the query speed directly, and having unnecessary > unused indexes increases the time it takes to do the inserts > and deletes on the indexed tables. > > SQLite will only use a single index per table per query. The > way sqlite uses indexes is explained in the slide show at > http://www.sqlite.org/php2004/page-001.html This may help you > pick the best columns to index for your application. > > HTH > Dennis Cote > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
Unfortunately I don't have access to multiple CPUs - it's a 32bit Windows application, so at best it would run on 2 CPUs at any given time, provided Windows managed those correctly in the first place :) Thanks for the link - I'll give it a read. The insert and delete speeds fully satisfy our requirements, as those are rare operations in our case - usually data will be imported in large chunks, not very often deleted and mostly browsed. > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 1:17 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > I might not go down the threading path at all, as in theory it > > wouldn't gain me that much - I'd still need to lock/unlock the > > database for each of the threads and I think the overhead > of that plus > > the fact that it will have to wait for the other thread to do its > > stuff wouldn't gain me much if anything. > > > > I found that my indexes are not used correctly, eventhough the SQL > > statements have no inequalities on the left and all fields > are indexed. > > I guess I'll have to use the EXPLAIN to dig in and see > what's wrong. > > > > > Dennis, > > I wouldn't give up on the multiple thread idea so fast if you > have multiple CPU's to execute the queries on since you can > have multiple read queries executing in parallel on different > CPUs. Each of these readers would write their output to a > queue for a single writer thread which would run after all > the readers have finished. The writer would execute all the > database updates sequentially in a single transaction. > > If the queries take half the current execution time and the > updates take the other half, then using 4 CPUs could cut the > query time by 4 and eliminate 3/8 of the execution time. > However, if you don't have multiple CPUs the overhead will > probably slow things down overall. > > Using the correct indexes (or indicies) is very important. It > impacts the query speed directly, and having unnecessary > unused indexes increases the time it takes to do the inserts > and deletes on the indexed tables. > > SQLite will only use a single index per table per query. The > way sqlite uses indexes is explained in the slide show at > http://www.sqlite.org/php2004/page-001.html This may help you > pick the best columns to index for your application. > > HTH > Dennis Cote > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
No, that's ok :) We are pretty much stuck with SQL as the application itself holds various data - the tree is just one small representation of a subset of information that we store. > -Original Message- > From: Martin Jenkins [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 27, 2007 8:59 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > > [...] > > 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). > > [...] > > Sorry to butt in (especially without knowing your > constraints) but is SQL the right approach to use here? Isn't > this the sort of thing that Patricia/radix/suffix trees are used for? > > NB: I've just got up and haven't had any tea to start the > boot process yet, so I could be talking (more) nonsense (than > usual). ;) > > Martin > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
> at least for anyone who has worked with > assembly level programming That are not many then, but thanks and will have a look at the documentation you mentioned. RBS > [EMAIL PROTECTED] wrote: >> Is there any documentation about how to analyze the results of EXPLAIN? >> Or even better is there a utility that could analyze (at least to some >> extent) the results for you? >> >> > The documentation you need is in the VDBE tutorial at > http://www.sqlite.org/vdbe.html and the opcode reference at > http://www.sqlite.org/opcode.html > > The only utility is the explain command in the sqlite shell. Use > .explain on to get a nicely formatted display and then execute > > explain ; > > Its really fairly simple (at least for anyone who has worked with > assembly level programming) to follow the execution of an SQL statement > by the virtual machine (at least for simple statements). Following the > logic of a complex statements can be challenging because there are no > human friendly text labels for branches, table and index names, or > runtime variables. > > HTH > Dennis Cote > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
[EMAIL PROTECTED] wrote: Is there any documentation about how to analyze the results of EXPLAIN? Or even better is there a utility that could analyze (at least to some extent) the results for you? The documentation you need is in the VDBE tutorial at http://www.sqlite.org/vdbe.html and the opcode reference at http://www.sqlite.org/opcode.html The only utility is the explain command in the sqlite shell. Use .explain on to get a nicely formatted display and then execute explain ; Its really fairly simple (at least for anyone who has worked with assembly level programming) to follow the execution of an SQL statement by the virtual machine (at least for simple statements). Following the logic of a complex statements can be challenging because there are no human friendly text labels for branches, table and index names, or runtime variables. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
Dennis Volodomanov wrote: I might not go down the threading path at all, as in theory it wouldn't gain me that much - I'd still need to lock/unlock the database for each of the threads and I think the overhead of that plus the fact that it will have to wait for the other thread to do its stuff wouldn't gain me much if anything. I found that my indexes are not used correctly, eventhough the SQL statements have no inequalities on the left and all fields are indexed. I guess I'll have to use the EXPLAIN to dig in and see what's wrong. Dennis, I wouldn't give up on the multiple thread idea so fast if you have multiple CPU's to execute the queries on since you can have multiple read queries executing in parallel on different CPUs. Each of these readers would write their output to a queue for a single writer thread which would run after all the readers have finished. The writer would execute all the database updates sequentially in a single transaction. If the queries take half the current execution time and the updates take the other half, then using 4 CPUs could cut the query time by 4 and eliminate 3/8 of the execution time. However, if you don't have multiple CPUs the overhead will probably slow things down overall. Using the correct indexes (or indicies) is very important. It impacts the query speed directly, and having unnecessary unused indexes increases the time it takes to do the inserts and deletes on the indexed tables. SQLite will only use a single index per table per query. The way sqlite uses indexes is explained in the slide show at http://www.sqlite.org/php2004/page-001.html This may help you pick the best columns to index for your application. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
Is there any documentation about how to analyze the results of EXPLAIN? Or even better is there a utility that could analyze (at least to some extent) the results for you? RBS > Joe Wilson wrote: >> >> "EXPLAIN SELECT ..." is also a good way to find out what the queries are >> doing >> if you're prepared to decipher the opcodes. >> >> >> > Joe, > > I had looked at the explain output and it wasn't clear which would be > faster. The count method produces less VDBE code but involves calls to > the count functions (step and final). The exists tests are done by > setting and testing integer variables in the VDBE code even though the > code is somewhat longer. Hence my recommendation to test both. > > Still, examining the explain output is generally good advice whenever > you are optimizing your SQL. > > Dennis Cote > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
Joe Wilson wrote: "EXPLAIN SELECT ..." is also a good way to find out what the queries are doing if you're prepared to decipher the opcodes. Joe, I had looked at the explain output and it wasn't clear which would be faster. The count method produces less VDBE code but involves calls to the count functions (step and final). The exists tests are done by setting and testing integer variables in the VDBE code even though the code is somewhat longer. Hence my recommendation to test both. Still, examining the explain output is generally good advice whenever you are optimizing your SQL. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
Dennis Volodomanov wrote: [...] 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). > [...] Sorry to butt in (especially without knowing your constraints) but is SQL the right approach to use here? Isn't this the sort of thing that Patricia/radix/suffix trees are used for? NB: I've just got up and haven't had any tea to start the boot process yet, so I could be talking (more) nonsense (than usual). ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
I might not go down the threading path at all, as in theory it wouldn't gain me that much - I'd still need to lock/unlock the database for each of the threads and I think the overhead of that plus the fact that it will have to wait for the other thread to do its stuff wouldn't gain me much if anything. I found that my indexes are not used correctly, eventhough the SQL statements have no inequalities on the left and all fields are indexed. I guess I'll have to use the EXPLAIN to dig in and see what's wrong. > -Original Message- > From: Jason Hawryluk [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 27, 2007 5:40 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Questions on views > > I'd be interested in knowing the performance improvements > your able to achieve from threading. I would be in debt if > you'd post the results. > > cheers > > jason > > > > -Message d'origine- > De : Dennis Volodomanov [mailto:[EMAIL PROTECTED] > Envoye : mardi 27 mars 2007 04:35 > A : sqlite-users@sqlite.org > Objet : RE: [sqlite] Questions on views > > > Yes, threading is one option I'm testing and timing as well. > > > -Original Message- > > From: Joe Wilson [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, March 27, 2007 12:29 PM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] Questions on views > > > > Assuming you're running on multi-core machines, spread the > work over a > > few threads/connections. > > > > --- Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > > > 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. > > > > > > > > > > > > __ > > __ > > Never miss an email again! > > Yahoo! Toolbar alerts you the instant new Mail arrives. > > http://tools.search.yahoo.com/toolbar/features/mail/ > > > > -- > > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > -- > > --- > > > > > > -- > -- > - > To unsubscribe, send email to [EMAIL PROTECTED] > -- > -- > - > > > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
I'd be interested in knowing the performance improvements your able to achieve from threading. I would be in debt if you'd post the results. cheers jason -Message d'origine- De : Dennis Volodomanov [mailto:[EMAIL PROTECTED] Envoye : mardi 27 mars 2007 04:35 A : sqlite-users@sqlite.org Objet : RE: [sqlite] Questions on views Yes, threading is one option I'm testing and timing as well. > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 27, 2007 12:29 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Questions on views > > Assuming you're running on multi-core machines, spread the > work over a few threads/connections. > > --- Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > > 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. > > > > > > __ > __ > Never miss an email again! > Yahoo! Toolbar alerts you the instant new Mail arrives. > http://tools.search.yahoo.com/toolbar/features/mail/ > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
Yes, threading is one option I'm testing and timing as well. > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 27, 2007 12:29 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Questions on views > > Assuming you're running on multi-core machines, spread the > work over a few threads/connections. > > --- Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > > 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. > > > > > > __ > __ > Never miss an email again! > Yahoo! Toolbar alerts you the instant new Mail arrives. > http://tools.search.yahoo.com/toolbar/features/mail/ > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
Assuming you're running on multi-core machines, spread the work over a few threads/connections. --- Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > 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. Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
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] -
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] -
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] -
RE: [sqlite] Questions on views
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? Thanks! Dennis > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 27, 2007 12:35 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > Is doing a SELECT EXISTS (...) faster than a SELECT COUNT > (... LIMIT > > 1) or would it be the same (I would expect them to be the same, but > > that's only my guess)? > > > > > I would expect them to be very nearly the same. Any > difference would only be apparent if you repeat them many > times (which is what you are doing). You should probably try > it both ways and measure the execution time to see which is faster. > > HTH > Dennis Cote > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
--- Dennis Cote <[EMAIL PROTECTED]> wrote: > Dennis Volodomanov wrote: > > Is doing a SELECT EXISTS (...) faster than a SELECT COUNT (... LIMIT 1) > > or would it be the same (I would expect them to be the same, but that's > > only my guess)? > > > I would expect them to be very nearly the same. Any difference would > only be apparent if you repeat them many times (which is what you are > doing). You should probably try it both ways and measure the execution > time to see which is faster. "EXPLAIN SELECT ..." is also a good way to find out what the queries are doing if you're prepared to decipher the opcodes. Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
Dennis Volodomanov wrote: Is doing a SELECT EXISTS (...) faster than a SELECT COUNT (... LIMIT 1) or would it be the same (I would expect them to be the same, but that's only my guess)? I would expect them to be very nearly the same. Any difference would only be apparent if you repeat them many times (which is what you are doing). You should probably try it both ways and measure the execution time to see which is faster. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Questions on views
Thank you for the reply. Is doing a SELECT EXISTS (...) faster than a SELECT COUNT (... LIMIT 1) or would it be the same (I would expect them to be the same, but that's only my guess)? Regards, Dennis > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 24, 2007 1:47 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > > > Is it quicker (slower or the same) to execute a query on a > view or to > > execute the original query from which this view was created? > > > > I'm basically looking for the best (fastest) way to execute > thousands > > of queries to check whether they return any results or not. At the > > moment I'm doing a COUNT, but I'm hoping to find an easier > way than to > > execute count thousands of times... > > > > > Dennis, > > A query on a view will take a little longer to prepare than > the same query used to define the view, but both should > execute at the same speed since sqlite saves and recompiles > the sql code used to define the view. > > You can check for a result from a query using EXISTS faster > than you can count the number of results. Exists tests stop > as soon as they have found a result, count must find all the results. > > select exists( ); > > HTH > Dennis Cote > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
Dennis Volodomanov wrote: Is it quicker (slower or the same) to execute a query on a view or to execute the original query from which this view was created? I'm basically looking for the best (fastest) way to execute thousands of queries to check whether they return any results or not. At the moment I'm doing a COUNT, but I'm hoping to find an easier way than to execute count thousands of times... Dennis, A query on a view will take a little longer to prepare than the same query used to define the view, but both should execute at the same speed since sqlite saves and recompiles the sql code used to define the view. You can check for a result from a query using EXISTS faster than you can count the number of results. Exists tests stop as soon as they have found a result, count must find all the results. select exists( ); HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Questions on views
Hello all, Is it quicker (slower or the same) to execute a query on a view or to execute the original query from which this view was created? I'm basically looking for the best (fastest) way to execute thousands of queries to check whether they return any results or not. At the moment I'm doing a COUNT, but I'm hoping to find an easier way than to execute count thousands of times... Thank you in advance, Dennis - To unsubscribe, send email to [EMAIL PROTECTED] -
