February 3, 2011 at 3:37 am
I have written a query which is taking 38 Seconds to execute on a table which has more than 30 Million records. This is for a search page where all the parameters are optional except 1 parameter ie., @CIN.
If I pass the below parameters, the query takes approx. 38 secs to execute.
declare @cin as varchar(20)
declare @CLI as varchar(20)
declare @date as datetime
declare @Todate as datetime
declare @service varchar(100)
select @cin = '1622737814'
select @date = getdate()-30
select @Todate = getdate() -25
select @service = 'STANDARD'
SELECT
[Service],
Product_Entered as [Product Entered],
CIN,
CLI,
CLI_Match as [CLI Match],
Date,
Convert(varchar(5),[Time],114) as [CallTime]
FROM CLIExtract WITH (NOLOCK)
WHERE
ISNULL(CIN,'') = ISNULL(@CIN,ISNULL(CIN,'')) AND
ISNULL(DATE,'') >= ISNULL(@Date,ISNULL(DATE,'')) AND
ISNULL(DATE,'') <= ISNULL(@ToDate,ISNULL(DATE,''))AND
ISNULL(Service,'') = ISNULL(@Service,ISNULL(Service,''))
Later I have created a dynamic query, which takes less than 1 sec to retrieve the data. Below is the Dynamic Query.
declare @cin as varchar(20)
declare @date as datetime
declare @Todate as datetime
declare @service varchar(100)
select @cin = '1622737814'
select @date = getdate()-30
select @Todate = getdate() -25
select @service = 'STANDARD'
declare @strSql as varchar(max)
SELECT @strsql = 'SELECT
[Service],
Product_Entered as [Product Entered],
CIN,
CLI,
CLI_Match as [CLI Match],
Date,
Convert(varchar(5),[Time],114) as [CallTime]
FROM CLIExtract WITH (NOLOCK)
WHERE '
if ISNULL(@CIN,'') <> ''
begin
SELECT @strsql = @strsql + 'CIN = ''' + @cin + ''' AND '
end
if isnull(@date,'') <> ''
begin
SELECT @strsql = @strsql + 'DATE >= ''' + convert(varchar(30),@Date ,109) + ''' AND '
end
if isnull(@ToDate,'') <> ''
begin
SELECT @strsql = @strsql + 'DATE <= ''' + convert(varchar(30),@ToDate ,109) + ''' AND '
end
if isnull(@service,'') <> ''
begin
SELECT @strsql = @strsql + 'Service = ''' + @Service + ''' AND '
end
select @strsql=left(@strsql,len(@strsql)-4)
exec (@strsql)
Is there any other way to retrieve the data without using the dynamic query approach. Please suggest.
Note: All the parameters are optional except @CIN.
Thank you,
Aditya
February 3, 2011 at 5:17 am
In the first query, the use of ISNULL on the columns you're applying the filters to is forcing a table/Clustered index scan as the engine cannot use any covering indexed you may have due to the function applied to the column(s)
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 3, 2011 at 5:23 am
Please see this link ... http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
EDIT :
Basically , no , but you can improve your situation by using sp_executesql
February 3, 2011 at 7:19 am
Also, while it might appear to help performance, that NOLOCK hint is potentially problematic.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 8, 2011 at 10:35 am
You should also consider using sp_executesql to prevent SQL injections.
February 9, 2011 at 5:39 am
Dynamic sql is almost always the most performant way to deal with this type of scenario. Beware SQL Injection as someone else stated and also watch out for procedure cache bloat. SQL 2008+ helps with this with the optimize for ad hoc workloads option. You can do dbcc freeproccache, but that is a all-server action (NOTE that I have quite a few clients that have scheduled jobs to run this throughout the day in production systems).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 9, 2011 at 5:45 am
TheSQLGuru (2/9/2011)
Dynamic sql is almost always the most performant way to deal with this type of scenario. Beware SQL Injection as someone else stated and also watch out for procedure cache bloat. SQL 2008+ helps with this with the optimize for ad hoc workloads option. You can do dbcc freeproccache, but that is a all-server action (NOTE that I have quite a few clients that have scheduled jobs to run this throughout the day in production systems).
On 2005 you can create plan guides to selectively clear the cache
Or on 2008 DBCC FREEPROCACHE accepts a planhandle, to clear plans selectively.
So it doesnt have to be a server-wide action
February 9, 2011 at 9:39 am
Dave Ballantyne (2/9/2011)
TheSQLGuru (2/9/2011)
Dynamic sql is almost always the most performant way to deal with this type of scenario. Beware SQL Injection as someone else stated and also watch out for procedure cache bloat. SQL 2008+ helps with this with the optimize for ad hoc workloads option. You can do dbcc freeproccache, but that is a all-server action (NOTE that I have quite a few clients that have scheduled jobs to run this throughout the day in production systems).On 2005 you can create plan guides to selectively clear the cache
Or on 2008 DBCC FREEPROCACHE accepts a planhandle, to clear plans selectively.
So it doesnt have to be a server-wide action
My gut tells me that the server effort to clear gobs (tens/hundreds of thousands or even millions) of ad hoc plans from cache using either method you mention (i.e. single plan at a time) will be MUCH worse on the server than simply clearing all plans. I have several pretty high-volume production systems that do this on a regular schedule to get buffer pool pages back. Of course YMMV!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 10, 2011 at 3:00 am
SELECT [Service],
Product_Entered AS [Product Entered],
CIN,
CLI,
CLI_Match AS [CLI Match],
[Date],
CONVERT(CHAR(5), [Time], 114) AS CallTime
FROM dbo.CliExtract
WHERE (CIN = @cin OR @cin IS NULL)
AND ([Service] = @Service OR @Service IS NULL)
AND [Date] >= COALESCE(@Date, '17530101')
AND [Date] <= COALESCE(@ToDate, '99991231')
N 56°04'39.16"
E 12°55'05.25"
February 10, 2011 at 3:09 am
TheSQLGuru (2/9/2011)
My gut tells me that the server effort to clear gobs (tens/hundreds of thousands or even millions) of ad hoc plans from cache using either method you mention (i.e. single plan at a time) will be MUCH worse on the server than simply clearing all plans. I have several pretty high-volume production systems that do this on a regular schedule to get buffer pool pages back. Of course YMMV!!
Sure , not suggesting that its ideal to every scenario 🙂
In our system , it is handy to cherry pick the statements in cache that will NEVER be reused.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply