SQL Server Performance

how to avoid table scan

Discussion in 'General DBA Questions' started by sramesh, Jul 6, 2006.

  1. sramesh New Member

    <br />hi all<br />this is my query,<br /><br />select productiondate, unitname from unit<br />inner join dailyproductionmaster dpm<br />on dpm.unitcode = unit.unitcode <br />where unit.unitcode = 'kat'<br /><br />after execute execution plan, i got the plan history below.<br />i saw the table scan. how to avoid table scan.<br />DailyProductionMaster.lot field -- this field indexed<br />Please give me suggestion.<br /><br /><br /><br />StmtText <br />------------------------------------------------------------------------------------------------------------------------- <br /> |--Nested Loops(Inner Join)<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1000]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FeedsJune01].[dbo].[Unit]))<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FeedsJune01].[dbo].[Unit].[PK_Unit]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Unit].[UnitCode]='kat') ORDERED FORWARD)<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FeedsJune01].[dbo].[DailyProductionMaster] AS [dpm]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[dpm].[UnitCode]='kat'))<br /><br />(4 row(s) affected)<br /><br /><br /><br /><br /><br />Thanks<br />S. Ramesh
  2. sramesh New Member

    hi all,<br />i have changed the query. now my problem is solved.<br />the query is<br /><br />select productiondate, unitname from unit<br />inner join dailyproductionmaster dpm (index =unitcode)<br />on dpm.unitcode = unit.unitcode <br />where dpm.unitcode = 'kat'<br /><br />StmtText <br />--------------------------------------------------------------------------------------------------------------------------------------------------- <br /> |--Nested Loops(Inner Join)<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1000]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FeedsJune01].[dbo].[Unit]))<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FeedsJune01].[dbo].[Unit].[PK_Unit]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Unit].[UnitCode]='kat') ORDERED FORWARD)<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1002]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FeedsJune01].[dbo].[DailyProductionMaster] AS [dpm]))<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FeedsJune01].[dbo].[DailyProductionMaster].[UnitCode] AS [dpm]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[dpm].[UnitCode]='kat') ORDERED FORWARD)<br /><br />(5 row(s) affected)<br /><br /><br />Thanks<br />S. Ramesh
  3. satya Moderator

    I appreciate your feedback that will help other with similar problems, and in any case make sure the table has required indexes to avoid such fullscan properties.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page