November 8, 2016 at 8:34 am
SQL server fails to use Index when I use these set of variables:
Declare @FromDate datetime
Declare @ToDate datetime
set @FromDate = '02/11/2016'
set @ToDate = '02/11/2016'
set @FromDate = @FromDate + ' 00:00:00'
set @ToDate = @ToDate + ' 23:59:59'
select * from tableA
where event_Date between @FromDate and @ToDate
and event_Type = 'Open'
Execution Plan shows full Table Scan
As opposed to
select * from tableA
where event_Date between '02/11/2016 00:00:00' and '02/11/2016 23:59:59'
and event_Type = 'Open'
Execution Plan shows full Index Scan (nonClustered)
Why would it be the case?
Thank you,
Vinay
November 8, 2016 at 8:49 am
Please supply table DDL for tableA, including indexes. How many rows are in the table? Are your statistics up to date? What happens if you add SELECT @ToDate, @FromDate to your first script to check that the dates have been set up correctly? Do the two queries return identical result sets? Please also post both execution plans (the .sqlplan files).
John
Edit - asked for execution plans as well
November 8, 2016 at 8:57 am
Unfortuetly this is by design. When SQL Server creates the query plan for an ad-hoc query, it doesn't know what will be the value of the variables, so it just knows that you are looking for data between 2 points of time, but has no idea what those point of times are. To solve this you have few options. First option is to use the recompile hint:
Declare @FromDate datetime
Declare @ToDate datetime
set @FromDate = '02/11/2016'
set @ToDate = '02/11/2016'
set @FromDate = @FromDate + ' 00:00:00'
set @ToDate = @ToDate + ' 23:59:59'
select * from tableA
where event_Date between @FromDate and @ToDate
option (recompile)
The second object is to create a stored procedure instead of a batch. In the stored procedure you can use parameters instead of variables. SQL Server has a feature that is called parameter sniffing, that lets him "peek" at the parameters' values and create a plan according to there values:
create procedure ShowRecordsByDates @FromDate datetime, @ToDate datetime
as
select * from tableA
where event_Date between @FromDate and @ToDate
go
exec ShowRecordsByDates '20161102', '20161102 23:59:59'
Another option is to use sp_executesql that can convert an ad-hoc sql statement to a stored procedure and then take an advantage of the parameter sniffing:
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(100);
DECLARE @ParmDefinition nvarchar(100);
Declare @FromDate datetime
Declare @ToDate datetime
set @FromDate = '02/11/2016'
set @ToDate = '02/11/2016'
set @FromDate = @FromDate + ' 00:00:00'
set @ToDate = @ToDate + ' 23:59:59'
set @SQLString = 'select * from tableA
where event_Date between @FromDate and @ToDate'
set @ParmDefinition = '@FromDate datetime, @ToDate datetime'
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2016 at 8:58 am
Hello John,
Here is the table structure:
CREATE TABLE [dbo].[tableA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmailAddress] [varchar](60) NULL,
[event_Type] [varchar](25) NOT NULL,
[MessageName] [varchar](255) NULL,
[DeliveryDate] [datetime] NULL,
[event_Date] [datetime] NULL,
[BroadcastId] [varchar](25) NULL,
[subjectline] [varchar](100) NULL,
[firstname] [varchar](50) NULL,
[surname] [varchar](50) NULL,
[deviceInfo] [varchar](255) NULL,
[import_Date] [datetime] NOT NULL,
[profiler] [varchar](50) NULL
) ON [PRIMARY]
GO
There are over 12 Million records.
There are six Indexes non Clustered (combination and standalone fields)
Both the queries return exact same output - no difference. The one with @FromDate, @ToDate takes longer to execute as it does Full table scan.
I can force the Index using "With Index", but why would it differ?
Thank you for your help.
Vinay
November 8, 2016 at 9:16 am
We need the index definitions as well as the actual execution plans.
November 8, 2016 at 9:20 am
Hi Adi,
The clause Option(recompile) works well.
Execution time 59 seconds;
and 54 seconds for constant values supplied.
Before Option(recompile), it was "2min 31 sec."
I will also use Stored Procedure. That's a better option infact, because the Query is going to be used in SSRS.
Many thanks for the solution.
Vinay
November 8, 2016 at 9:42 am
Glad I could help:-)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2016 at 1:43 pm
The real solution is very likely to cluster the table on event_date, if that is how you most often query the table. You'll get minimum I/O without having to recompile the query.
But first look at existing index stats to see if you still need all 6 indexes that are in place, that is, are all of those indexes being used enough to warrant them existing? If not, drop any that aren't needed.
Then:
1) script out the existing, useful nonclus indexes
2) drop the nonclus indexes
3) create the clus index
4) re-create the nonclus indexes
Review the index stats again later to see if the nonclus indexes are still needed. Often many nonclus indexes can be removed once the table is best clustered.
/*be careful to not set the fillfactor too low (such as by letting it default),
since presumably event_dates are naturally ascending anyway.*/
CREATE UNIQUE CLUSTERED INDEX tableA__CL ON dbo.tableA ( event_date, Id ) WITH ( /*DATA_COMRPESSION = PAGE*,/ FILLFACTOR = 98 ) ON [PRIMARY] /*change filegroup name as required*/;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply