Improve SQL Query Performance after adding Unique Transaction clause

Question

I have a stored procedure that has optional parameters (pretty much all of them); if the parameter is not supplied, it needs to pull all data.

I have the following right now.

select * 
from myTableird
where 
    ird.tIS like COALESCE(null, ird.tIS) 
    and ird.InventoryDate = COALESCE(null, ird.InventoryDate)
    and ird.DateClosed = COALESCE(null, ird.DateClosed)

This runs very quickly, less than a second to generate the results.

I need to append code to only pull only the most recent record, for each repID. When I add the following code (last and statement), it just runs forever and times out after a few minutes. what did I do wrong? please help optimize the query, if possible, please explain so that I can learn, going forward. thank you!

select * 
from myTableird
where 
    ird.repID like COALESCE(null, ird.repID) 
    and ird.InventoryDate = COALESCE(null, ird.InventoryDate)
    and ird.DateClosed = COALESCE(null, ird.DateClosed)
    and ird.PeriodID in (SELECT MAX(PeriodID)    
                         FROM myTableird   
                         GROUP BY repID)

Change that to an INNER JOIN and you’ll see a big boost or use a window function like row_number in your query — S3S, Jan 10 '19 at 19:26
You should make an effort to be **consistent** with how you write the SQL keywords - *either* write them in all UPPERCASE (SELECT, FROM), or write them in all lowercase (select, from), or in mixed case (Select, From) - but please **pick one style** and then **stick to it** - don't mix all of them together! — marc_s, Jan 10 '19 at 19:33
why do you have coalesce(null, ...) ? that will always return the second parameter because the first is null. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017 . or am i missing something here? — John Boker, Jan 10 '19 at 19:37
@JohnBoker i have parameter names in my stored procedure, instead of "null" i should have said that. sorry! — Madam Zu Zu, Jan 10 '19 at 19:40

score 0 · Answer 1 · answered Jan 10 '19 at 19:27

UGH. figured it out. all i had to do was post it. haha. anyway, here is what i came up with, it works instant now! please confirm that this is the best solution? Thanks!

select * from myTableird
inner join (ird.PeriodID in (SELECT MAX(PeriodID) as Pid   FROM myTableird   GROUP BY repID)) t2
on irdPeriodID = t2.PID
                                   where 
                                    ird.repID like COALESCE(null, ird.repID) 
                                    and ird.InventoryDate = COALESCE(null, ird.InventoryDate)
                                    and ird.DateClosed = COALESCE(null, ird.DateClosed)

Is `ird.PeriodID in` a left over? It doesn't look like valid syntax to me. — sticky bit, Jan 10 '19 at 21:09

Improve SQL Query Performance after adding Unique Transaction clause

1 Answers1