AW: AW: [sqlite] Performance question
-Ursprüngliche Nachricht- Von: Martin Pfeifle [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 26. September 2006 13:35 An: sqlite-users@sqlite.org Betreff: AW: AW: [sqlite] Performance question >Hi Michael, >could you please (re)post the exact create inex statements +primary key you used. For speeding up >your query, you need an index on x only but not on id,x. Best Martin The table looks like: (blobsize between 100 and 8000 bytes, 25 rows in the table) Create table t1 (x integer, y integer, flag integer, data blob) Create index idx on t1 (x,y,flag) (it doesn't matter if is inlcuded in the index) Takes 5ms on my pda, 100 of those need 500ms: Select data from t1 where x=v1 and y=v1 and flag=f Takes 7sec(!) on pda for a rectangle with 60 blobs: Select data from t1 where (x between xlow and xhigh) and (y between ylow and yhigh) and flag=f Lightning fast: Adding a column xy set to (x << 16|y) and replacing idex with an idx on xy: Select x,y,data from t1 where xy in (xy1,xy2,...) Cheers, Michael
AW: AW: [sqlite] Performance question
Hi Michael, could you please (re)post the exact create inex statements +primary key you used. For speeding up your query, you need an index on x only but not on id,x. Best Martin - Ursprüngliche Mail Von: Michael Wohlwend <[EMAIL PROTECTED]> An: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> Gesendet: Dienstag, den 26. September 2006, 09:34:00 Uhr Betreff: AW: [sqlite] Performance question -Ursprüngliche Nachricht- Von: Dennis Cote [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 22. September 2006 17:07 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Performance question Michael Wohlwend wrote: > But If I do "select data from pictures where (x between high_x and > low_x) and (y between high_y and low_y) then this takes ca. 8 seconds > (!) on wince. > > >>If you are really writing your between clauses as above with the high >>limit first, then they are not doing what you think. The low limit >>should always be given first. Ah, that was a typo, of course the query was "between (low and high)". I changed this to "x > low and x <= high ..." and i got the same result: 1 single query (without bouds-check) takes 5ms, the query with the bounds-check takes ca. 7seconds (there are indices on x and y). I changed the query to (select ... where id in (v1,v2,...)) this was quite fast again, even if the list of values got over 200 elements, but that's not the way I wanted to do it. Maybe sqlite on arm cpus in the current implementation isn't optimized enough. But I have no idea where this huge slowdown comes from. Cheers Michael ___ Der frühe Vogel fängt den Wurm. Hier gelangen Sie zum neuen Yahoo! Mail: http://mail.yahoo.de
AW: [sqlite] Performance question
-Ursprüngliche Nachricht- Von: Dennis Cote [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 22. September 2006 17:07 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Performance question Michael Wohlwend wrote: > But If I do "select data from pictures where (x between high_x and > low_x) and (y between high_y and low_y) then this takes ca. 8 seconds > (!) on wince. > > >>If you are really writing your between clauses as above with the high >>limit first, then they are not doing what you think. The low limit >>should always be given first. Ah, that was a typo, of course the query was "between (low and high)". I changed this to "x > low and x <= high ..." and i got the same result: 1 single query (without bouds-check) takes 5ms, the query with the bounds-check takes ca. 7seconds (there are indices on x and y). I changed the query to (select ... where id in (v1,v2,...)) this was quite fast again, even if the list of values got over 200 elements, but that's not the way I wanted to do it. Maybe sqlite on arm cpus in the current implementation isn't optimized enough. But I have no idea where this huge slowdown comes from. Cheers Michael
AW: [sqlite] Performance question
-Ursprüngliche Nachricht- Von: Gerald Dachs [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 22. September 2006 11:28 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Performance question >My sql knowledge may be a little bit rusty and I have really no idea how sqlite is doing "between" >querys. Anyway, once I have learned never to use between because your query is equivalent to: >where ((x >= high_x and x <= low_x) or (x >= low_x and x <= high_x) and (y >= high_y and y <= > low_y) or (y >= low_y and y <= high_y)) I think it is: where (x >= low_x and x <= high_x) and (y >= low_y and y <= high_y), i.e. in "between a and b", a should be lower or equal to b (you don't get a result otherwise) So it's without the "or" part. But I will test the other statement too. >because of the or operators you will get a union of 4 selects. Maybe I am wrong but I would expect >that "where x >= low_x and x <= high_x and y >= low_y and y <= high_y" should be faster and all >what you need. You have indices on x and y, haven't you? Yep :-) Cheers Michael
