1

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

Execution Plan

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 )
Dale K
  • 25,246
  • 15
  • 42
  • 71
Dothertechie
  • 27
  • 6
  • Could you please post your execution plan to https://www.brentozar.com/pastetheplan/ ? And could you please also post the table's DDL (SQL to create the table) and any information on indexes on that table? – marc_s Mar 26 '19 at 10:11
  • https://www.brentozar.com/pastetheplan/?id=SyaLo_wOE – Dothertechie Mar 26 '19 at 10:18

1 Answers1

0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Hi Marc, I created the IX_Test as suggested above, please find the execution plan for the same https://www.brentozar.com/pastetheplan/?id=r1CTltDd4 (IX_Test index seek is 100% ) ... Please advise – Dothertechie Mar 26 '19 at 10:38
  • @kevin: well - the cost of the query is about half of what it was before - so that's pretty good, isn't it? – marc_s Mar 26 '19 at 10:40
  • Is it Operator/subtree/IO/CPU, how do we determine the cost is half compared original query.. Just trying to understand, I am a SQL Server newbie. – Dothertechie Mar 27 '19 at 12:34
  • @kevin: if you hover over the top-left most operator (`SELECT`) in the execution plan visualization, you see the "Estimated Subtree Cost" - that's about half for the second execution plan you posted, that's what I'm referring to – marc_s Mar 27 '19 at 12:52