-
Oct 29th, 2014, 10:37 AM
#1
Runnvarchar manually no variable improve speed
Hi. I am having this query
Code:
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|