July 21, 2014 at 2:07 pm
Hello!
I'm not very technical on T-SQL so hoping this is an easy answer for someone. When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly. There must be something I'm doing wrong please!
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROMdbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid
Thanks in advance!
Dave
July 21, 2014 at 4:04 pm
What is the data type of the field agrtid? If it isn't INT, you probably have some implicit conversion going on which can affect performance. Otherwise, it might be a parameter sniffing issue. Try adding a WITH RECOMPILE hint and see if it still takes several minutes.
July 21, 2014 at 4:15 pm
Thanks for the reply, It's int data type in both tables and a unique index in both tables.
July 21, 2014 at 4:45 pm
Hit up the second link down in my signature (index/tuning help). It'll show you how to snag and attach the execution plan (actual is preferred). We'll need that to even start helping you beyond blind guesses. Schema and indexing on the tables involved would also help.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 22, 2014 at 3:11 am
Unfortunately I don't have "showplan" permissions on the database agr.
But if I set the variable @CutOffTid to the value the first select statement returns then it runs instantly. To summarise. The following select runs instantly and returns the number 1000 for @CutOffTid:
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROM dbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
If I run the 2nd select statement as follows it runs instantly
Set @CutOffTid = 1000
DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid
However, if I run it without setting the variable manually and taking the variable from the first select it takes ages to run.
July 22, 2014 at 4:09 am
Have you tried timing each statement?
DECLARE @Starttime DATETIME = GETDATE()
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROM dbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
SELECT Starttime = @Starttime, Endtime = GETDATE()
SET @Starttime = GETDATE()
DECLARE @MinPeriod int
SELECT @MinPeriod = MIN(period)
FROM agr.dbo.atrans
WHERE agrtid > @CutOffTid
SELECT Starttime = @Starttime, Endtime = GETDATE()
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2014 at 4:14 am
The time is on the second select statement when @CutOffTid parameter is set by the first select. The first select runs instantly on its own.
Its like it's not treating the parameter as an INT when its taken from the select but does when you set it manually like SET @CutOffTid = 1000.
July 22, 2014 at 4:19 am
Doubt it has anything to do with the data type. Probably lack of parameter sniffing since SQL can't sniff the value of variables.
Will this be a procedure eventually? Will that value be a parameter?
And why, oh why are you using NoLock? Is the query so unimportant that incorrect results are acceptable?
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
July 22, 2014 at 4:33 am
Thanks for that.
Yes, it will be a stored procedure, could you suggest the code as a SP please?
PS Removed NOLOCK!
July 22, 2014 at 4:44 am
Something like this?
DECLARE @MinPeriod int
SELECT @MinPeriod = MIN(a.period)
FROM agr.dbo.atrans a
CROSS APPLY (
SELECT CutOffTid = ISNULL(MAX(ccas_id),0)
FROM dbo.fbs_trans
) x
WHERE a.agrtid > x.CutOffTid
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2014 at 5:21 am
ChrisM@Work (7/22/2014)
Something like this?
DECLARE @MinPeriod intSELECT @MinPeriod = MIN(a.period)
FROM agr.dbo.atrans a
CROSS APPLY (
SELECT CutOffTid = ISNULL(MAX(ccas_id),0)
FROM dbo.fbs_trans
) x
WHERE a.agrtid > x.CutOffTid
As Chris said, and then just wrap that in a CREATE PROCEDURE with @MinPeriod being a parameter. Should be fine. If not, ask your DBA for an execution plan, and we can evaluate further.
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
July 22, 2014 at 7:40 am
Parameter sniffing was the issue alright.
I added a parameter to the query and because it's run from a main SP I moved the first SQL statement to the main SP and then passed the @CutOFFTid to the sub-SP.
Thanks guys.
Dave
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply