I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve quite a lot of calculation and rows. I put all the merge statements into one long stored procedure and ran it, but it took an impossibly long time. I made some covering indexes and some other changes, which made barely any difference; I was still manually stopping the job after over an hour of running.

Acting on general intuition, I split the long script into seven separate stored procedures and called them one by one with an over-arching stored procedure. The whole lot consistently finishes within 3 minutes (no code changes were made to the scripts).

My question is this - is there a general set of conditions in which it is always so much significantly faster to split a script up into smaller steps - is there some feature of the scripts / server memory or other factor that will mean splitting is occasionally so much more effective than at other times?

Alternatively, is it just that it is always better to split scripts up like this (assuming no problems with naming / SP proliferation etc), and the huge difference in speed is nothing unusual?

My original script was large and would be hard to sanitise sufficiently to show here, but I can give some details: It is about 550 lines, there were many derived tables, CTEs, and GROUP BY clauses, with a couple of relatively complex WHERE clauses and 1 temp table. There were 8 Merge statements, all targeting the fact table, but sourcing from 3 databases on the same server as the target. Maybe there is something in this which would have particularly slowed things down.

  • 2
    Can we see the script? – James Anderson Apr 28 '16 at 9:15
  • Is the original long script using variables that have become parameters in the new arrangement? – Martin Smith Apr 28 '16 at 9:22
  • The old script did use variables; they were correctly typed and declared, but not used as parameters. In the second, the over-arching SP loaded up the variables at the beginning and then each called SP used a relevant selection as parameters. – High Plains Grifter Apr 28 '16 at 10:01
up vote 3 down vote accepted

Your comment confirms that the original long script was using variables and these have become parameters.

Probably you are benefitting from parameter sniffing here and better execution plans. The values for variables are not sniffed unless you use the ’option recompile' query hint so you will get the same plan regardless of what the runtime values are.

When you parameterize it SQL Server sniffs the parameter value at the time the plan for the statement is actually compiled and can look in the column statistics using these actual values. Based on that you can get hopefully better row estimates and a more suitable plan.

As a side note parameter sniffing can have its own problems if the plan is cached and the procedure is executed again with a different parameter value as the original plan might not be optimal for the new parameter value.

  • Does this imply that I should parameterise all variables whenever possible, as declarations made within the script will be less efficient than those made as parameters? – High Plains Grifter Apr 28 '16 at 10:40
  • 1
    @High no. In fact some people regularly do the exact opposite and assign parameters to variables to defeat parameter sniffing because of the side note. Using 'option recompile' with variables in your original script would have worked just as well and avoided the side note issue but then you pay for extra compilation time which might not be needed. Probably in your case it would be negligible compared to overall execution time without though. – Martin Smith Apr 28 '16 at 10:52

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.