none
UNION ALL Performance Issue

    Question

  • I have two somewhat complex SELECT statements that use a number of inline table-value functions. On their own (running each SELECT individually) their performance is acceptable. Once I UNION ALL them together the queries run for hours. Any thoughts?
    Wednesday, September 25, 2013 5:50 PM

All replies

  • Use temporary table instead of the UNION ALL.
    Wednesday, September 25, 2013 5:56 PM
  • Can't use temp tables in an ILTVF. To support the required interface this must be contained within an ILTVF.
    Wednesday, September 25, 2013 7:20 PM
  • Check the indexes and statistics on the tables that may be your bottleneck..

    Wednesday, September 25, 2013 7:27 PM
  • The quality of the responses is directly related to the amount of useful information you provide in your description.  Since performance is a cost-based issue, there is little to reply with beyond what may seem to be pointless generalities.  In addition, you are not helping yourself with your unusual abbreviations - I had to think about ILTVF for a moment. 

    Secondly, Stefan was suggesting (at least as far as I interpret it) that you store the resultsets of the individual select statements that are members of the union into temp tables.  I'm certain Stefan is well aware that you cannot use temp tables inside a inline TVF.   Based on your comments it sounds like you have multiple levels of nesting in your functions.  Is that true?  It may be that you have simply gone too far in trying to modularize your logic; the query optimizer does have its limits.

    Next, you say that "to support the required interface...".  If you must live within limitations it would best to state those up front.  It would also be useful to know why you must use inline functions vs. multi-statement functions.  I cannot see a reason why one is "supported" and the other not.

    Next, you say that the performance of the individual statements is "acceptable" but not acceptable when part of this union.  So is the perfomance of the union query significantly worse than the sum of the individual statements?  Does the union include a sort?  Lastly, are you on a current version of sql server and are you current with the service packs?

    Wednesday, September 25, 2013 7:50 PM
  • I'm sorry you had to think about what ILTVF was. I had spelled it out in the first post. Figured most would figure it out quickly as TVF is a common acronym. 

    There are not multiple levels of nesting.

    I stated that the SELECTs were contained within the ILTVFs. Moving this to a multi-statement function does not solve the problem nor do I see why it would/should.

    The performance of the individual queries is within a few minutes. After UNIONing them it runs for hours but I have not let it run long enough to complete. There are no sorts. SQL Server 10.50.4000.


    Wednesday, September 25, 2013 8:26 PM
  • Well, what about a concise and concrete example? What cardinalities are involved? Is it an OLTP or an OLAP case?
    Thursday, September 26, 2013 9:49 AM
  • Hi , it's really difficult to help without the code that you are using but i know that "OUTER JOIN" can be an alternative to using "UNION ALL" in some situations.

    Thursday, September 26, 2013 10:52 AM
  • I'm sorry you had to think about what ILTVF was. I had spelled it out in the first post. Figured most would figure it out quickly as TVF is a common acronym. 

    There are not multiple levels of nesting.

    I stated that the SELECTs were contained within the ILTVFs. Moving this to a multi-statement function does not solve the problem nor do I see why it would/should.

    The performance of the individual queries is within a few minutes. After UNIONing them it runs for hours but I have not let it run long enough to complete. There are no sorts. SQL Server 10.50.4000.


    In case if the temp tables and query separation not feasible , can you try splitting the single ILTVF to two ILTVFs as the two select queries are merged using UNION ALL?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 26, 2013 12:49 PM
  • Good SQL programmers do not use TVP or temp tables. These constructs are how bad SQL programmers mimic 1960's scratch tapes in a procedural COBOL program. The UNION ALL is probably a tape merge. 

    Since you were so rude as to not post DDL or sample data, we cannot help you turn this into real SQL. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Dean Savović Thursday, September 26, 2013 5:59 PM
    Thursday, September 26, 2013 4:00 PM
  • I stated that the SELECTs were contained within the ILTVFs.

    No you didn't.

    The performance of the individual queries is within a few minutes. After UNIONing them it runs for hours but I have not let it run long enough to complete.

    The obvious next step would be to inspect the query plans of the individual queries and the query plan of your inline TVF.

    I'd also like to quote Scott: "The quality of the responses is directly related to the amount of useful information you provide in your description".

    For example, there is a structural difference between using literal, variables and parameters in queries. You will only be comparing apples with apples and oranges with oranges if both the individual queries as well as the inline TVF are using literals for the same predicates, variables for the same predicates and parameters for the same predicates.

    In other words (and to repeat Scott again), without seeing any of the code, or any query plan, all anyone can do is make wild guesses.


    Gert-Jan

    Thursday, September 26, 2013 10:12 PM