Peter de Bruijn Peter de Bruijn - 5 months ago 15
SQL Question

Terribly slow query inner join

In my application I have products and categories. A product can be in multiple categories. So I have two tables: CwObject (products) and EntityObjectLink (Link between product and category). I have one query that is used a lot but even after days of tweeking it is terribly slow. There are aproximately 400K records in CwObject and 1.2M in EntityObjectLink.

This is the query:

SELECT TOP (99999) CwObject.* FROM CwObject INNER JOIN dbo.EntityObjectLink
ON CwObject.CwObject_Guid = EntityObjectLink.EntityObjectLink_LinkedCwObject_Guid WHERE
EntityObjectLink_LinkedCwEntity_Guid = '9a0e41d7-a472-445e-b94f-44fe1a1506b3'
AND CwObject_CwSiteCluster_Guid = '0f178176-9720-41c7-9528-99fdf30005e8'
AND CwObject_EntityType = 1
AND (CwObject_Predecessor_Guid IS NULL)
ORDER BY CwObject_Name ASC


EntityObjectLink has a relevant clustered index:

PRIMARY KEY CLUSTERED (
[EntityObjectLink_LinkedCwEntity_Guid] ASC,
[EntityObjectLink_LinkedCwObject_Guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)


The CwObject table has a relevant index:

NONCLUSTERED INDEX [IX_ClusterEntitytypePredecessorStatusClusteraccount] ON [dbo].[CwObject](
[CwObject_CwSiteCluster_Guid] ASC,
[CwObject_EntityType] ASC,
[CwObject_Predecessor_Guid] ASC,
[CwObject_Status] ASC,
[CwObject_ClusterAccount_Guid] ASC
)
INCLUDE (% ALL other columns%) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)


If I use the query optimiser it tells me the query uses both indexes. But i see a couple of things I don't understand:
1) It warns me that there are no column statistics for entitytype and predecessorguid. Could it be because I just added the index?
2) There is a huge difference between the actual and estimated row read on the CwObjects table.

If I look at the live query statistics the query reads 171K records in CwObjects with an index seek (why a seek?). Then does a merge join on the EntityObjectLink table where it reads 2.5K records. It would have been much more effective to do it the other way around.

I am really, really stuck here... Can anyone help?
Here is the execution plan: https://1drv.ms/u/s!AlCbN2sexrJ-hNJjeSR9cZPOEpOHww

UPDATE:
AtoStats are ON, a few hours old.
Almost all time is consumed in the index seek on CwObject...

UPDATE 2:
I forced the statistics to update on the cwObjects table. This made a huge difference! The query is almost 10 times faster!

Answer

You don't have statistics on some of the columns, hence you get this warning in your execution plan:

Columns With No Statistics: [compareware].[dbo].[CwObject].CwObject_EntityType; [compareware].[dbo].[CwObject].CwObject_Predecessor_Guid

This results in an Actual Number of rows of 174480 while the estimated number of rows was 1779,2.

Try activating the auto create statistics on the database, or create statistics on those columns manually.