Distinct performance???

Question

Post reply

Distinct performance???

sol.nt

SSCrazy

Points: 2062
More actions
January 26, 2007 at 9:11 pm

#117457

Hi all ,
I have problem and need somebody help me.
I have a select statement which exec about 4 seconds but when i put "distinct" into this select statement then exec it , it run between 55' and 1'20''
So , What happen which my select ?
Somebody know something about this , please tell me . Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

DISTINCT is simplest option of GROUP BY.

It makes SQL Server rescan returning resultset and group the data.

It involves creating hash table in tempbd for holding temporary data.

Your query must return big recordset, so it takes time to work it out.

_____________
Code for TallyGenerator

Bose C S SSC Rookie Points: 45 More actions · Answer 2

its may be because ur table and record count may be big ,

try to index ur table with the column which u use for distint clause

sol.nt SSCrazy Points: 2062 More actions · Answer 3

My result record just return 507 record, i think it maybe small , so i don't know exactly why this result took so long to run when have distinct in it ???

Regard.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

As posted by Sergiy, Your DISTINCT clause would implement a GROUP BY implicitly and does a scan of the results, so in the query analyzer if you look at the Execution Play you would see that there would an additional SORT scan when you include the DISTINCT clause to your query.

Prasad Bhogadi
www.inforaise.com

sol.nt SSCrazy Points: 2062 More actions · Answer 5

Thanks for your answer.

I watched the Excution Plan and saw what you said, but what i mentioned is why have difference between 5'' and 55'' when you just add "distinct" on result of 507 records.

Regards.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 6

Can you please post your table structure and the query. Your question makes sense as it should not really take so much time.

Prasad Bhogadi
www.inforaise.com

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

And, do a ...

SELECT COUNT(*)

FROM yourtablename

... to find out how many rows it's really looking at...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

sol,

how many records WITHOUT distinct (see Jeff's query) and how "heavy" is each of those records?

I mean what is aggregate size of all columns in your recordset?

_____________
Code for TallyGenerator

sol.nt SSCrazy Points: 2062 More actions · Answer 9

It's exactly 507 records , the same when have "distinct". Because this, I have confused. ???

About my SQL statement , i will post it in here in the short time , please be patient.

Thanks and Regards.