SQL Server Performance

Tuning some SQL's no detected by Profiler or Anal.

Discussion in 'Contribute Your Performance and Clustering Tips' started by Luis Martin, Sep 10, 2003.

  1. Luis Martin Moderator

    Some time a client need more perfomance in particular report.

    I find (allmost 3rd party software in witch I can't modify a single line) some SQL's no detected by Profiler or Query Analizer (even execution plan).
    Suchs SQL's made a temporary table to collect the data to be printed, like:

    Create table #Report .....

    The following, after create table, is a long select with join, etc.

    Profiler or Analyzer can't undertand auxiliars tables. So I find those via Profiler, cut and paste into Analyzer, erase the first part (create table #...) and tuning the rest.

    Some times performance improve 10 times or more.

    Luis Martin




    Luis Martin, Sep 10, 2003
    #2

  2. gaurav_bindlish New Member

    I agree with you Luis. I have seen multiple instances of third party applications using temporary tables and ad-hoc queries. I guess they still have to learn the basics of perofrmance tuning.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    gaurav_bindlish, Sep 10, 2003
    #2

  3. Twan New Member

    I am amazed at the number of people still writing dynamic SQL within the client and then passing it on to SQL, rather than using stored procedures...

    Also I find it odd that people use n-tier models simply because it seems the normal thing to do rather than if it is the appropriate thing to do... n-tier is great for scalability but performance wise it ain't likely to beat good old 2-tier...

    Cheers
    Twan
    Twan, Sep 10, 2003
    #2

Share This Page