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
PRIMARY KEY CLUSTERED (
)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)
NONCLUSTERED INDEX [IX_ClusterEntitytypePredecessorStatusClusteraccount] ON [dbo].[CwObject](
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)
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.