my query is fast in SSMS but goes over 1 hour in my app

Question

I have an issue with a slow query in my app, but it is so fast in SSMS . After I saw some answer, I noticed that the execution plan seems to be different.

I noticed that when I use SSMS something is called [Parallelism (Repartition Streams)] is used but I don't see it in the execution plan coming from my C# app ( I use Activity Monitor in SSMS to see that). Also, When I use my app I see that the query is listed in the "Active Expensive Queries' list, but that is not the case when I run it from the SSMS.

I will add my query but try to hide some text just for my company privacy:

SELECT  [REC_INVENT_LIST_ID]
      , ril.[NOTE_TYPE_ID]
      , ril.[TRANS_ID] 
      , [QUANTITIES]
      , [AMOUNT]
      , nt.[CU_TYPE]
      , nt.[CASH_TYPE]
      , nt.[NOTE_VALUE]
      , nt.[UNIT_ID]
      , t.RECYCLER_ID
FROM [D].[dbo].[RecyclerInventoryList] ril
JOIN [S].[dbo].[NoteType] nt ON nt.NOTE_TYPE_ID = ril.NOTE_TYPE_ID
JOIN [D].[dbo].[Transaction] t ON ril.TRANS_ID = t.TRANS_ID
WHERE QUANTITIES <> 0
AND ril.TRANS_ID IN (
        SELECT sub.TRANS_ID FROM (SELECT *, MAX(CREATE_DATE) 
        OVER(PARTITION BY t.RECYCLER_ID) AS _max
        FROM [D].[dbo].[Transaction] AS t
        WHERE TRANS_ID <= @lastTransId) AS sub
        WHERE CREATE_DATE = _max
                 )
ORDER BY t.RECYCLER_ID, CU_TYPE 

What I did is:

1- I tried to remove the part where it says WHERE TRANS_ID <= @lastTransId to make sure it's not a parameter conversion issue, but that didn't help.

2- I removed the part where it says WHERE CREATE_DATE = _max I got result back in the app FAST!, but it's not the result I want. This where is really important for me.

3- I used the SQL SERVER PROFILER to see what is the exact query that tries to run after my application timeouts and crashes (1 hour command timeout) I see it like this

exec sp_executesql N'SELECT [REC_INVENT_LIST_ID], ril.[NOTE_TYPE_ID], ril.[TRANS_ID], [QUANTITIES], [AMOUNT], nt.[CU_TYPE], nt.[CASH_TYPE], nt.[NOTE_VALUE], nt.[UNIT_ID], t.[RECYCLER_ID] FROM[RCMDYNAMIC].[dbo].[RecyclerInventoryList] ril JOIN[RCMSTATIC].[dbo].[NoteType] nt ON nt.NOTE_TYPE_ID = ril.NOTE_TYPE_ID JOIN[RCMDYNAMIC].[dbo].[Transaction] t ON ril.TRANS_ID = t.TRANS_ID  WHERE QUANTITIES<> 0 AND ril.TRANS_ID IN (SELECT sub.TRANS_ID FROM (SELECT TRANS_ID, CREATE_DATE , MAX(CREATE_DATE) OVER(PARTITION BY t.RECYCLER_ID) AS _max FROM[RCMDYNAMIC].[dbo].[Transaction] AS t WHERE TRANS_ID <= @lastTransId ) AS sub WHERE CREATE_DATE = _max) ORDER BY t.RECYCLER_ID, CU_TYPE',N'@lastTransId int',@lastTransId=XXXXX

When I run this on SSMS, it's still very fast(milliseconds) and the execution plan seems to be the same as when I run the query directly from SSMS.

Any ideas?

EDIT:

My C# code :

public Dictionary<int, List<RCMBalanceTransactionModel>> getBalanceTransactions(int transId) {

        SqlCommand command;
        SqlDataReader reader;
        command = new SqlCommand(getBalanceTransactionInfoQuery(), con);
        command.CommandTimeout = 3600;
        command.Parameters.AddWithValue("@lastTransId", transId);
        reader = command.ExecuteReader();


  //the rest is omitted the application is stuck here at ExecutedReader();
}

Answer 1

Based on your comment from the other answer, if you are just trying to get the latest TRANS_ID per recycler_id, you could probably do it with the query below. This assumes your IDs are generated in order. You could do basically the same thing with CREATE_DATE too if you can be sure no two transactions from the same recycler have the same CREATE_DATE. I'm not sure of your goal of @lastTransId so you might need to add that to the where clause.

SELECT  
    [REC_INVENT_LIST_ID]
      , ril.[NOTE_TYPE_ID]
      , ril.[TRANS_ID] 
      , [QUANTITIES]
      , [AMOUNT]
      , nt.[CU_TYPE]
      , nt.[CASH_TYPE]
      , nt.[NOTE_VALUE]
      , nt.[UNIT_ID]
      , t.RECYCLER_ID
FROM 
    [D].[dbo].[RecyclerInventoryList] ril
    JOIN [S].[dbo].[NoteType] nt 
        ON nt.NOTE_TYPE_ID = ril.NOTE_TYPE_ID
    JOIN [D].[dbo].[Transaction] t 
        ON ril.TRANS_ID = t.TRANS_ID
    LEFT OUTER JOIN [D].[dbo].[Transaction] t2
        ON t.RECYCLER_ID = t2.RECYCLER_ID AND t.TRANS_ID < t2.TRANS_ID
WHERE
    t2.TRANS_ID IS NULL AND /* This is null only for t.TRANS_ID that have no records greater than itself, ie the latest one */
    QUANTITIES <> 0
ORDER BY 
    t.RECYCLER_ID, CU_TYPE 

Answer 2

I tried to rewrite the query, but without data I am not sure if I got it right

SELECT  [REC_INVENT_LIST_ID]
      , ril.[NOTE_TYPE_ID]
      , ril.[TRANS_ID] 
      , [QUANTITIES]
      , [AMOUNT]
      , nt.[CU_TYPE]
      , nt.[CASH_TYPE]
      , nt.[NOTE_VALUE]
      , nt.[UNIT_ID]
      , t.RECYCLER_ID
FROM [D].[dbo].[RecyclerInventoryList] ril
JOIN [S].[dbo].[NoteType] nt ON nt.NOTE_TYPE_ID = ril.NOTE_TYPE_ID
JOIN [D].[dbo].[Transaction] t ON ril.TRANS_ID = t.TRANS_ID
WHERE QUANTITIES <> 0
AND t.TRANS_ID <= @lastTransId
AND NOT EXISTS(SELECT 1 FROM [D].[dbo].[Transaction] AS t2 
    WHERE t2.RECYCLER_ID = t.RECYCLER_ID 
      AND t2.CREATE_DATE > t.CREATE_DATE 
          AND t2.TRANS_ID <= @lastTransId)

ORDER BY t.RECYCLER_ID, CU_TYPE