Post reply

Query to run in TEMPDB or Stored Procedure

  • November 17, 2015 at 3:51 am

    #309693

    Two questions:

    * Why is it quicker to create a TEMPDB - select into that tempdb then query it than it is to just query the DB???

    * Can I create the table in a temp stored procedure, will this be quicker and is it possible as the only way I know to populate temp tables in stored procedures is by hard coding them - not by doing a select into.

    Any thoughts greatly appreciated

    Thanks

  • November 17, 2015 at 4:24 am

    #1840255

    Oh wow...

    Ok, there's a whole bunch of problems here.

    Firstly, unless you're dealing with case-sensitive database, the only thing those UPPER functions are doing is slowing things down. Is your database running with a case-sensitive collation?

    Second, all of the branch LIKE @Branch OR Coalesce(@Branch,'') = '') is confusing the hell out of the query optimiser and resulting in really sub-optimal plans. That's probably why the select into is faster, it's forcing recompiles and so you don't get the really bad cached plans.

    I go into a lot more details here: https://www.simple-talk.com/content/article.aspx?article=2280

    In short, remove the UPPER (unless the DB really is case sensitive), rather use the format branch LIKE @Branch OR @Branch IS NULL, and add OPTION(RECOMPILE) to the query.

    All of that though, this is still going to be slow, because of the wild card searches. The leading wildcards will prevent any index usage, meaning this will never be quick.

    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

  • November 17, 2015 at 4:52 am

    #1840264

    Thanks for the response and so quickly as well.

    If I remove the Where clause - this takes 4 seconds longer to run.

    I'll have a look at changing the tables to only have UPPERCASE.

    Thanks

  • November 17, 2015 at 5:05 am

    #1840275

    That's not what I said.

    Unless your database is case-sensitive (is it?), the UPPER is a complete waste of time. The default SQL collations are case-insensitive.

    I also didn't say remove the WHERE clause, returning the entire table isn't a solution. Please read the article I referenced.

    And, in case you missed it:

    In short, remove the UPPER (unless the DB really is case sensitive), rather use the format branch LIKE @Branch OR @Branch IS NULL, and add OPTION(RECOMPILE) to the query.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply