March 3, 2011 at 6:03 am
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.
March 3, 2011 at 8:06 am
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
March 3, 2011 at 8:08 am
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.
March 4, 2011 at 12:22 am
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.
March 4, 2011 at 12:43 am
GilaMonster (3/3/2011)
You need two indexes because of the orOne 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
March 4, 2011 at 12:58 am
GilaMonster (3/4/2011)
GilaMonster (3/3/2011)
You need two indexes because of the orOne 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.
March 4, 2011 at 1:02 am
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
March 4, 2011 at 1:11 am
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])
March 4, 2011 at 1:14 am
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
March 4, 2011 at 1:19 am
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
March 4, 2011 at 1:21 am
I read a couple of articles which says that the 'or' clause will cause a scan no matter what!
March 4, 2011 at 1:23 am
Please post the DDL , the plan has many convert implicits in it.
March 4, 2011 at 1:25 am
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
March 4, 2011 at 1:26 am
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]
March 4, 2011 at 1:30 am
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
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply