May 21, 2014 at 1:17 pm
I have a dynamic SQL query that uses various indexes and views.
When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:
@date is an integer.
begindate is datetime format.
begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))
The above code slows my query performance by at least 400%!
Would it be faster to add a computed column to my table, using the suggested method in the link below?
http://stackoverflow.com/questions/15998830/auto-computed-column-in-sql-server-2012
Then i could add an indexed view to improve performance, or can this be done another way?
Thanks
May 21, 2014 at 1:30 pm
What's the type of your begindate column?
For performance advice, read the following article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2014 at 1:31 pm
Why are you casting to Varchar? It's just going to force SQL to convert back to date, that's expensive conversions.
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
May 21, 2014 at 1:39 pm
GilaMonster (5/21/2014)
Why are you casting to Varchar? It's just going to force SQL to convert back to date, that's expensive conversions.
What would be the best datatype to cast to, in this situation?
Thanks
May 21, 2014 at 2:23 pm
isuckatsql (5/21/2014)
GilaMonster (5/21/2014)
Why are you casting to Varchar? It's just going to force SQL to convert back to date, that's expensive conversions.What would be the best datatype to cast to, in this situation?
Thanks
Assuming that begindate is a datetime you shouldn't cast it as anything. Do your comparison as a datetime datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2014 at 2:47 pm
"The data types varchar(max) and date are incompatible in the add operator."
Since it is within a dynamic sql query, i get the above error msg.
May 21, 2014 at 2:53 pm
isuckatsql (5/21/2014)
"The data types varchar(max) and date are incompatible in the add operator."Since it is within a dynamic sql query, i get the above error msg.
Boy you do make it difficult to help when you don't provide any details. Is begindate a varchar(MAX)??? If so, can you change it to datetime?
I take the following is similar to what you tried?
begindate > dateadd(d,@Date,GETDATE())
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2014 at 2:59 pm
May 21, 2014 at 4:36 pm
Sean,
In my original post, i said:
"I have a dynamic SQL query"
I also gave datatypes:
"@date is an integer."
"begindate is datetime format."
I also asked:
"Would it be faster to add a computed column to my table, using the suggested method in the link below?
http://stackoverflow.com/questions/15998830/auto-computed-column-in-sql-server-2012
Then i could add an indexed view to improve performance, or can this be done another way?"
I don't want to rewrite the whole query, which is why i did not post it!
I am more interested in other potential ways of doing this, not just fixing the date code.
Ian
May 22, 2014 at 2:34 am
isuckatsql (5/21/2014)
"The data types varchar(max) and date are incompatible in the add operator."Since it is within a dynamic sql query, i get the above error msg.
Shouldn't be if you properly parameterise the dynamic SQL, you only get that kind of error if you're concatenating variables into the string. But since you haven't shown us the full code, kinda hard to say anything more.
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
May 22, 2014 at 2:43 am
isuckatsql (5/21/2014)
In my original post, i said:"I have a dynamic SQL query"
I also gave datatypes:
"@date is an integer."
"begindate is datetime format."
Neither of which suggests that there's any conversions to varchar(max) anywhere.
I also asked:
"Would it be faster to add a computed column to my table, using the suggested method in the link below?
http://stackoverflow.com/questions/15998830/auto-computed-column-in-sql-server-2012
Then i could add an indexed view to improve performance, or can this be done another way?"
To be honest, no idea, would need to take query and test various options. I suspect that neither of those is a particularly good optoin, but again, without seeing the actual code it's like working in the dark.
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
May 22, 2014 at 7:55 am
Gail,
You are correct, i should have mentioned the varchar(max).
Please see the attached query below. I am aware it needs work to protect from SQL Injection ... its on my todo list.
The query ran great until i added the date code, after the creation of indexed views.
'Duration' is the computed field. [Duration] AS (datediff(day,[begindate],getdate())),
I decided to try a View on 'Duration' and it did help a lot, but i cannot created an index on the view due to the 'getdate' being deterministic. I am trying to work around that issue at the moment.
ALTER Procedure [dbo].[GetAllJobsSearchedDynamicQuery]
-- Add the parameters for the stored procedure here
@Title varchar(250),
@Company varchar(250),
@Date int,
@JobTitle int,
@JobType CHAR(19),
@Experience int,
@education CHAR(15),
@State CHAR(2),
@City VARCHAR(50),
@payper varchar(4),
@Salary int,
@MaxSalary int,
@Hourly int,
@MaxHourly int,
@fromRec int,
@toRec int,
@OrderType VARCHAR(10),
@OrderBy VARCHAR(100)
AS
DECLARE @sql VARCHAR(MAX), @QRY VARCHAR(MAX)
SELECT @sql = 'Begin
WITH results AS
(
select
id,
jobtitle as title,
company as companyname,
did,
JobDescription as description,
begindate,
locationcity as city,
locationstate,
employmenttype,
degreerequired,
PAYLOW,PAYHIGH,payper,duration,
'
if (@OrderBy = 'begindate')
Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY posteddate ' + ' ' + @OrderType + ') '
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY begindate ' + ' ' + @OrderType + ', ' + 'id' + ') '
SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) '
End
SELECT @sql = @sql + ' FROM dbo.cb
WHERE 1=1 '
IF (@Salary > 0 AND @Salary IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PAYLOW > CAST(''' + CAST(@Salary AS VARCHAR(50)) + ''' AS DECIMAL(9,2)) '
END
IF (@MaxSalary > 0 AND @MaxSalary IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PAYHIGH < CAST(''' + CAST(@MAXSalary AS VARCHAR(50)) + ''' AS DECIMAL(9,2)) '
END
IF (@Hourly > 0 AND @Hourly IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PAYLOW > CAST(''' + CAST(@Hourly AS VARCHAR(50)) + ''' AS DECIMAL(9,2)) '
END
IF (@MaxHourly > 0 AND @MaxHourly IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PAYHIGH < CAST(''' + CAST(@MAXHourly AS VARCHAR(50)) + ''' AS DECIMAL(9,2)) '
END
if (@Date <> 0)
Begin
set @sql = @sql + ' and begindate >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
--set @sql = @sql + ' and begindate >''' + cast(((select dateadd(d,@Date,GETDATE()))) as datetime) + ''''
End
if (@Title <> '')
Begin
--set @sql = @sql + ' and jobtitle like ''' + '%' + cast(@Title as varchar(250)) + '%' + ''''
set @sql = @sql + ' AND (SELECT COUNT(*) FROM SPLIT(''' + cast(@Title as varchar(250)) + ''','','') WHERE jobtitle like ''%'' + ITEMS + ''%'') > 0'
End
if (@Company <> '')
Begin
set @sql = @sql + ' and company = ''' + cast(@Company as varchar(250)) + ''''
End
if (@JobType <> '')
Begin
set @sql = @sql + ' and employmenttype = ''' + cast(@JobType as char(19)) + ''''
End
if (@Education <> '')
Begin
set @sql = @sql + ' and degreerequired = ''' + cast(@Education as char(15)) + ''''
End
if (@Payper <> '')
Begin
set @sql = @sql + ' and payper = ''' + cast(@Payper as varchar(4)) + ''''
End
if (@City <> '')
Begin
set @sql = @sql + ' and ' + cast(@City as varchar(10)) + ' in (select id from cities where LTRIM(RTRIM(locationcity)) = LTRIM(RTRIM(Cities.Name)))'
End
if (@State <> '')
Begin
--set @sql = @sql + ' and ' + cast(@State as varchar(10)) + ' in (select id from regions where LTRIM(RTRIM(locationstate)) = LTRIM(RTRIM(regions.abbreviatedName))) '
set @sql = @sql + ' and ''' + cast(@State as char(2))+ ''' = LTRIM(RTRIM(locationstate))'
End
SET @QRY = @SQL + ') '
set @sql = @sql + ')
SELECT
id,
title,
companyname,
did,
begindate,
description,
city,
locationstate,
city + '', '' + locationstate as Location,
employmenttype,
degreerequired,
PAYLOW,PAYHIGH,payper,duration,
(select count(*) from results) totalcount
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY BeginDate ' + ' ' + @OrderType +') R1 FROM results) T
WHERE R1 BETWEEN ' + cast(@fromRec as varchar(10)) + ' AND ' + cast(@toRec as varchar(10)) +
'
OPTION(Maxdop 8)
End
'
print @sql
exec (@sql)
DECLARE @QJOBTYPE VARCHAR(MAX) = @QRY;
SET @QJOBTYPE = @QJOBTYPE + 'SELECT employmenttype, COUNT(*) cnt FROM results GROUP BY employmenttype OPTION(Maxdop 8) END'
exec (@QJOBTYPE)
DECLARE @QSTATE VARCHAR(MAX) = @QRY;
SET @QSTATE = @QSTATE + 'SELECT locationstate, COUNT(*) cnt FROM results GROUP BY locationstate OPTION(Maxdop 8) END'
exec (@QSTATE)
DECLARE @QEDUCATION VARCHAR(MAX) = @QRY;
SET @QEDUCATION = @QEDUCATION + 'SELECT degreerequired, COUNT(*) cnt FROM results GROUP BY degreerequired OPTION(Maxdop 8) END'
exec (@QEDUCATION)
DECLARE @QSALARY VARCHAR(MAX) = @QRY;
SET @QSALARY = @QSALARY +
'
SELECT 1 id, ''0'' as minSalary, ''20000'' as maxSalary, COUNT(*) as cnt FROM results
WHERE ((PAYLOW > 0 AND PAYHIGH < 20000) and (payper = ''year''))
union
SELECT 2 id, ''20000'', ''40000'', COUNT(*) FROM results
WHERE ((PAYLOW > 20000 AND PAYHIGH < 40000) and (payper = ''year''))
union
SELECT 3 id, ''40000'', ''60000'', COUNT(*) FROM results
WHERE ((PAYLOW > 40000 AND PAYHIGH < 60000) and (payper = ''year''))
union
SELECT 4 id, ''60000'', ''80000'', COUNT(*) FROM results
WHERE ((PAYLOW > 60000 AND PAYHIGH < 80000) and (payper = ''year''))
union
SELECT 5 id, ''80000'', ''100000'', COUNT(*) FROM results
WHERE ((PAYLOW > 80000 AND PAYHIGH < 100000) and (payper = ''year''))
union
SELECT 6 id, ''100000'', ''1000000'', COUNT(*) FROM results
WHERE ((PAYLOW > 100000 AND PAYHIGH < 1000000) and (payper = ''year''))
union
SELECT 7 id, ''n/a'', ''n/a'', COUNT(*) FROM results
WHERE ((PAYLOW IS NULL OR PAYHIGH IS NULL) and (payper = ''year''))
END
'
exec (@QSALARY)
DECLARE @QHOURLY VARCHAR(MAX) = @QRY;
SET @QHOURLY = @QHOURLY +
'
SELECT 1 id, ''0'' as minHourly, ''20'' as maxHourly, COUNT(*) as cnt FROM results
WHERE ((PAYLOW > 0 AND PAYHIGH < 20) and (payper = ''hour''))
union
SELECT 2 id, ''20'', ''40'', COUNT(*) FROM results
WHERE ((PAYLOW > 20 AND PAYHIGH < 40) and (payper = ''hour''))
union
SELECT 3 id, ''40'', ''60'', COUNT(*) FROM results
WHERE ((PAYLOW > 40 AND PAYHIGH < 60) and (payper = ''hour''))
union
SELECT 4 id, ''60'', ''80'', COUNT(*) FROM results
WHERE ((PAYLOW > 60 AND PAYHIGH < 80) and (payper = ''hour''))
union
SELECT 5 id, ''80'', ''100'', COUNT(*) FROM results
WHERE ((PAYLOW > 80 AND PAYHIGH < 100) and (payper = ''hour''))
union
SELECT 6 id, ''100'', ''1000'', COUNT(*) FROM results
WHERE ((PAYLOW > 100 AND PAYHIGH < 1000) and (payper = ''hour''))
union
SELECT 7 id, ''n/a'', ''n/a'', COUNT(*) FROM results
WHERE ((PAYLOW IS NULL OR PAYHIGH IS NULL) and (payper = ''hour''))
END
'
exec (@QHOURLY)
--print @sql
DECLARE @QMODDATE VARCHAR(MAX) = @QRY;
SET @QMODDATE = @QMODDATE +
'
SELECT 1 id, ''-1'' as duration, COUNT_big(*) as cnt FROM results
WHERE (duration between 0 and 1)
union
SELECT 2 id, ''-3'' as duration, COUNT_big(*) as cnt FROM results
WHERE (duration between 0 and 3)
union
SELECT 3 id, ''-7'' as duration, COUNT_big(*) as cnt FROM results
WHERE (duration between 0 and 7)
union
SELECT 4 id, ''-14'' as duration, COUNT_big(*) as cnt FROM results
WHERE (duration between 0 and 14)
union
SELECT 5 id, ''-30'' as duration, COUNT_big(*) as cnt FROM results
WHERE (duration between 0 and 30)
END
'
exec (@QMODDATE)
May 22, 2014 at 8:16 am
May 22, 2014 at 12:47 pm
I think i found the solution.
I created an indexed view on begindate and did the days duration count in the query, not in a computed column.
The query produces:
cnt duration
13611 1
19038 2
22038 3
SELECT
--begindate,
COUNT(*) cnt,
(datediff(day,[begindate],getdate())) as duration
FROM cb
where Duration < 4
GROUP BY begindate
ORDER BY begindate desc
How would i get just the total cnt in one row (54687)?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply