Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: variables in WHERE clause cause massive slowdown?

    I have a large table and a query that SELECTs from it, but it runs extremely slowly. Ive been experimenting with making it faster and I just came across something that surprised the crap out of me.

    Code:
    declare @t1 datetime
    select @t1 = '2010-08-20'
    declare @t2 datetime
    select @t2 = '2010-08-22'
    
    select 
      ... columns ...
    from
      BIGTABLE
    where 
      timestamp >= @t1
    and
     timestamp <= @t2
    The above snippet takes 14 minutes to complete on this particular big-ass table. The below snippet - which seems awefully similar to me - completes in 2 seconds. Can someone please explain this mind boggling speed difference of about 40000 % ?

    Code:
    select 
      ... columns ...
    from
      BIGTABLE
    where 
      timestamp >= '2010-08-20'
    and
     timestamp <= '2010-08-22'

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm pretty sure that the difference isn't in the code that you posted, but in the code that you edited out. Look at the query plans, the query plans will show you both whether or not my guess is correct and also why there is a performance difference.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2010
    Posts
    75
    Thanks Pat.

    Its not like the code I left out is different for each query. The queries are 100% identical except for the dates being in a variable in one, and a hardcoded value in the other. In fact I get the same results if I just do SELECT *, so here are the ENTIRE queries, with nothing edited out.

    Code:
    declare @t1 datetime
    select @t1 = '2010-08-20'
    declare @t2 datetime
    select @t2 = '2010-08-22'
    
    select 
     *
    from
      BIGTABLE
    where 
      timestamp >= @t1
    and
     timestamp <= @t2
    vs

    Code:
    select 
      *
    from
      BIGTABLE
    where 
      timestamp >= '2010-08-20'
    and
     timestamp <= '2010-08-22'

    Yes I know there are other things you dont know about my database - what indexes I have etc etc. But I dont understand how anything matters because whatever the other factors are, they are the same for both of these queries.

    I ran the query plan, and for both queries it said 100% of the time was in clustered index lookup. Note, the clustered index is timestamp.

    It seems to me that the problem is that for each and every row in the table, the @t1 and @t2 variables are being converted to a datetime all over again. This is the only thing I can think of, am I way off the mark?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "all over again"? What datatype is "timestamp"?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2010
    Posts
    75
    timestamp is a datetime.

    I know my theory doesnt make a lot of sense, but to me it has to have something to do with the 2 datetime variables because they are the ONLY difference.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only thing I can offer is review the query plans... See where the plans are different. The answer ultimately lies somewhere in those query plans.

    I really hope that you did NOT really mean timestamp as a Primary Key! That would be almost unimaginably bad! It could (depending on caching) cause exactly the effect that you're describing though.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wait a second! Change the name of the column timestamp to something else, then modify and re-run the query. That might solve the problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2010
    Posts
    75
    Nope, timestamp is the clustered index. The primary key is a different column (bigint) that is unique.

    The query plans are not different. I'll keep investigating though, there are some other profiling tools I havnt tried yet.

    Im also going to look into what the problems WOULD be if I had a datetime primary key. Im STILL convinced that no matter how badly I choose my primary keys and indexes, none of that should matter. The 2 queries I posted should have exactly the same runtimes. If I had a poorly chosen primary key, then sure maybe the queries would run extremly slow, but I would still expect them to run EQUALLY slow.

    Arrg! this is frustrating. Thanks for the help so far.

  9. #9
    Join Date
    Feb 2010
    Posts
    75
    Quote Originally Posted by Pat Phelan View Post
    Wait a second! Change the name of the column timestamp to something else, then modify and re-run the query. That might solve the problem.

    -PatP
    Sorry, Im guilty of hiding information (I hate it when people do that too) - the column is actually called transaction_timestamp, not just timestamp.

    Also the table is not actually called BIGTABLE. Do you think that could be part of the problem? My god I never realized how flakey and sensitive SQL Server was.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In SQL Server, timestamp is a reserved word like SELECT or INT. Using a reserved word as a name (column, table, view, or anything else) is a bad idea in any language, not just Transact-SQL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a side note, this discussion illustrates exactly why I'm convinced that the problem lies outside of the code that you've posted. Without realizing what's important, there isn't any way for you to filter the "important" from the "junque" so my guess is that there is some critical detail that we don't have that is causing the problem.

    Go back to the query plan. A graphic plan is good, but a text query plan is much better. The text plan is harder for a novice to read, but it provides far more detail and I suspect that for this problem the devil lies in the details!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, you can save graphical plans as .sqlplan. This is XML that we can view, or we can open in SSMS and see all the pretty pictures.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •