slow distinct

Question

slow distinct

Tara-1044200

SSCoach

Points: 15951
More actions
November 18, 2010 at 7:55 am

#231414

I have this simple query which returns 50 million records but it take mroe than 2 hrs, is there a way ti speed up this simple query.
select distinct *
into #t
from RevEmployee
where empcode= '098665877'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

It will have to create a work table which it will use to get the distinct rows.

Do you need all columns to be distinct or can you get the primary key values from a subset (using a covering index hopefully) then join back to the table to get the rest of the columns.

I take it empcode is indexed? It would be worth checking whether the index is being used.

Cursors never.
DTS - only when needed and never to control.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

yes, i need all columns and empcode is not indexed but many of the columns in the distinct are indexed and i cant change anything, is there any other simple trick to make it fast. thanks

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

If there was a simple trick , then the optimizer would probably be doing it already.

Since you hands are so severely tied , there really is not much that you can do.

Clear Sky SQL
My Blog[/url]

Ellen-477471 SSCrazy Points: 2238 More actions · Answer 4

Let me ask you what you are trying to accomplish. There may be a better/easier way to go about it.

That is a lot of "distinct" records returned.