Re: [sqlite] how to speed up this ?
> Select >H1.ID > from >HASH1 H1 > where >x1_y1 BETWEEN min11 AND max11 AND >x1_y2 BETWEEN min12 AND max12 AND >x1_y3 BETWEEN min13 AND max13 AND >x1_y4 BETWEEN min14 AND max14 AND >x1_y5 BETWEEN min15 AND max15; > > no it's not work at all !! without an rtree index the speed is a total disaster (several minutes) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
i do it.. but it's change nothing :( On 12/24/2010 3:47 PM, Simon Slavin wrote: > On 24 Dec 2010, at 8:17am, Vander Clock Stephane wrote: > >> I have a key like this >> >> 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i >> call node >> Node are integer comprise between 0 and 255 (bytes) > You mean from 000 to 254. 255 breaks your system because you are using > modulus 255. > >> and i need to found "similare" key. >> A similar key is a key where one node is max + - 10 more or lower than >> another node >> >> so >> >> 123-098-230-120-111 >> is similare to >> 120-097-235-118-110 >> >> but not similare to >> 180-197-215-018-010 >> >> it's for our graphical software > Precalculate five sets of minimum and maximum bounds: > > min11 = max((<#randomnumber> % 255)-10,0) > max11 = min((<#randomnumber> % 255)+10,255) > > Then you can just seize the ten values you need from the table and use them > to make up your SELECT command. > > Select >H1.ID > from >HASH1 H1 > where >x1_y1 BETWEEN min11 AND max11 AND >x1_y2 BETWEEN min12 AND max12 AND >x1_y3 BETWEEN min13 AND max13 AND >x1_y4 BETWEEN min14 AND max14 AND >x1_y5 BETWEEN min15 AND max15; > > This will be faster than trying to get SQLite to do the calculations for > every row of your 50 000 000 row table. > > It will work much faster with an index on x1_y1. > It may or may not work faster with an index on > (x1_y1,x1_y2,x1_y3,x1_y4,x1_y5). Try it. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
On 24 Dec 2010, at 12:47pm, Simon Slavin wrote: > Precalculate five sets of minimum and maximum bounds: > > min11 = max((<#randomnumber> % 255)-10,0) > max11 = min((<#randomnumber> % 255)+10,255) > > Then you can just seize the ten values you need from the table and use them > to make up your SELECT command. Sorry, replace the 'seize the ten values you need from the table' with 'use those ten values'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
On 24 Dec 2010, at 8:17am, Vander Clock Stephane wrote: > I have a key like this > > 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i > call node > Node are integer comprise between 0 and 255 (bytes) You mean from 000 to 254. 255 breaks your system because you are using modulus 255. > and i need to found "similare" key. > A similar key is a key where one node is max + - 10 more or lower than > another node > > so > > 123-098-230-120-111 > is similare to > 120-097-235-118-110 > > but not similare to > 180-197-215-018-010 > > it's for our graphical software Precalculate five sets of minimum and maximum bounds: min11 = max((<#randomnumber> % 255)-10,0) max11 = min((<#randomnumber> % 255)+10,255) Then you can just seize the ten values you need from the table and use them to make up your SELECT command. Select H1.ID from HASH1 H1 where x1_y1 BETWEEN min11 AND max11 AND x1_y2 BETWEEN min12 AND max12 AND x1_y3 BETWEEN min13 AND max13 AND x1_y4 BETWEEN min14 AND max14 AND x1_y5 BETWEEN min15 AND max15; This will be faster than trying to get SQLite to do the calculations for every row of your 50 000 000 row table. It will work much faster with an index on x1_y1. It may or may not work faster with an index on (x1_y1,x1_y2,x1_y3,x1_y4,x1_y5). Try it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
can you gave me the name of a good SSD you advise me to buy ? i decide to make a try ! Thanks again stéphane On 12/24/2010 12:24 AM, John Drescher wrote: > On Thu, Dec 23, 2010 at 4:06 PM, Vander Clock Stephane > wrote: >> that very very much expensive :( how much you thing ? >> > $500 to $600 US. > > John > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
> Can you describe what you're trying to do with that command ? of course ! I have a key like this 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i call node Node are integer comprise between 0 and 255 (bytes) and i need to found "similare" key. A similar key is a key where one node is max + - 10 more or lower than another node so 123-098-230-120-111 is similare to 120-097-235-118-110 but not similare to 180-197-215-018-010 it's for our graphical software so i add in a rtree table the key like this insert into rtreetable Node1_min = 123-10, Node1_max = 123+10, Node2_min = 098-10, Node2_max = 098+10, etc.. and after to detect similare node i simply do select from rtreetable where Node1_min >= 120 and Node1_max <= 120 and Node2_min >= 098 and Node2_max <= 098 and etc... it's ok ? thanks you by advance ! stéphane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
hmmm, how spatial index can help me better than sqlite Rtree index ? i just need to do Select H1.ID from HASH1 H1 where x1_y1_min>= x and x1_y1_max<= y and x1_y2_min>= z and x1_y2_max<= w and x1_y3_min>= a and x1_y3_max<= b and x1_y4_min>= c and x1_y4_max<= d and x1_y5_min>= e and x1_y5_max<= f; thanks by advance stéphane On 12/24/2010 12:40 AM, stormtrooper wrote: > I'd be surprised if you run out of memory, updating a field, even with a > 50,000,000 row table. > > Also, if you are working with real geospatial data such as Imagery or vector > data, there are many applications that may be suited for these calculations. > > Spatialite is a Sqlite extension that has spatial indexes(rtree) and spatial > functions such as buffer, intersect,etc > > > > Vander Clock Stephane wrote: >> i don't know but i quite sure not, because the cost to update all the >> row in the table Hash will be much much (much) more expensive ... >> and also this solution it's absolutely not multi thread :( >> >> On 12/23/2010 11:46 PM, stormtrooper wrote: >>> would it run faster if you add two columns to the Hast table - randmax >>> and >>> randmin >>> >>> update Hash set randmax = max((<#randomnumber> % 255)-10,0) >>> update Hash set randmin = min((<#randomnumber> % 255)+10,255) >>> >>> CREATE INDEX HASH_RMIN_IDX ON HASH (RANDMIN); >>> CREATE INDEX HASH_RMAX_IDX ON HASH (RANDMAX); >>> >>> Select >>> H1.ID >>> from >>> HASH1 H1 >>> where >>> x1_y1_min>= randmax and >>> x1_y1_max<= randmin and >>> x1_y2_min>= randmax and >>> x1_y2_max<= randmin and >>> x1_y3_min>= randmax and >>> x1_y3_max<= randmin and >>> x1_y4_min>= randmax and >>> x1_y4_max<= randmin and >>> x1_y5_min>= randmax and >>> x1_y5_max<= randmin; >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
On 23 Dec 2010, at 8:19pm, Vander Clock Stephane wrote: > in my test all the random are different ... Can you describe what you're trying to do with that command ? Or can anyone else explain to me what Stephane is trying to do ? I don't see the point of a construction like this 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); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
I'd be surprised if you run out of memory, updating a field, even with a 50,000,000 row table. Also, if you are working with real geospatial data such as Imagery or vector data, there are many applications that may be suited for these calculations. Spatialite is a Sqlite extension that has spatial indexes(rtree) and spatial functions such as buffer, intersect,etc Vander Clock Stephane wrote: > > i don't know but i quite sure not, because the cost to update all the > row in the table Hash will be much much (much) more expensive ... > and also this solution it's absolutely not multi thread :( > > On 12/23/2010 11:46 PM, stormtrooper wrote: >> would it run faster if you add two columns to the Hast table - randmax >> and >> randmin >> >> update Hash set randmax = max((<#randomnumber> % 255)-10,0) >> update Hash set randmin = min((<#randomnumber> % 255)+10,255) >> >> CREATE INDEX HASH_RMIN_IDX ON HASH (RANDMIN); >> CREATE INDEX HASH_RMAX_IDX ON HASH (RANDMAX); >> >> Select >> H1.ID >> from >> HASH1 H1 >> where >> x1_y1_min>= randmax and >> x1_y1_max<= randmin and >> x1_y2_min>= randmax and >> x1_y2_max<= randmin and >> x1_y3_min>= randmax and >> x1_y3_max<= randmin and >> x1_y4_min>= randmax and >> x1_y4_max<= randmin and >> x1_y5_min>= randmax and >> x1_y5_max<= randmin; >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/how-to-speed-up-this---tp30524605p30525283.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
On Thu, Dec 23, 2010 at 4:06 PM, Vander Clock Stephane wrote: > that very very much expensive :( how much you thing ? > $500 to $600 US. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
that very very much expensive :( how much you thing ? On 12/23/2010 11:55 PM, John Drescher wrote: >> i m affraid so ... but what it's will be with 50 000 000 rows ? i don't >> have 100 gigabytes of memory :( > I would get a 256GB SSD. > > John > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
i don't know but i quite sure not, because the cost to update all the row in the table Hash will be much much (much) more expensive ... and also this solution it's absolutely not multi thread :( On 12/23/2010 11:46 PM, stormtrooper wrote: > would it run faster if you add two columns to the Hast table - randmax and > randmin > > update Hash set randmax = max((<#randomnumber> % 255)-10,0) > update Hash set randmin = min((<#randomnumber> % 255)+10,255) > > CREATE INDEX HASH_RMIN_IDX ON HASH (RANDMIN); > CREATE INDEX HASH_RMAX_IDX ON HASH (RANDMAX); > > Select > H1.ID > from > HASH1 H1 > where > x1_y1_min>= randmax and > x1_y1_max<= randmin and > x1_y2_min>= randmax and > x1_y2_max<= randmin and > x1_y3_min>= randmax and > x1_y3_max<= randmin and > x1_y4_min>= randmax and > x1_y4_max<= randmin and > x1_y5_min>= randmax and > x1_y5_max<= randmin; > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
> i m affraid so ... but what it's will be with 50 000 000 rows ? i don't > have 100 gigabytes of memory :( I would get a 256GB SSD. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
would it run faster if you add two columns to the Hast table - randmax and randmin update Hash set randmax = max((<#randomnumber> % 255)-10,0) update Hash set randmin = min((<#randomnumber> % 255)+10,255) CREATE INDEX HASH_RMIN_IDX ON HASH (RANDMIN); CREATE INDEX HASH_RMAX_IDX ON HASH (RANDMAX); Select H1.ID from HASH1 H1 where x1_y1_min >= randmax and x1_y1_max <= randmin and x1_y2_min >= randmax and x1_y2_max <= randmin and x1_y3_min >= randmax and x1_y3_max <= randmin and x1_y4_min >= randmax and x1_y4_max <= randmin and x1_y5_min >= randmax and x1_y5_max <= randmin; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/how-to-speed-up-this---tp30524605p30524963.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
> 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. > i m affraid so ... but what it's will be with 50 000 000 rows ? i don't have 100 gigabytes of memory :( with 50 000 000 rows and btree index like where fiedA = yyy then result is imediate ! but why not also with rtree index ? thanks you by advance stéphane >> 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) in my test all the random are different ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
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 thanks by advance ! stéphane On 12/23/2010 10:52 PM, Simon Slavin wrote: > On 23 Dec 2010, at 7:36pm, Vander Clock Stephane wrote: > >> when their is lower than 1 000 000 row it's return in lower than 10 ms >> with more than 1 000 000 row it's return with around 350 ms :( >> and i need more than 50 000 000 rows :( :( > How much memory do you have in that computer ? What operating system are you > running ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
On 23 Dec 2010, at 7:36pm, Vander Clock Stephane wrote: > when their is lower than 1 000 000 row it's return in lower than 10 ms > with more than 1 000 000 row it's return with around 350 ms :( > and i need more than 50 000 000 rows :( :( How much memory do you have in that computer ? What operating system are you running ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
