Optimizing my SQL SELECT statement

[chpicker]

I am trying to pull a list of unique items from a table. The table has 850,000 records in it. Each record has a unitnum field in it, and I want to pull out a list of how many different unitnums are present. I tried 2 different SELECT statements:

SELECT unitnum FROM (masterlist) GROUP BY unitnum
SELECT DISTINCT unitnum FROM (masterlist)

Both of these statements return the same results, but take almost 12 seconds to process, returning only 47 records. The table is indexed on the unitnum field. How can I optimize this query so it pulls these 47 records out faster? I thought it would be almost instant, but it isn't. I even tried changing the unitnum tag to UNIQUE instead of REGULAR, and it still takes as long. Any suggestions to get it to work faster?

 

[fluteplr]

Ary you sure the index is on unitnum and not on something like uppper(unitnum)?

Also check your setting of set deleted this can have an effect when there is a large table and there is not and index on deleted(). BTW it does not seem to matter if there are deleted records or not, since it still has to check to see if there are any.

Also how big is the unitnum field?

 

[dgrewe]

If it is something you do several times a day you may want to create a permanent unique index like you tried before. Then Try
use table order uniqueunit
copy fields unitnum to xxx
select 0
use xxx

or if it is just a number of units you want

use table order uniqueunit
count to lnUniqueUnit
? lnUniqueUnit && or whatever else you want with the info David W. Grewe
Dave@internationalbid.com

 

[chpicker]

*nods* The index is just straight "unitnum". I tried "SET DELETED OFF" and that fixed the problem of speed...the query runs in about 0.17 seconds. However, now it doesn't sort the results. If I use this statement:

SELECT unitnum FROM (masterlist) GROUP BY unitnum ORDER BY unitnum

then it sorts it...but I lose the speed again. Changing the index to UNIQUE is interesting...whether or not I include ORDER BY, it takes about 3.5 seconds to run the query. This is an improvement, but still not satisfactory, since I KNOW the query can be done almost instantaneously.

Any suggestions on how to get the resulting query to sort without the speed loss, other than to do another query on the results?

Further note: in my EXE it still takes the full 12 seconds to process even with SET DELETED OFF. No clue why.

 

[DEDMOD]

Are you certain you are using your new code? You might want to have a wait window indicate what 'build' you are running, if you don't have another sure give-away.

Dave Dardinger

 

[chpicker]

Yep...I'm sure it's the current EXE. There are some dead giveaways )

 

[ThomC]

<<However, now it doesn't sort the results...>>

Bizarre. Ordering the result set is an intrinsic characteristic of a GROUP BY clause. Something else is going on here.

<<I tried &quot;SET DELETED OFF&quot; and that fixed the problem of speed...the query runs in about 0.17 seconds...in my EXE it still takes the full 12 seconds to process even with SET DELETED OFF>>

If you're running the query interactively and you're doing it repeatedly, Rushmore is buffering the query in memory. The first time you run the query, the query will take the maximum amount of time.

Regards,
Thom C.

 

[dgrewe]

Well I have a 1,231,295 records of IP addresses hits on my web site in the last 30 days so I did a test.

Index on IPA tag IPA
Index on IPA tag UNIQUEIPA unique

Did a SQL and the best I can do is 4 seconds
did a
set order to UNIQUEIPA
copy fields IPA to dwg
and it completed it in 0.3 seconds

How much faster do you want? David W. Grewe
Dave@internationalbid.com