slow query, speed up with a non clustered index

Question

slow query, speed up with a non clustered index

keyser soze-308506

SSChasing Mays

Points: 630
More actions
November 27, 2013 at 5:01 pm

#300885

Hi
I have a query like that
select c.client_group, s.product_id, count(*) as q
from Sales s
inner join Client c on c.client_id= s.client_id
group by c.client_id, s.product_id
the Sales.pk (clustered index) consist of client_id and product_id -- 1.000.000 rows
the Client.pk (clustered index) consist of client_id -- 20.000 rows
very simple, but it was VERY slow
when I added a non clustered index Client.ix ( identical to the clustered index )
the query turned incredibly fast
what could be the reason?
thanks

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Actual query plans of both situations might be nice to troubleshoot.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

keyser soze-308506 SSChasing Mays Points: 630 More actions · Answer 2

thanks Koen

I was looking for "general reasons"

If you see the query plans now, they are almost identical

Now, with or without the index, the engine doesn't try to use it

I don't know why the query was so slow

I suspect about "with recompute" improved it

the next time I will post the problem

just when it appears

:doze:

thanks anyway

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

keyser soze-308506 (11/27/2013)
Hi
I have a query like that
select c.client_group, s.product_id, count(*) as q
from Sales s
inner join Client c on c.client_id= s.client_id
group by c.client_id, s.product_id
the Sales.pk (clustered index) consist of client_id and product_id -- 1.000.000 rows
the Client.pk (clustered index) consist of client_id -- 20.000 rows
very simple, but it was VERY slow
when I added a non clustered index Client.ix ( identical to the clustered index )
the query turned incredibly fast
what could be the reason?
thanks

To do the join, SQL must fully scan the index containing the Client.ix. When that is the clustered index, SQL must scan the entire table, since a clus index is the table itself. When it is a nonclustered index, SQL scans the nonclustered index instead of the entire table. If the rows are wide, then scanning the nonclus index will likely be far fewer pages than scanning the entire table, thus it runs faster.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

keyser soze-308506 (11/27/2013)
Hi
I have a query like that
select c.client_group, s.product_id, count(*) as q
from Sales s
inner join Client c on c.client_id= s.client_id
group by c.client_id, s.product_id

That code won't actually work at all. You have the non-aggregated c.client_group in the SELECT list and it's not included in the GROUP BY. You also have c.client_id in the GROUP BY and it's not included as a non-aggregate in the SELECT list.

Please post the actual query that you're having problems with. Also, please see the second link in my signature line below under "Helpful Links" to get help with performance problems.

--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)