Different query time using the same parameters

Last Post 03 Jun 2008 01:37 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
02 Jun 2008 02:32 PM
Hi all,

I have an interesting situation with one of the stored procedures.

It’s a simple select like

select column
from test
where columnDate > 'datetime' and columnDate < 'datetime'
and column1 = 'userid'

test table have 2 non-clustered indexes (One defined on columnDate and another on column1).
The index based on columnDate field is not very selective.

Most of the time optimizer chooses index based on column1 which is fast, but sometimes based on columnDate which is slow when passing the same parameters.

Why is that?
trans53
New Member
New Member

--
02 Jun 2008 03:21 PM
Sure, below is the code:

Regarding an index on OrderDate i don't know why it was created, i just noticed that it's not selective at all.
What do you think if i add Userid to the OrderDate index? Instead of having 2 separate indexes.

Thanks

CREATE PROC [dbo].[xx_GetOrders]
@userID char(12),
@StartDate datetime,
@EndDate datetime
AS

set nocount on

SELECT OrderID
FROM dbo.Orders WITH (NOLOCK)
WHERE dbo.Orders.OrderDate >= @StartDate AND dbo.Orders.OrderDate < @EndDate
AND dbo.Orders.Userid = 139770


RETURN (0)


Index info:

IX_Order_UserID nonclustered located on DATA UserID
IX_Order_OrderDate nonclustered located on DATA OrderDate
PK_Order clustered, unique, primary key located on DATA OrdersID
SQLUSA
New Member
New Member

--
03 Jun 2008 01:37 AM
quote:

Originally posted by: trans53
select column
from test
where columnDate > 'datetime' and columnDate < 'datetime'
and column1 = 'userid'

test table have 2 non-clustered indexes (One defined on columnDate and another on column1).
The index based on columnDate field is not very selective.




If this is a frequently executed query, you should consider clustered index in columnDate.

Optimizer makes its decision based on the availabe info which is subject to change in a dynamic table.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
You are not authorized to post a reply.

Acceptable Use Policy