Re: [sqlite] optimizing your sql

2006-07-11 Thread Jay Sprenkle
On 7/11/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote: OTOH sometimes a correlated subquery that just collects data is faster to execute or more readily optimisable when expressed as a JOIN. I remember such a case, but it wasn't in my code so I paid insufficient attention and now my memory of the

Re: [sqlite] optimizing your sql

2006-07-11 Thread A. Pagaltzis
* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-11 20:35]: > I think perhaps the correlated subquery optimization is really > another name for rewriting it so the smallest table is the > driving table. It probably doesn't matter how you write the sql > as long as you get the smallest table as the

Re: [sqlite] optimizing your sql

2006-07-11 Thread Jay Sprenkle
> >Not so fast there. I have accelerated queries by several 100 > >percent by turning joins into subqueries. On other occasions I > >did so by turning subqueries into joins. The performance of > >joins vs subqueries in any non-trivial query depends on a > >*lot* of variables. You can't just say

Re: [sqlite] optimizing your sql

2006-07-11 Thread A. Pagaltzis
* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-11 20:15]: > On 7/11/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote: > >* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-10 17:30]: > >> // - Use SQL Joins instead of using sub-queries > > > >Not so fast there. I have accelerated queries by several 100 > >percent

Re: [sqlite] optimizing your sql

2006-07-11 Thread Jay Sprenkle
On 7/11/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote: * Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-10 17:30]: > // - Use SQL Joins instead of using sub-queries Not so fast there. I have accelerated queries by several 100 percent by turning joins into subqueries. On other occasions I did so by

Re: [sqlite] optimizing your sql

2006-07-11 Thread A. Pagaltzis
* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-10 17:30]: > // - Use SQL Joins instead of using sub-queries Not so fast there. I have accelerated queries by several 100 percent by turning joins into subqueries. On other occasions I did so by turning subqueries into joins. The performance of joins vs

[sqlite] optimizing your sql

2006-07-10 Thread Jay Sprenkle
Good morning all, A poster to digg mentioned some sql performance tuning tips. I've checked one of them out to see what difference it makes. My results are below, but first here's the poster's tuning tip list: // SQL tuning tips // // Avoid using the following: // // - Boolean operators >, =,