Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 6 of 6 total |
Select DISTINCT don't use any index |
Tue, Sep 12 2006 11:17 AM | Permanent Link |
"Enrico Ghezzi" | Hi
I have this SQL SELECT DISTINCT FIELDNAME1 FROM MYDB ..dat is 100 MB , 80'000 records. record result should to be only 100 records. but this query is very slow, i have add any index .. but.. notting. Are there any plans to implement DISTINCT processing that uses an index when available ? dbisam 4.2x , Delphi 7 |
Tue, Sep 12 2006 12:36 PM | Permanent Link |
"Johnnie Norsworthy" | "Enrico Ghezzi" <EnricoGhezzi@Ghezzi.com> wrote in message
news:7B4AA14F-3C79-48AC-9432-6C3A65F561E0@news.elevatesoft.com... > SELECT DISTINCT FIELDNAME1 FROM MYDB > > .dat is 100 MB , 80'000 records. > record result should to be only 100 records. > this query is very slow, i have add any index .. but.. notting. Enrico, I'm sure Tim will answer your question concerning DBISAM directly, but I thought you might want to take a look at this and might give it a try: http://www.grebarsys.com/html/superfastdistinct_.html -Johnnie |
Tue, Sep 12 2006 4:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Enrico,
<< .dat is 100 MB , 80'000 records. record result should to be only 100 records. but this query is very slow, i have add any index .. but.. notting. Are there any plans to implement DISTINCT processing that uses an index when available ? >> Yes, but in the mean time just leave off the DISTINCT if you know that there is a unique or primary index on the selected columns. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 12 2006 4:32 PM | Permanent Link |
"Robert" | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:4C6ADD80-7B20-4A8B-9BFD-CCF2720719BF@news.elevatesoft.com... > > Yes, but in the mean time just leave off the DISTINCT if you know that > there is a unique or primary index on the selected columns. > Not an option, IMO. If the table contains 80K records and he is only expecting 100 as a result of DISTINCT, then obviously the field is not unique. However, SELECT FIELD FROM TABLE GROUP BY FIELD might give you the correct set in a reasonable time. Robert |
Fri, Sep 15 2006 10:13 AM | Permanent Link |
"Enrico Ghezzi" | > However, > > SELECT FIELD FROM TABLE GROUP BY FIELD > > might give you the correct set in a reasonable time. Hi i have try it. work well but it dont' use any index ( always low , like select distinct ) |
Fri, Sep 15 2006 11:57 AM | Permanent Link |
"Robert" | "Enrico Ghezzi" <EnricoGhezzi@Ghezzi.com> wrote in message news:373C4B1A-A4CD-4020-8BBD-97133B936895@news.elevatesoft.com... > >> However, >> >> SELECT FIELD FROM TABLE GROUP BY FIELD >> >> might give you the correct set in a reasonable time. > > > Hi > > i have try it. > > work well > > > but it dont' use any index ( always low , like select distinct ) > True, as Tim explained, DBISAM simply will not use the index for these selects. I thought it might be faster with group by. If you need speed, you'll have to code a loop using a ttable. Let's say your table is indexed by FIELD and ID var prev : string; first; prev := field; while not eof store prev; FindNearest[prev, 99999]; << should position you in the next FIELD end Never done anything like this, but seems to me it should work. Robert |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |