Could you help to optimize the below query to perform better? Can I reduce the cost?
SELECT this_.id AS id1_20_0_,
this_.version AS version2_20_0_,
this_.domain AS domain4_20_0_,
this_.createdate AS createda5_20_0_,
this_.lastmodifydate AS lastmodi6_20_0_,
this_.ownerid AS ownerid7_20_0_,
this_.timeperiod AS timeperi8_20_0_,
this_.type AS type9_20_0_,
this_.capturesource AS capture10_20_0_,
this_.value AS value11_20_0_,
this_.siteid AS siteid12_20_0_,
this_.lastmodifyuserid AS lastmod13_20_0_,
this_.classid AS classId3_20_0_
FROM dbo.pcwdepconstraints this_
WHERE this_.classid = 65
AND this_.ownerid = 200000000001
AND ( this_.capturesource IS NULL
OR this_.capturesource IN ( 1073741826, 1073741827, 0, 1, 2 ) )
I have recreated the ix2_pcwdepcons by below columns, but still there is no change in the execution plan and its cost.
( this_.id ,
this_.version ,
this_.domain ,
this_.createdate ,
this_.lastmodifydate,
this_.timeperiod ,
this_.type ,
this_.value ,
this_.siteid
this_.lastmodifyuserid )
The execution plan shows that you have a seek (to find the basic information), but then also a "key lookup", which is a rather expensive operation which you should try to avoid if you can.
Looking at the query, I see you're using classid
, ownerid
and capturesource
in your WHERE
clause - so those need to be in an index. If you can, you can also "include" all other columns in your query's SELECT
clause into that index, to avoid the "key lookup".
So I'd say try an index like this:
CREATE NONCLUSTERED INDEX IX_Test
ON dbo.pcwdepconstraints_bkp (classid, ownerid, capturesource)
INCLUDE (id, version, domain, createdate, lastmodifydate, lastmodifyuserid,
timeperiod, type, value, siteid)
Including that many columns only makes sense, if this is not the whole list of columns from that table. These columns will take up space, so you're trading off increased query performance vs. more disk space needed. But if your table as a lot more columns and your query selects "only" those 13 (since it really needs those), then the INCLUDE
can be really helpful to speed things up.