tomullus 69522 (7/29/2015)
Hi,I came upon an odd issue today:
I have a query that runs fine and at an acceptable speed (a few seconds). It looks like this:
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
This query is a part of a stored procedure where it is used as a condition of an IF statement. That part of the SP is extremely slow.
Below is a query that ran for an hour until i stopped it:
if (
(
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
)
) > 0
begin
select 1
end
Can anyone suggest an explanation for this behaviour?
I used a local variable to calculate the result of the query before the IF statement and that solved the problem, though i feel like this might me sidestepping the real issue.
It seems you have changed the posted query based on the first quoted response above. Your query has a number of challenges from a performance perspective. First is the inequality in the where predicate. This forces a scan no matter what indexing you have in place. Another issue is that the whole query seems to be flawed conceptually. It is totally out of context here but it looks like you should be using an EXISTS.
Also, it seems that one attempt you tried using NOLOCK. That hint is NOT a performance enhancing query hint. It carries a LOT of baggage that people seem to ignore. Things like missing and/or duplicate rows are one of them.
If you really want some help with this we need a lot more information posted. Tables structures along with index definitions. An actual execution plan. Some sample data. Take a look at this article for more details about how to post performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/