Hi I've encountered some really differing performance results for a query on
the AIX platform. I could you some help with this.
The query performs a union and an order by its large and complicated. But here
is a "Watered down" version:
select
id, xl.srcn srcn col1, ... coln
from ktab, xlist xl
where ( (op= 2 and (fb&32) and not (fb&64))
or (op=11 and kdo.cdba = bdba)
or (op= 5 and kdo.cmr = 5 and (lfb&4))
or (op= 6 and kdo.cmr = 5 and (lfb&4))
or (op= 8 and kdo.cmr = 5 and (lfb&4)))
and ktab.xid = xl.xid
union
select
id, xl.srcn srcn, col1, ... coln
from ltab, xlist xl
where (ltab.fb&4) and ltab.xid = xl.xid
order by srcn, id;
The table ktab contains 84000 rows, ltab contains 0 and xlist contains 3 rows.
So there are 3 distinct srcn values.
The timing to execute this is about 14.7 seconds
query plan:
0|0|TABLE ktab
1|1|TABLE xlist AS xl USING PRIMARY KEY
0|0|TABLE ltab
1|1|TABLE xlist AS xl USING PRIMARY KEY
If I re-organize this into a create table as select and a create index and a
select.
Then I get the following times:
Create table: 2.2 s
Create index: 1.3 s
Select: 7.6
Query plan for this final select
0|0|TABLE ksrcn WITH INDEX ksrcn ORDER BY
10 seconds for the new vs 14.7 seconds for the original ....
On linux this same query executes in 5.5 seconds. (After flushing the linux
buffer cache).
Any ideas why this is so slow on aix, where the disk drives are scsi 10k drives?
Thanks,
Ken
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
