How To: Optimize SQL Query (SQL Server) [closed]

Question

Anyone have any suggestions for how to optimize this query? Running SQL Server 2008

SELECT 
    o.id_testu, 
    o.pub_int_od, 
    p.tk AS stanowisko, 
    p3.tu AS jednostka, 
    ox.id_testu AS lk, 
    ox1.id_testu AS wn, 
    ox2.id_testu AS nd, 
    o.jozus_wprow AS jozus_utw, 
    o.priorytet
FROM 
    dbo.TEST AS o 
    LEFT OUTER JOIN
        dbo.POLA AS px ON 
        px.id = o.id_testu 
        AND px.id_definicji_pola = 143 
    LEFT OUTER JOIN
        dbo.TEST AS ox ON 
        ox.id_testu = px.id_testu 
        AND ox.data_pub IS NOT NULL 
        AND ox.pub_int_od < GETDATE() 
    LEFT OUTER JOIN
        dbo.POLA AS px1 ON 
        px1.id = o.id_testu 
        AND px1.id_definicji_pola = 138 
    LEFT OUTER JOIN
        dbo.TEST AS ox1 ON 
        ox1.id_testu = px1.id_testu 
        AND ox1.data_pub IS NOT NULL 
        AND ox1.pub_int_od < GETDATE() 
    LEFT OUTER JOIN
        dbo.POLA AS px2 ON 
        px2.id = o.id_testu 
        AND px2.id_definicji_pola = 147 
    LEFT OUTER JOIN
        dbo.TEST AS ox2 ON 
        ox2.id_testu = px2.id_testu 
        AND ox2.data_pub IS NOT NULL 
        AND ox2.pub_int_od < GETDATE() 
    INNER JOIN
        dbo.POLA AS p ON 
        p.id_testu = o.id_testu 
        AND p.id_definicji_pola = 129 
    INNER JOIN
        dbo.POLA AS p3 ON 
        p3.id_testu = o.id_testu 
        AND p3.id_definicji_pola = 127
WHERE
    (ox1.pub_int_do > GETDATE()) 
    AND (o.id_typu_testu = 8) 
    AND (o.czy_gotowe = 1) 
    AND (o.data_apr IS NOT NULL) 
    AND (o.data_superapr IS NOT NULL) 
    AND (o.data_pub IS NOT NULL) 
    AND (o.pub_int_od < GETDATE()) 
    OR (o.id_typu_testu = 8) 
    AND (o.czy_gotowe = 1) 
    AND (o.data_apr IS NOT NULL) 
    AND (o.data_superapr IS NOT NULL) 
    AND (o.data_pub IS NOT NULL) 
    AND (o.pub_int_od < GETDATE()) 
    AND (ox1.data_pub IS NULL) 
    OR (ox1.pub_int_do < GETDATE()) 
    AND (o.id_typu_testu = 8) 
    AND (o.czy_gotowe = 1) 
    AND (o.data_apr IS NOT NULL) 
    AND (o.data_superapr IS NOT NULL) 
    AND (o.data_pub IS NOT NULL) 
    AND (o.pub_int_od < GETDATE()) 
    AND (ox2.pub_int_do > GETDATE()) 
    AND (ox2.data_pub IS NOT NULL)

Answer 1

The overall optimization process consists of two main steps:

• Isolate long-running queries.
• Identify the cause of long-running queries.

Step 1. Isolate Long-Running Queries

You can isolate long-running queries using SQL Profiler. For more information about how to identify the queries that take the longest to execute, see "Isolating a Slow-Running Query with SQL Profiler" in "How To: Use SQL Profiler" in the "How To" section of this guide.

Step 2. Identify the Cause of Long-Running Queries:

Several techniques can be used to identify the cause of long-running queries. The two most commonly used options are:

• Using SET statements.
• Using SQL Query Analyzer options.

Using SET Statements: Use such statements as SET SHOWPLAN_ALL, SET STATISTICS IO, SET STATISTICS TIME, and SET STATISTICS PROFILE. For more information about using these SET statements, see the SQL Server product documentation.

Using SQL Query Analyzer: SQL Query Analyzer displays query execution plans in text mode or graphical mode.

To use SQL Query Analyzer

• Start SQL Query Analyzer, connect to the server, and select the database that you are working on.
• Paste the query into the SQL Query Analyzer window.
• If you are using SQL Profiler to trace queries, the query text can be copied from the trace window and used within SQL Query Analyzer.
• On the Query menu, click Display Estimated Execution Plan. The estimated execution plan for the query is displayed. If the query window contains multiple queries, an execution plan is displayed for each query.
• On the Query menu, click Show Execution Plan, and then run the query in the query window. Execution plans and query results now appear in separate panes of the window so you can view them together.
• Place the mouse pointer over any icon displayed in the query execution plan. Details of the query step are displayed, including information about the execution and cost.

Before Optimize Query "write your query so you can read it... if you can't read it how can you possibly optimize it?" @Jacob H Thanks.

The complexity of the results can vary depending on the nature of the query. The following list identifies some common things to look for:

• Red icons and warning messages: Look for icons that are color-coded red, and for warning messages. You might see a warning message such as "Warning: Statistics missing for the table." If the Physical operation in the query step details is in red, then it indicates that the query optimizer has chosen a less efficient query plan. The graphical execution plan suggests remedial action for improving performance. In the case of missing statistics you can right-click the icon, and click Manage Statistics to create the missing statistics.

• Estimated cost: The estimated cost values indicate whether the query is I/O intensive or CPU intensive.

• Table scan and clustered index scan icons: Look for table scan and clustered index scan icons, which indicate either that the table is small (not a problem), that the indexes are not properly designed, or, if you have indexes in place, that the optimizer has ignored the indexes. The Index Tuning Wizard can be used to identify the indexes needed. You need to ensure that you drop all hints before proceeding with the Index Tuning Wizard.

• Queries with the highest cost: When a batch of queries is executed, a query plan is displayed for each query. The query cost is displayed for each query relative to the batch. Concentrating optimization effort on the highest relative cost query in a batch may yield the best improvements. Some queries are inherently resource intensive — for example, queries that return large number of rows back to the caller, or queries that perform many calculations. In some cases, the only way to improve performance is to redesign the database or rewrite the query.