Query optimization

Question

There is a query with many left joins. the query runs fast if some of the left joins are removed but they are necessary so cannot be removed.
Is there a way to optimize such query?

Answer 1

It is unclear what your question is. You put Query Optimization in the title, but all the posts appears to be about the query text being truncated.

In any case, I was not able to reproduce the problem with the query being truncated. There are some traps you can walk into when building dynamic SQL when you can run into this, but your code seems OK. The situation where this can happen is when you do something like:

   SELECT @sql = N'some long string > 2000 and < 4000 chars here'  + @something +  
           N'some other ong string > 2000 chars and < 4000 chars  

Because of the type rules in SQL Server, this results in a string that
is truncated, even if @sql is nvarchar(MAX). To avoid this issue, you need to inject the type nvarchar(MAX), and you are a good boy and already doing this in your script:

   DECLARE [@](/users/na/?userId=070292c7-7ffe-0006-0000-000000000000) NVARCHAR(max)=N''  
            SET [@](/users/na/?userId=070292c7-7ffe-0006-0000-000000000000) = [@](/users/na/?userId=070292c7-7ffe-0006-0000-000000000000) + N'SELECT *  

Note that if you would use the += operator, you would still be in the risk for the issue that I mentioned.

As for why the query is slow, I have a theory. I look at this part:

   LEFT JOIN dbo.REFUND_Txn_DATA strtd(NOLOCK) ON strtd.Txn_Id = stt.Txn_ID  
                LEFT JOIN dbo.THIRDPARTY_Txn_DATA stttd(NOLOCK) ON stttd.TxnId = stt.Txn_ID  
                LEFT JOIN dbo.Symbol_PAYMENT stwp (NOLOCK)ON stwp.Txn_Id = stt.Txn_ID  
                LEFT JOIN dbo.IST_PAYMENT step(NOLOCK)   ON step.TxnId = stt.Txn_ID  

And there are quite a few more left join from this derived table stt to other tables. This only makes sense if all but at one of these left joins can produce 0 or 1 rows. But say that for the four joins in the sample above, the first table produces 5 rows for a specific row from stt, the second 3 rows, the third 10 rows and the fourth table produces 6 rows, that single row from stt produces 5*3*10*6 = 900 rows. That is, you get every combination of these rows.

And say now that stt produces 500 rows, and 900 is an average value you have 45000 rows. Now assume that the other tables you left join to
produces even more rows, this may explode to hundreds of millions of rows. Such a result set will always take a long time to produce.

The way to fix this is to give a second thought on what you actually want to achieve. My analysis is that this query is producing a big pile of garbage. Of course, I could be wrong, since I know nothing about your tables. But this is certainly a pattern I've seen people try more than once.

Answer 2

Make sure the variable is of type nvarchar(MAX), including expressions using to assign the variable value, to avoid truncation. Include the code in your question if you need help troubleshooting.