Query Performance - Inner Join

Question

I have this query and I want to improve performance:

SELECT 
    OrarioA, 
    OrarioB,
    IDOrario,
    IDDettaglioOrarioA, 
    IDDettaglioOrarioB
FROM
(
    SELECT 
        Tb_01.Orario AS OrarioA,
        Tb_02.Orario AS OrarioB,
        Tb_01.IDDettaglioOrariLinee AS IDDettaglioOrarioA,
        Tb_02.IDDettaglioOrariLinee AS IDDettaglioOrarioB,
        Tb_01.IDOrario,
        ROW_NUMBER() OVER (PARTITION BY Tb_01.Orario, Tb_02.Orario ORDER BY Tb_01.IDOrario DESC) AS Row
    FROM
        (
            SELECT Orario, IDDettaglioOrariLinee, IDOrario 
            FROM DettaglioOrariLinee 
            WHERE IDRelLineeStazionamenti = @IDRelA
        ) AS Tb_01
            INNER JOIN
        (
            SELECT Orario, IDDettaglioOrariLinee, IDOrario 
            FROM DettaglioOrariLinee 
            WHERE IDRelLineeStazionamenti = @IDRelB
        ) AS Tb_02
            ON Tb_01.IDOrario = Tb_02.IDOrario
            INNER JOIN
        (
            SELECT IDOrario 
            FROM Periodi 
            WHERE 
            (
                @Data = 0 OR
                (
                        @Data >= CAST(CAST(DATEPART(DAY, PeriodoDal) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoDal)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
                    AND 
                        @Data <= CAST(CAST(DATEPART(DAY, PeriodoAl) AS  VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoAl)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
                )
            )
        ) Tb_Periodi
            ON Tb_01.IDOrario = Tb_Periodi.IDOrario        --dbo.periodi ON Tb_01.IDOrario = dbo.periodi.IDOrario
            INNER JOIN                                     --dbo.relgiornisettimanaorarilinee ON Tb_01.IDOrario = dbo.relgiornisettimanaorarilinee.IDOrario
        (
            SELECT IDOrario 
            FROM relgiornisettimanaorarilinee
            WHERE @IDGiorno = 0 OR IDGiorno = @IDGiorno
        ) Tb_Giorni
            ON Tb_01.IDOrario = Tb_Giorni.IDOrario
    WHERE
        (
            @Orario = '' OR DATEDIFF(minute, CAST(@ORARIO AS DATETIME), CAST(Tb_01.Orario AS DATETIME)) >=0
        ) AND (
            DATEDIFF(minute, CAST(Tb_01.Orario AS DATETIME), CAST(Tb_02.Orario AS DATETIME)) >=0
        ) AND (
            @IDOrari = '' OR Tb_01.IDOrario NOT IN (SELECT CAST(s AS INT) AS IDOrario FROM dbo.Split(',', @IDOrari) AS Split_1)
        )
        /*
        AND
            (
                   @Data = 0
                OR
                    (
                            @Data >= CAST(CAST(DATEPART(DAY, PeriodoDal) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoDal)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
                        AND 
                            @Data <= CAST(CAST(DATEPART(DAY, PeriodoAl) AS  VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoAl)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
                    )
            )
        AND
            (@IDGiorno = 0 OR IDGiorno = @IDGiorno)
        */
) As Tb_New
WHERE ROW = 1

OPTION (MAXRECURSION 0);

I want to filter Tb_Periodi And Tb_Giorni with IDOrario.

How can I improve this query?

Answer 1

When optimizing performance, it's usually better (and easier) to measure where the bottlenecks are first. Have you tried using the Query Analyzer?

Answer 2

Well look at the execution plan to see what it is doing. You may find some indexes will help, but looking at that code I doubt it will be that simple.

One thing that immediately jumps to mind that will help improve preformance is to change your structure to properly store dates in a datetime data type. That would get rid of alot of that cast and convert stuff whihc has to act on every row especailly inthe where clause where it has to join that stuff together into a date for every row in order to apply the where conditions. If you are doing any data operations at all, you must store as datetime datatypes or you can expect poor performance.

Answer 3

OK,

1. Does the query actually WORK? No point trying to improve the performance of a query returning the wrong result sets.

2. Do you have a list of test plans with known result sets that you can compare. SQL query improvement is a VERY good example of test driven development as it can be VERY easy to introduce bugs (wrong results) when re-structuring a query.

3. Describe what you expect the query to do IN ENGLISH - give us a chance to understand the purpose of the query.

4. Descrive your dataset (size, indexes, data distribution)

5. What are your expectations? Should this query complete in 1 second, 1 minute, 1 hour? How long does it take? How many times does it get called (many times a second or once a week?)

I don't think it's fair that your question is down modded - It's a valid question but just needs more information. Good Luck.

Answer 4

Without knowing the structures of tables and what indexes or statistics exist on them, it would be very difficult to troubleshoot query performance. As suggested, your best first step it to take a look at the execution plan and determine where the majority of your the cost is. A common fix is to implement additional indexes to reduce table scans.

Query tuning is somewhat of an art, so you many not find a single "correct" consensus on the matter.

Answer 5

As others have mentioned, it's difficult to offer suggestions without knowing all the facts. In addition to the suggestions that others have given, if you are running this query often, or using some of the inner queries in other queries, you might benefit from turning them into views.

Answer 6

Yes. I'm using sql server 2005. It's query called many times, in order 50 times per page request It's in for-cycle. Is it possible to use cache. I don't know increase performance. I've tried everything!

I've noticed that some query are repeated many times in the cycle, accordin to you how can I take advantage of this?

Answer 7

Nice code :-o

As many have said, its hard to help you when we know nothing about data or what you are trying to achieve.

But I can give you some pointers:

• Check http://sql-server-performance.com for info on who to analyse and improve performance of queries. They also have a forum, but unless you give more info they will not likely be able to help you neither.

• OR's in WHERE or ON clauses tends to lead to bad performance

• IN also tends to lead to bad performance
• Functions (UDF or built in) in WHERE or ON clauses is also bad for performance.

You combine all of the above, so Im not surprised.. Also, subqueries and derived tables (which you also use) doesn't necessarily lead to bad performance, but used wrong it definitely can :-)

For beginners of T-SQL, I suggest to keep it simple. For example, split the big query on smaller ones that you understand, using variables or temporary tables to hold data.

Many smaller queries can lead to worse performance than a big good one, but not understanding your code or why it gives bad performance will be worse :)

Good luck

Answer 8

I saw your note on caching. Of course it's possible, just create a table for the result and insert the result of the query into the table.

Be sure to clear the cache table when required (for example by using triggers, or other means)

Depending on your system, using a cache is a good or bad option, I cant say.

Also, if this code is not already in a stored procedure, put it in one as it makes it easier for SQL Server to cache plans etc.

Id suggest fixing the bad code rather than hiding the problem with a cache.

cheers

/L

Answer 9

I suspect that this will end up being a database design issue more than a query optimization issue. Nonetheless, the steps to analyze this query have all been outlined above. To reiterate:

0) Consider the logic of your app, you may not need to run this query nearly as many times as you are currently, or you may find that you don't need the data in the same format that you're currently producing it, allowing you to write a simpler and faster query. I'm going to assume that you need this query though.

1) generate the execution plan for the query for a sample run. (press the include execution plan button or Ctrl+m) Look for portions of the the execution plan that are taking a lot of work, and see if you can tie those back to a specific part of the query. This will give us the clues on where to focus our efforts. Play around with these portions of the query and see what you can come up with.

One thing that I can see that might help performance (depending on the specifics of your data) is to try and force the order of data access to the DettaglioOrariLinee table. For example:

    ;WITH DettaglioOrariLineePeriodi AS
(
     SELECT Orario, IDDettaglioOrariLinee, IDOrario 
     FROM DettaglioOrariLinee
     WHERE IDOrario IN ( SELECT IDOrario --This is still an inner join
          FROM Periodi 
          WHERE 
          (
           @Data = 0 OR
           (
            @Data >= CAST(CAST(DATEPART(DAY, PeriodoDal) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoDal)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
            AND 
            @Data <= CAST(CAST(DATEPART(DAY, PeriodoAl) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoAl)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
           )
          )
         )
)
SELECT 
    OrarioA, 
    OrarioB,
    IDOrario,
    IDDettaglioOrarioA, 
    IDDettaglioOrarioB
FROM
(
    SELECT 
     Tb_01.Orario AS OrarioA,
     Tb_02.Orario AS OrarioB,
     Tb_01.IDDettaglioOrariLinee AS IDDettaglioOrarioA,
     Tb_02.IDDettaglioOrariLinee AS IDDettaglioOrarioB,
     Tb_01.IDOrario,
     ROW_NUMBER() OVER (PARTITION BY Tb_01.Orario, Tb_02.Orario ORDER BY Tb_01.IDOrario DESC) AS Row
    FROM
    (
     SELECT Orario, IDDettaglioOrariLinee, IDOrario
     FROM DettaglioOrariLineePeriodi     --NEW CTE
     WHERE IDRelLineeStazionamenti = @IDRelA
    ) AS Tb_01
    INNER JOIN
    (
     SELECT Orario, IDDettaglioOrariLinee, IDOrario 
     FROM DettaglioOrariLineePeriodi     --NEW CTE
     WHERE IDRelLineeStazionamenti = @IDRelB
    ) AS Tb_02
    ON Tb_01.IDOrario = Tb_02.IDOrario
    INNER JOIN --dbo.relgiornisettimanaorarilinee ON Tb_01.IDOrario = dbo.relgiornisettimanaorarilinee.IDOrario
    (
     SELECT IDOrario 
     FROM relgiornisettimanaorarilinee
     WHERE @IDGiorno = 0 OR IDGiorno = @IDGiorno
    ) Tb_Giorni
    ON Tb_01.IDOrario = Tb_Giorni.IDOrario
    WHERE
    (
    @Orario = '' OR DATEDIFF(minute, CAST(@ORARIO AS DATETIME), CAST(Tb_01.Orario AS DATETIME)) >=0
    ) AND (
    DATEDIFF(minute, CAST(Tb_01.Orario AS DATETIME), CAST(Tb_02.Orario AS DATETIME)) >=0
    ) AND (
    @IDOrari = '' OR Tb_01.IDOrario NOT IN (SELECT CAST(s AS INT) AS IDOrario FROM dbo.Split(',', @IDOrari) AS Split_1)
    )
/*
AND
(
@Data = 0
OR
(
@Data >= CAST(CAST(DATEPART(DAY, PeriodoDal) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoDal)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
AND 
@Data <= CAST(CAST(DATEPART(DAY, PeriodoAl) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoAl)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
)
)
AND
(@IDGiorno = 0 OR IDGiorno = @IDGiorno)
*/
) As Tb_New
WHERE ROW = 1

OPTION (MAXRECURSION 0);

Another thing you could try would be replacing the line

@IDOrari = '' OR Tb_01.IDOrario NOT IN (SELECT CAST(s AS INT) AS IDOrario FROM dbo.Split(',', @IDOrari) AS Split_1)

With

@IDOrari = '' OR (@IDOrari NOT LIKE Tb_01.IDOrario + ',%' AND @IDOrari NOT LIKE '%,' + Tb_01.IDOrario + ',%' AND @IDOrari NOT LIKE '%,' + Tb_01.IDOrario)

3) If you can't find anything that helps, or you have a bunch of tablescans, the next step would be to see if you can modify the indexes for these tables. What I'd recommend is loading SQL profiler (You may need to install it from your sql disk) and creating a new trace. As soon as the trace has started, execute the operation that is taking too long, and then stop the trace. Save the trace to a file, and then launch the database engine tuning advisor. Load the file, and select the database to tune, and press "start trace."

Hopefully this will give you a list of recommendations. I'd focus on changes to the DettaglioOrariLinee table, and any other table that took up a large part of the execution plan with a scan operation.

I'd strongly advise not implementing the recommended changes straight from this tool, instead use these recommndations as a guide.

4) If none of the above give you the needed performance improvement, You could look into denormalizing the table DettaglioOrariLinee so that each row has the data for the A and B sides. This is probably not a good idea.