Index requirement for logical operations

  • Please help me build the index for the query.The table has more than 3 million rows and whatever index combination I choose,always there is a index scan for the query.I am not able to convert it into a seek operation.this runs throughout the day and keeps my CPU spike up every 5 minutes

    SELECT TOP 20 Primary, Secondary, [Year], DocType, [PageCount], ReturnId, IMAGEPATH

    FROM

    WHERE [Year] = 2009 AND

    (Primary IN (123, 234) OR Secondary IN (241, 354))

    Please suggest me an index for this.I beleive the 'OR' operator is causing the scan but is there a way to avoid it.

  • You need two indexes because of the or

    One index on Primary, one on Secondary. You may need to make both covering, depends on the rows involved. test and see

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If you paste the SQL into a query window in SSMS and press CTL-L you will see the estimated execution plan. SSMS may display a Missing Index suggestion and right-clicking the suggestion and choosing Missing Index details will show the SQL needed to create the index. Alternatively you could use the Index tuning tool available from the query menu.

  • Philip Turtle (3/3/2011)


    If you paste the SQL into a query window in SSMS and press CTL-L you will see the estimated execution plan. SSMS may display a Missing Index suggestion and right-clicking the suggestion and choosing Missing Index details will show the SQL needed to create the index. Alternatively you could use the Index tuning tool available from the query menu.

    Gail,

    I created the two indexes.

    One on primary and year including all the columns in select query

    Second on secondary or year including all the columns in select query.

    But there is still a table scan.I have good amount of memory so all the database stays in cache but the scan makes the cpu go up to 90 percent every time this query runs.Any more suggestions please.

  • GilaMonster (3/3/2011)


    You need two indexes because of the or

    One index on Primary, one on Secondary. You may need to make both covering, depends on the rows involved. test and see

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/4/2011)


    GilaMonster (3/3/2011)


    You need two indexes because of the or

    One index on Primary, one on Secondary. You may need to make both covering, depends on the rows involved. test and see

    CREATE NONCLUSTERED INDEX [primary] ON [dbo].[Finals]

    (

    [Primary] ASC

    )

    INCLUDE ( [ReturnId],

    [Secondary],

    [Year],

    [DocType],

    [PAGECOUNT],

    [IMAGEPATH])

    CREATE NONCLUSTERED INDEX [secondary] ON [dbo].[Finals]

    (

    [secondary] ASC

    )

    INCLUDE ( [ReturnId],

    [primary],

    [Year],

    [DocType],

    [PAGECOUNT],

    [IMAGEPATH])

    I created these two indexes but the query does a table scan with the first index to give me the output.I even updated the statistics for the table.

  • Execution plan please

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have attached the execution plan for the query.I am posting my query and indexes which i created as per your specification.

    My query:

    SELECT TOP 20 Primary, Secondary, [Year], DocType, [PageCount], ReturnId, IMAGEPATH

    FROM finals WHERE [Year] = 2009 AND

    (Primary IN (123, 234) OR Secondary IN (241, 354))

    Index-1

    CREATE NONCLUSTERED INDEX [prim] ON [dbo].[Finals]

    (

    [Primary ] ASC

    )

    INCLUDE ( [ReturnId],

    [Secondary ],

    [Year],

    [DocType],

    [PAGECOUNT],

    [IMAGEPATH])

    Index-2

    CREATE NONCLUSTERED INDEX [sec] ON [dbo].[Finals]

    (

    [Secondary ] ASC

    )

    INCLUDE ( [ReturnId],

    [Primary ],

    [Year],

    [DocType],

    [PAGECOUNT],

    [IMAGEPATH])

  • Move year from the include to the key. I didn't notice the filter on that earlier. That's for both indexes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/4/2011)


    Move year from the include to the key. I didn't notice the filter on that earlier

    I moved 'year' to the key column in both the indexes but still the same execution plan with non-clustered index scan

  • I read a couple of articles which says that the 'or' clause will cause a scan no matter what!

  • Please post the DDL , the plan has many convert implicits in it.



    Clear Sky SQL
    My Blog[/url]

  • chandan_jha18 (3/4/2011)


    I read a couple of articles which says that the 'or' clause will cause a scan no matter what!

    Those articles are wrong, and it's trivial to prove that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have already given the DDL for indexes.Here is the DDL for table:

    CREATE TABLE [dbo].[Finals](

    [ReturnId] [int] IDENTITY(1,1) NOT NULL,

    [primary] [varchar](20) NULL,

    [secondary] [varchar](20) NULL,

    [Year] [varchar](4) NULL,

    [QTR] [varchar](1) NULL,

    [DocType] [varchar](5) NULL,

    [ScGrpID] [varchar](8) NULL,

    [RECID] [bigint] NOT NULL,

    [SOURCE] [varchar](25) NULL,

    [PAGECOUNT] [int] NULL,

    [IMAGEPATH] [varchar](150) NULL,

    [DateCreated] [datetime] NOT NULL

    ) ON [PRIMARY]

  • Put single quotes around all the literals in the string. They're strings in the table, they should be strings in the queries too.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply