Runnvarchar manually no variable improve speed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Runnvarchar manually no variable improve speed

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-27 : 10:41:08

Hi. I am having this query


	DECLARE @DateFrom as DateTime,
@DateTo as DateTime



SET @DateFrom = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET @DateTo = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))




		declare @datetimefrommv as nvarchar(30)
		declare @datetimetomv as nvarchar(30)


SET		@DateFrom		= CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET		@DateTo			= CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))




 set @datetimefrommv = CONVERT(VARCHAR(30),DATEADD(hh,-6,getdate()),112)
 set @datetimetomv = CONVERT(nvarchar(30), DATEADD(hh,-6,getdate())+1, 112)
  set @datetimefrommv = @datetimefrommv + ' 00:00:00.000'
  set @datetimetomv = @datetimetomv + ' 00:00:00.000'
 select @datetimefrommv 
 select @datetimetomv
 --select @datetimetomv



		
	SELECT	@DateFrom, @DateTo,
			C.Cinema_strCode, 
			C.Cinema_strName,
			CASE S.CinOperator_strCode 
			WHEN 'COSO' THEN 1
			WHEN 'RENO' THEN 1
			ELSE 0 END AS IsCool,
			F.HOPK as Film_strCode,
			F.Film_strTitleAlt,
			COUNT(*) AS Admissions
	FROM tblTrans_Ticket T
	Left Outer JOIN tblSession S 
		ON S.Session_lngSessionID = T.Session_lngSessionID
	LEFT OUTER JOIN tblCinema_Operator CO
		ON CO.CinOperator_strCode = S.CinOperator_strCode
	LEFT OUTER JOIN tblCinema C
		ON CO.Cinema_strCode = C.Cinema_strCode
	LEFT OUTER JOIN tblFilm F
		ON F.Film_strCode = S.Film_strCode
		
	WHERE	T.TransT_strType='P' 
			AND T.TransT_strStatus='V'
		AND S.Session_dtmRealShow BETWEEN @DateFrom AND @DateTo
			AND S.Session_dtmRealShow BETWEEN '20141027 00:00:00.000' AND '20141028 00:00:00.000'
		--	AND S.Session_dtmRealShow BETWEEN LEFT(CONVERT(VARCHAR, @DateFrom, 120), 20) AND LEFT(CONVERT(VARCHAR, @Dateto, 120), 20)
			--	AND S.Session_dtmRealShow BETWEEN @datetimefrommv AND @datetimetomv
			
			
	GROUP BY C.Cinema_strCode, C.Cinema_strName,
	         CASE S.CinOperator_strCode 
			 WHEN 'CS' THEN 1
			 WHEN 'RN' THEN 1
			 ELSE 0 END,
			 F.HOPK, F.Film_strTitleAlt

So what i do is 3 things. I am using a datetime in:
AND S.Session_dtmRealShow BETWEEN @DateFrom AND @DateTo
nvarchar variable in:
AND S.Session_dtmRealShow BETWEEN @datetimefrommv AND @datetimetomv
and finally manually nvarchar in:
AND S.Session_dtmRealShow BETWEEN '20141027 00:00:00.000' AND '20141028 00:00:00.000'
The last one runs in one second. The others in 2 minutes.
I am trying to simulate the manual '20141028 00:00:00.000' with nvarchar variables:
@datetimefrommv AND @datetimetomv but it will do 2 minutes run.
What is the deal here and any advice?
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-27 : 10:53:50

What is the datatype of column Session_dtmRealShow ?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-27 : 20:13:35

Datetime

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 09:10:34

Well, since @datetimefrommv and @datetimetomv are varchar, there will be an implicit conversion in the second query (though not in the first one). Assuming all three queries yield identical results (do they?) there could be a difference between versions 2 and 3. Plus I see that you set @datefrom like this:


SET		@DateFrom		= CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))

but you set @datetimefrommv like this:


set @datetimefrommv = CONVERT(VARCHAR(30),DATEADD(hh,-6,getdate()),112)

which is not exactly the same. So you're not really comparing apples to apples. Also, why the round-trip through varchar in


CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))

Finally, is Session_dtmRealShow an indexed column? If so, the second query may not use the index since it is not a SARG. Please check your query plans for all three and examine the differences.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-28 : 11:15:04

These where just some test i made.My question was that i saw that a @variable nvarchar run slower than just hand writing the nvarchar in the AND.
I am not sure if it is an indexed column, don't think so but will have to check tomorrow.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources