sql - Terribly slow query inner join -

in application have products , categories. product can in multiple categories. have 2 tables: cwobject (products) , entityobjectlink (link between product , category). have 1 query used lot after days of tweeking terribly slow. there aproximately 400k records in cwobject , 1.2m in entityobjectlink.

this query:

select top (99999)      cwobject.*       cwobject  inner join      dbo.entityobjectlink on cwobject.cwobject_guid = entityobjectlink.entityobjectlink_linkedcwobject_guid      entityobjectlink_linkedcwentity_guid = '9a0e41d7-a472-445e-b94f-44fe1a1506b3'      , cwobject_cwsitecluster_guid = '0f178176-9720-41c7-9528-99fdf30005e8'      , cwobject_entitytype = 1      , (cwobject_predecessor_guid null) order      cwobject_name asc 

entityobjectlink has relevant clustered index:

primary key clustered ([entityobjectlink_linkedcwentity_guid] asc,                        [entityobjectlink_linkedcwobject_guid] asc)        (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 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 (% other columns%)         (pad_index = off, statistics_norecompute = off,               sort_in_tempdb = off, drop_existing = off, online = off,               allow_row_locks = on, allow_page_locks = on) 

if use query optimiser tells me query uses both indexes. see couple of things don't understand:

1. it warns me there no column statistics entity type , predecessorguid. because added index?

2. there huge difference between actual , estimated row read on cwobjects table.

if @ live query statistics query reads 171k records in cwobjects index seek (why seek?). merge join on entityobjectlink table reads 2.5k records. have been more effective other way around.

i really, stuck here... can help?

here execution plan: https://1drv.ms/u/s!alcbn2sexrj-hnjjesr9czpoepohww

update:

atostats on, few hours old.

almost time consumed in index seek on cwobject...

update 2:

i forced statistics update on cwobjects table. made huge difference! query 10 times faster!

you don't have statistics on of columns, hence warning in execution plan:

columns no statistics: [compareware].[dbo].[cwobject].cwobject_entitytype; [compareware].[dbo].[cwobject].cwobject_predecessor_guid

this results in actual number of rows of 174480 while estimated number of rows 1779,2.

try activating auto create statistics on database, or create statistics on columns manually.