Hard to say what's the problem if you don't post the 
schema of the tables and the indexes, and provide some
sample data.

Perhaps there is a bug in the join optimizer.
Try using an explicit CROSS JOIN.

select     t1.a, t1.b, t2.c, t2.d
from       t1
cross join t2
where      t1.x = t2.x and t1.a >= 100 and t1.a < 200
group by   t1.a, t1.b, t2.c, t2.d


--- Steve Green <[EMAIL PROTECTED]> wrote:

> The performance is exactly the same after running analyze on both tables.
> 
> Steve
> 
> Joe Wilson wrote:
> 
> > Run an ANALYZE statement on your database and your 
> > queries will be fast once again.
> > 
> > Perhaps SQLite should fall back to the old non-optimized 
> > join behavior when the sqlite_stat1 table is missing.
> > This would cover 99% of the pre-SQLite3.2.3 legacy databases 
> > out there where the queries have already been hand-tuned.
> > 
> > --- Steve Green <[EMAIL PROTECTED]> wrote:
> > 
> > 
> >>Hi,
> >>
> >>I currently have a v3.2.0 database that contains two tables that I regularly
> >>query with a join, e.g.,
> >>
> >>select    t1.a, t1.b, t2.c, t2.d
> >>from      t1
> >>join      t2
> >>on        t1.x = t2.x and t1.a >= 100 and t1.a < 200
> >>group by  t1.a, t1.b, t2.c, t2.d
> >>
> >>Table t1 has an index on a
> >>Table t2 has an index on x
> >>
> >>I've created a new v3.3.4 database with the same schema and data.  Table t1 
> >>has
> >>about 150 million rows and t2 has about 3000 rows.  When I run the query 
> >>using
> >>v3.2.0, it returns in about 0.1 seconds.  When run using v3.3.4, it takes 
> >>about
> >>20 seconds.  For completeness, I ran the same query using v3.2.8, v3.3.2, 
> >>and
> >>v3.3.3.  v3.2.8 performed the same (roughly) as v3.2.0; v3.3.2 and v3.3.3 
> >>performed
> >>the same as v3.3.4 (so, it appears that the change was made going into 
> >>v3.3.x).
> >>
> >>When I ran an explain on the query in both databases, the main difference I
> >>noticed was in the opening of the database tables; in v3.2.0, it appears 
> >>that
> >>the indices of both tables are being used...
> >>
> >>addr  opcode          p1          p2          p3
> >>----  --------------  ----------  ----------  
> >>---------------------------------
> >><snip>
> >>37    OpenRead        1           4
> >>38    SetNumColumns   1           7
> >>39    Integer         0           0
> >>40    OpenRead        3           6           keyinfo(1,BINARY)
> >>41    Integer         2           0
> >>42    OpenRead        0           2
> >>43    SetNumColumns   0           8
> >>44    Integer         2           0
> >>45    OpenRead        4           3           keyinfo(1,BINARY)
> >>46    Integer         3795633     0
> >><snip>
> >>
> >>In v3.3.4, it appears that the index of only one of the tables is being 
> >>used...
> >>
> >>addr  opcode          p1          p2          p3
> >>----  --------------  ----------  ----------  
> >>---------------------------------
> >><snip>
> >>37    OpenRead        0           2
> >>38    SetNumColumns   0           3
> >>39    Integer         0           0
> >>40    OpenRead        1           4
> >>41    SetNumColumns   1           7
> >>42    Integer         0           0
> >>43    OpenRead        3           6           keyinfo(1,BINARY)
> >>44    Rewind          0           78
> >>45    Integer         3795633     0
> >><snip>
> >>
> >>It's quite possible that I'm misinterpreting the explain data as I'm fairly 
> >>new
> >>with it... However, it seems to me that this might explain the huge 
> >>difference
> >>in performance.
> >>
> >>Has anyone else seen this type of performance difference between v3.2.x and 
> >>v3.3.x?
> >>Any ideas on how we can improve the performance using v3.3.x (we'd really 
> >>like to
> >>use the most current version of the database, but the performance 
> >>difference would
> >>be a killer)?
> >>
> >>Thanks,
> >>
> >>Steve
> > 
> > 
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around 
> > http://mail.yahoo.com 
> 
> -- 
> Steve Green
> SAVVIS
> Transforming Information Technology SM
> 
> This message contains information which may be confidential and/or
> privileged.  Unless you are the intended recipient (or authorized
> to receive for the intended recipient), you may not read, use,
> copy or disclose to anyone the message or any information contained
> in the message. If you have received the message in error, please
> advise the sender by reply e-mail at [EMAIL PROTECTED] and
> delete the message and any attachment(s) thereto without retaining
> any copies.
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Reply via email to