Where clause Integer Greater than

  • 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

  • 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.

  • Thanks for the reply, It's int data type in both tables and a unique index in both tables.

  • 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.


    - Craig Farrell

    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

  • 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.

  • 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()

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for that.

    Yes, it will be a stored procedure, could you suggest the code as a SP please?

    PS Removed NOLOCK!

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • ChrisM@Work (7/22/2014)


    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

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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