Post reply

Uber SQL Union Query

  • January 6, 2004 at 5:01 am

    #106828

    Hi,

    I've got a huge Union query as the result of using the SQL XML functionality of SQL2K. There are (gulp) 38 separate queries all unioned together. It all works though and does return what I want.

    However, there are serious performance issues (no surprise there) and these need addressing. I will be able to cache the data returned if necessary so I can get around it, it's just that the first hit to any query is going to take about a minute (this is a web based app and this is with just one person hitting the database !).

    So...

    1. When you have a repeated query to a table, e.g., the root element in the XML, is it quicker to do a single query first to return the record and store the values in variables or a temporary table and then reference that rather than relying on any internal caching that SQL Server is doing ?
    2. When joining multiple tables (that are all indexed (the quality of the indexing may be iffy as well!)) is there a performance penalty to no specifying in the joins all of the indexed values ?
    3. When you are using an index and one of the join clauses is either a >= or a between, does the index get used or should I build an index only on values that are specifially declared.

    thanks for the help in advance,

    cheers

    Andy

  • January 6, 2004 at 5:36 am

    #488782

    1 - not yet my domain

    2 - t-sql your full query with "show execution plan" / "show estimated execution plan"

    3 - no can tell, depents on your statistics and cardinality, ...

    you might also try to post the full DDL and your full union-statement, so we could give it a try to optimize it.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 6, 2004 at 12:12 pm

    #488855

    With respect to your first question.  It is very likely that repeated queries to the same table may be faster if you "do a single query first to return the record and store the values in variables or a temporary table and then reference that rather than relying on any internal caching that SQL Server is doing ?"  I would look at table variables rather tham temporary tables as table variables may provide even better performance that temp tables. 

    Testing is the best way to determine if this is faster or not.

    Francis

  • January 7, 2004 at 12:51 pm

    #489034

    I second the use of TABLE variables. In my own application, the use of table variables improved performance dramatically (2x or greater in many cases). Also, regarding indices, if you index fields A, B, C in that order, then a constraint on A and B or just A MAY exploit the index. (In some cases, depending on statistics, even querying on B may use the index, an optimization not found in many databases, but advertised as applicable in SQL Server.) But SHOW PLAN will tell you whether the INDEX is actually being used. You may need to use a hint to force it if it is not being used.

    I also strongly recommend the book "SQL Server Query Performance Tuning Distilled" by Sajal Dam, published by Curlingstone. One of their tips was using "SET STATISTICS IO ON" to gather info on the number of Reads being performed. Sometimes show plan yields a plan that looks good, but an inferior plan may actually perform fewer reads in some instances, and the number of reads is usually the key indicator for performance.

    - Paul

     

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

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