February 9, 2011 at 10:37 am
I have the following bizarre situation and was hoping someone could help me figure it out.
The following statement does not seem to complete (I have to stop it):
WHILE EXISTS
(
SELECT top 1 NULL
FROM dbo.tmp1 TMP (NOLOCK)
INNER JOIN dbo.CTR (NOLOCK) ON TMP.CTRID = CTR.CTRID
LEFT JOIN dbo.USG (NOLOCK) ON CTR.STID = USG.STID AND USG.IsU = 'N'
WHERE USG.USGID IS NULL
)
BEGIN
RETURN
END
On the other hand, the inside condition completes instantaneously:
SELECT top 1 NULL
FROM dbo.tmp1 TMP (NOLOCK)
INNER JOIN dbo.CTR (NOLOCK) ON TMP.CTRID = CTR.CTRID
LEFT JOIN dbo.USG (NOLOCK) ON CTR.STID = USG.STID AND USG.IsU = 'N'
WHERE USG.USGID IS NULL
Why is that? Anyone seen this before?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 9, 2011 at 10:55 am
what does this return:
WHILE EXISTS
(
SELECT top 1 NULL
FROM dbo.tmp1 TMP (NOLOCK)
INNER JOIN dbo.CTR (NOLOCK) ON TMP.CTRID = CTR.CTRID
LEFT JOIN dbo.USG (NOLOCK) ON CTR.STID = USG.STID AND USG.IsU = 'N'
WHERE USG.USGID IS NULL
)
BEGIN
Print 'row exists'
RETURN
Print 'no rows'
END
February 9, 2011 at 10:59 am
AlexSQLForums (2/9/2011)
what does this return:
WHILE EXISTS
(
SELECT top 1 NULL
FROM dbo.tmp1 TMP (NOLOCK)
INNER JOIN dbo.CTR (NOLOCK) ON TMP.CTRID = CTR.CTRID
LEFT JOIN dbo.USG (NOLOCK) ON CTR.STID = USG.STID AND USG.IsU = 'N'
WHERE USG.USGID IS NULL
)
BEGIN
Print 'row exists'
RETURN
Print 'no rows'
END
It actually completes after 34 seconds, but that is more than 30 times slower than the inside-condition query.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 9, 2011 at 11:00 am
I saw this link that describes a very similar issue, this time with the IF EXISTS clause.
Are we looking at some bizarre behavior from the query engine here?
http://www.sqlservercentral.com/Forums/Topic957634-338-1.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 9, 2011 at 3:16 pm
WHILE EXISTS
(
SELECT top 1 NULL
FROM dbo.tmp1 TMP (NOLOCK)
INNER JOIN dbo.CTR (NOLOCK) ON TMP.CTRID = CTR.CTRID
LEFT JOIN dbo.USG (NOLOCK) ON CTR.STID = USG.STID AND USG.IsU = 'N'
WHERE USG.USGID IS NULL
)
BEGIN
RETURN
END
Change the While Exists to If Exists. You are trying to return in the middle of an infinite loop. It might work but it is very unpredictable what might happen.
_______________________________________________________________
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/
March 12, 2014 at 10:15 am
Changing while exists to if exists worked to me,
thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply