Also, very good.
No index on Delta File:
3 seconds.
Index on SN:
4 seconds.
Index on MasterList (write_out_ok, MFGID, TypeID, SN);
4 seconds.
Time is to the nearest second in my test program, so I can't distinguish
between the two.
In summary:
/*FAST */
insert or replace into
Adam DeVita wrote:
> I've identified the following query as a bottle neck in a utility I've
> written.
>
> insert or replace into main.masterlist select * from delta.masterlist
> d where d.write_out_ok=0 and
> d.sn not in(select M.sn from main.masterlist M where
> M.record_updatetime >
Awesome, brilliant, and decisive!
New times:
No index on Delta File:
3 seconds.
Index on SN:
4 seconds.
Index on MasterList (write_out_ok, MFGID, TypeID, SN);
4 seconds.
The speedup of the one query is greater than this because the above time
figures include
1) A query to see if there are any
I believe your choice of query is not good enough. Try this one:
insert or replace into main.masterlist
select d.*
from delta.masterlist d left outer join main.masterlist M on d.sn = M.sn
where d.write_out_ok=0
and d.record_updatetime >= ifnull(M.record_updatetime, '')
Pavel
Good day,
Could someone explain where I'm going wrong with this?
I've identified the following query as a bottle neck in a utility I've
written.
insert or replace into main.masterlist select * from delta.masterlist d
where d.write_out_ok=0 and
d.sn not in(select M.sn from main.masterlist M
5 matches
Mail list logo
