SQL Server 2000 SP 4 issue with query
-
This query executes (instantly) in SQL Server 2000 without SP4. With SP 4, it hangs - goes over 5 mins and does nothing. If we make the following change to the query: (replace the variables with actual values)
...
where Links.Matter_ID = 5872
AND Links.Entity_ID = 1
Order By Contacts.ContactID
...
It executes almost instantly in SP4 as well.
Looks like a SP4 issue, any ideas on how we could fix this?
t-sql follows:
Declare @MatterID bigint
Declare @EntityID int
Declare @ReturnDetails int
Declare @RETVAL VarChar(8000)
Declare @EntityDetails VarChar(1000)
Set @MatterID = 5875
Set @EntityID = 1
Set @ReturnDetails = 0
DECLARE Entity_Cursor CURSOR FOR
Select CASE WHEN Contacts.Job_Title IS Null Then '' Else Contacts.Job_Title + ' ' End + CASE WHEN Contacts.First_Name IS Null Then '' Else Contacts.First_Name + ' ' End + CASE WHEN Contacts.Middle_Name IS Null Then '' Else Contacts.Middle_Name + ' ' End
+ CASE WHEN Contacts.Last_Name IS Null Then '' Else Contacts.Last_Name End EntityDetails
From Links
INNER JOIN Contacts
On Links.Contact_ID = Contacts.ContactID
where Links.Matter_ID = @MatterID
AND Links.Entity_ID = @EntityID
Order By Contacts.ContactID
OPEN Entity_Cursor
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails
Select @RETVAL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'test'
/* If @ReturnDetails = 1
Select @RETVAL = @RETVAL + @EntityDetails + '
'
Else
Select @RETVAL = @RETVAL + @EntityDetails + ', '
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails */
END
CLOSE Entity_Cursor
DEALLOCATE Entity_Cursor
-
your compiled plan is "wrong". Try use with recomplie or parameter sniffing to bypass the issue.
Cheers,
* Noel -
You need to rewrite the query to be efficient. You can do that whole thing without a cursor:
Declare
@MatterID bigint
Declare
@EntityID int
Declare
@ReturnDetails int
Declare
@RETVAL VarChar(8000)
Set
@MatterID = 5875
Set
@EntityID = 1
Set
@ReturnDetails = 0
Select
@RETVAL = IsNull(@RETVAL + ',', '') + IsNull(C.Job_Title, '') + IsNull(C.First_Name, '') + IsNull(C.Middle_Name, '') + IsNull(C.Last_Name, '')
INNER
JOIN Contacts As C On L.Contact_ID = C.ContactID
where
L.Matter_ID = @MatterID
AND
L.Entity_ID = @EntityID
Order
By C.ContactID
Select
@RETVAL
I replaced your case statements with IsNull() and aliased your tables to make the query easier to read.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Heh... well done... I'm thinking that if SP4 is so tough on cursors, then I like it a lot
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
ok, the original sql works fine if I change the cursor type to static forward-only
I just changed the line
DECLARE Entity_Cursor CURSOR
To
DECLARE Entity_Cursor static forward-only CURSOR
but now I have a bunch of sps which could all fail because they are like this, Is there a way to tell sql server to have all cursors created as 'static forward-only' by default, I couldn't find a way to do this using sp_configure so any suggestions would be appreciated.
I wish rewriting all the sps was an option but they're part of an old legacy system and I don't want to mess around with it too much.
-
Afraid not...
You can search syscomments to find all cursors and then update 'em all :
SELECT OBJECT_NAME(id) AS ObjName FROM dbo.SysComments WHERE Text like '%CURSOR%' ORDER BY ObjName
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply