SQL Server Performance Forum – Threads Archive
Using Cast will affect performance?
Hi,<br /><br />I have a syntax below to specify a condition…<br /><br />and (<b>cast</b>(left(stat_period,4)+’-‘+substring(<b>cast</b>(stat_period as varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),5,2)+’-01’as datetime)) <b>>=</b> <br />dateadd(mm,-25,(dateadd(dd,-(day(getdate())-1), DATEADD(d,DATEDIFF(d,0,getdate()),0))))<br /><br />In the query the left hand side has two cast function and on the right hand side i am not using the cast instead using available date functions in sql server.<br /><br />My question, if I use available date functions on left hand side by removing cast, will the performance increase? <br /><br />Please suggest me as I need to implement this in production.<br /><br />Also suggest any articles regarding using date,cast & convert functions.<br /><br />Thanks & Regards,<br />BalaI feel replacing cast with another sql date function will not affect your code performance by much.
One way of constructing a date from bits of information is by nesting a few DATEADDS. This doesn’t need casting in your query, as it produces a true date:
DATEADD(m, <month> – 1, DATEADD(d, <day>, DATEADD(yyyy, <year> – 1900, 0))) The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC). Since you’re looking for the first of the month, you can do just the month and year:
DATEADD(m, RIGHT(@stat_period, 2) – 1, DATEADD(yyyy, LEFT(@stat_period, 4) – 1900, 0)) Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query. Seeing that your stat_period is a formatted string like ‘yyyy-mm’, you could probably also use this: DECLARE @calc_date CHAR(10)
SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
…………………………………………
WHERE (stat_period + ‘-01’) >= @calc_date You’ll have to see which one performs better.
Adrian, Thanks a lot, I am using the following syntax as you suggested. It does make sense to use this way.
DECLARE @calc_date CHAR(10)
SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120) -Bala
quote:Originally posted by Adriaan
One way of constructing a date from bits of information is by nesting a few DATEADDS. This doesn’t need casting in your query, as it produces a true date:
DATEADD(m, <month> – 1, DATEADD(d, <day>, DATEADD(yyyy, <year> – 1900, 0))) The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC). Since you’re looking for the first of the month, you can do just the month and year:
DATEADD(m, RIGHT(@stat_period, 2) – 1, DATEADD(yyyy, LEFT(@stat_period, 4) – 1900, 0)) Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query. Seeing that your stat_period is a formatted string like ‘yyyy-mm’, you could probably also use this: DECLARE @calc_date CHAR(10)
SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
…………………………………………
WHERE (stat_period + ‘-01’) >= @calc_date You’ll have to see which one performs better.
DATEADD(m, <month> – 1, DATEADD(d, <day>, DATEADD(yyyy, <year> – 1900, 0))) The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC). Since you’re looking for the first of the month, you can do just the month and year:
DATEADD(m, RIGHT(@stat_period, 2) – 1, DATEADD(yyyy, LEFT(@stat_period, 4) – 1900, 0)) Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query. Seeing that your stat_period is a formatted string like ‘yyyy-mm’, you could probably also use this: DECLARE @calc_date CHAR(10)
SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
…………………………………………
WHERE (stat_period + ‘-01’) >= @calc_date You’ll have to see which one performs better.
Hi Adrian, Left hand side I have date time and the right hand side we have converted into char(10)…while checking the condition does it affect any performance?
DateTime >= Char(10) Regards,
Balasundaram
quote:Originally posted by Adriaan
One way of constructing a date from bits of information is by nesting a few DATEADDS. This doesn’t need casting in your query, as it produces a true date:
DATEADD(m, <month> – 1, DATEADD(d, <day>, DATEADD(yyyy, <year> – 1900, 0))) The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC). Since you’re looking for the first of the month, you can do just the month and year:
DATEADD(m, RIGHT(@stat_period, 2) – 1, DATEADD(yyyy, LEFT(@stat_period, 4) – 1900, 0)) Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query. Seeing that your stat_period is a formatted string like ‘yyyy-mm’, you could probably also use this: DECLARE @calc_date CHAR(10)
SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
…………………………………………
WHERE (stat_period + ‘-01’) >= @calc_date You’ll have to see which one performs better.
DATEADD(m, <month> – 1, DATEADD(d, <day>, DATEADD(yyyy, <year> – 1900, 0))) The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC). Since you’re looking for the first of the month, you can do just the month and year:
DATEADD(m, RIGHT(@stat_period, 2) – 1, DATEADD(yyyy, LEFT(@stat_period, 4) – 1900, 0)) Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query. Seeing that your stat_period is a formatted string like ‘yyyy-mm’, you could probably also use this: DECLARE @calc_date CHAR(10)
SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
…………………………………………
WHERE (stat_period + ‘-01’) >= @calc_date You’ll have to see which one performs better.
Hi Balasundaram, As I said, you’ll have to see which one performs better. The options involve either date calculation or string concatenation, neither of which is ideal when used in criteria.
]]>