On 23 Dec 2010, at 7:56pm, Vander Clock Stephane wrote: > Windows 2008 R2 with 8GB of memory. > > but actually i run the test on a beta server with only 1Gb of memory and > win2003 .. > > the database si with 2 000 000 rows is 1.8 GO
Right. So you have a database with 2 000 000 rows that is 1.8GB So your first 1 000 000 rows takes up about 1GB. And your test case with just 1 000 000 rows in runs really fast. So what is happening is that most of the first 1 000 000 rows fits in memory. Once the database gets bigger than that the application has to keep fetching information from disk all the time, and that's far slower. > how to speed up this query : > > Select > H1.ID > from > HASH1 H1 > where > x1_y1_min >= max((<#randomnumber> % 255)-10,0) and > x1_y1_max <= min((<#randomnumber> % 255)+10,255) and > x1_y2_min >= max((<#randomnumber> % 255)-10,0) and > x1_y2_max <= min((<#randomnumber> % 255)+10,255) and > x1_y3_min >= max((<#randomnumber> % 255)-10,0) and > x1_y3_max <= min((<#randomnumber> % 255)+10,255) and > x1_y4_min >= max((<#randomnumber> % 255)-10,0) and > x1_y4_max <= min((<#randomnumber> % 255)+10,255) and > x1_y5_min >= max((<#randomnumber> % 255)-10,0) and > x1_y5_max <= min((<#randomnumber> % 255)+10,255); If there is only one random number involved in all that, precalculate all the max and mins: ll = max((<#randomnumber> % 255)-10,0) mm = min((<#randomnumber> % 255)+10,255) Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
