March 1, 2008 at 5:19 am
I'm pretty new with SQL-Server and SPs.
Because my SP does not finish and I wait and wait, I tried to debug and think there is something wrong with my cursor. I never worked with Cursors before.
Do you see anything wrong??
- the replAvg is for Merge-Replication only where I use the Avg conflict solver
Thanks
Chris
DECLARE @AuswertungGUID UNIQUEIDENTIFIER
DECLARE @OldNameFull VARCHAR(255)
DECLARE @NewNameFull VARCHAR(255)
DECLARE @OldName VARCHAR(255)
DECLARE @NewName VARCHAR(255)
DECLARE @idMMMFormel UNIQUEIDENTIFIER
DECLARE @idMMMFormelKey UNIQUEIDENTIFIER
--These three are the parameters for the future SP
SET @OldName ='TEST11'
SET @NewName = 'TEST12'
SET @idMMMFormel= '291EF17F-D9E6-DC11-A722-005056C00008'
SET @idMMMFormelKey = ( SELECT [fiAuswertungFeldMMM]
FROM [tdta_AuswertungFeldMMMFormel]
WHERE [idafMMMFormel] = @idMMMFormel
)
--Neuen Spaltenkopfnamen schreiben
UPDATE tdta_AuswertungFeldMMMFormel
SET afMMMFormelText = @NewName, replAvg = CAST(RAND()*1000000 AS INT)
WHERE idafMMMFormel = @idMMMFormel;
DECLARE AuswertungsFeld CURSOR LOCAL STATIC
FOR
SELECT idAuswertungFeld
FROM tdta_AuswertungFeld
WHERE fiAuswertungFeldMMM
IN (SELECT t1.fiAuswertungFeldMMM
FROM tdta_AuswertungFeldMMMFormel t1, tkey_AuswertungFeldMMM t2
WHERE t1.fiAuswertungFeldMMM = t2.idafMMM
AND t1.afMMMFormelText = @NewName
AND t2.idafMMM = @idMMMFormelKey);
OPEN AuswertungsFeld
FETCH NEXT FROM AuswertungsFeld INTO @AuswertungGUID
WHILE @@FETCH_STATUS = 0
SET @OldNameFull = CAST(@AuswertungGUID AS VARCHAR(50)) + '|' + @OldName
SET @NewNameFull = CAST(@AuswertungGUID AS VARCHAR(50)) + '|' + @NewName
PRINT @OldNameFull
PRINT @NewNameFull
--Update in Weichen
UPDATE tdta_Weiche
SET WeicheUpdateVon = @NewName , replAvg = CAST(RAND()*1000000 AS INT)
WHERE WeicheUpdateVon = @OldName AND
fiWeiche IN (SELECT idWeiche
FROM tkey_Weiche
WHERE fiAuswertungFeld = @AuswertungGUID)
FETCH NEXT FROM AuswertungsFeld INTO @AuswertungGUID
March 1, 2008 at 7:24 am
well first of if writing a cursor doit like this.
declare @databasename sysname
declare myCursor cursor for
select name from sys.databases
open myCursor
fetch next from myCursor into @databasename
--let's start looping
while @@fetch_status = 0
begin
--in cursor buis logic
print @databasename
--next record
fetch next from myCursor into @databasename
end
--@@fetch_status is not 0
close myCursor
--Close the cursor
deallocate myCursor
--Remove the cursor from Memory
At first glans it looks like you are missing the begin and end part from the while loop
kgunnarsson
Mcitp Database Developer.
March 1, 2008 at 8:39 am
Are you sure you even need a cursor to accomplish your task? Post your Table DDL, some sample data, and a description of what you want to accomplish. We should be able to come up with a better solution than using a cursor.
March 1, 2008 at 11:26 am
that's correct.. server side cursors are not something you want to do . I have been scaling them out.
like adam said post the schema and what is needed. im positive that we can find some brilliant way to solve this
kgunnarsson
Mcitp Database Developer.
March 1, 2008 at 1:25 pm
very interesting, your replies. I read that one have to avoid cursors wherever possible because of the performance.
a) But I think that with an expected affected recordcount of about 50 to 100 records, that should be ok, right?
b) concerning the DDL. Does that make sense? As you can see in the script, I do replace text within the field.
c) anyhow, I do find the problem in the sp - not in the essence I posted. I set the BEGIN ... END wrapping false.
I'm willing to learn. Do you mean I should post the create table statements and relationships to work on another way to solve the problem?
This is how it works now.
btw: I'll want to do the job within a transaction. Is the --BEGIN TRAN --COMMIT TRAN at the right position??
Alter PROCEDURE [dbo].[spUpdateSpaltenauswertung]
@OldName VARCHAR(255),
@NewName VARCHAR(255),
@idMMMFormel VARCHAR(255)
AS
SET NOCOUNT ON
DECLARE @AuswertungGUID UNIQUEIDENTIFIER
DECLARE @OldNameFull VARCHAR(255)
DECLARE @NewNameFull VARCHAR(255)
DECLARE @idMMMFormelKey UNIQUEIDENTIFIER
SET @idMMMFormelKey = ( SELECT [fiAuswertungFeldMMM]
FROM [tdta_AuswertungFeldMMMFormel]
WHERE [idafMMMFormel] = @idMMMFormel
)
--BEGIN TRAN
--Neuen Spaltenkopfnamen schreiben
UPDATE tdta_AuswertungFeldMMMFormel
SET afMMMFormelText = @NewName, replAvg = CAST(RAND()*1000000 AS INT)
WHERE idafMMMFormel = @idMMMFormel;
DECLARE AuswertungsFeld CURSOR LOCAL STATIC
FOR
SELECT idAuswertungFeld
FROM tdta_AuswertungFeld
WHERE fiAuswertungFeldMMM
IN (SELECT t1.fiAuswertungFeldMMM
FROM tdta_AuswertungFeldMMMFormel t1, tkey_AuswertungFeldMMM t2
WHERE t1.fiAuswertungFeldMMM = t2.idafMMM
AND t1.afMMMFormelText = @NewName
AND t2.idafMMM = @idMMMFormelKey);
OPEN AuswertungsFeld
FETCH NEXT FROM AuswertungsFeld INTO @AuswertungGUID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @OldNameFull = CAST(@AuswertungGUID AS VARCHAR(50)) + '|' + @OldName
SET @NewNameFull = CAST(@AuswertungGUID AS VARCHAR(50)) + '|' + @NewName
--Update in Weichen
UPDATE tdta_Weiche
SET WeicheUpdateVon = @NewName , replAvg = CAST(RAND()*1000000 AS INT)
WHERE WeicheUpdateVon = @OldName AND
fiWeiche IN (SELECT idWeiche
FROM tkey_Weiche
WHERE fiAuswertungFeld = @AuswertungGUID)
--WeichenMastern
UPDATE tdta_WeicheMaster
SET wmUpdateVon = @NewName, replAvg = CAST(RAND() * 1000000 AS INT)
WHERE wmUpdateVon = @OldName
AND idWeicheMasterFormel IN (
SELECT fiWeicheDtaMaster
FROM tdta_Weiche
WHERE fiWeiche IN ( SELECT idWeiche
FROM tkey_Weiche
WHERE fiAuswertungFeld = @AuswertungGUID ) )
--Berechnungswerten
UPDATE trel_WerteCalc
SET Typ =@NewName, replavg = CAST(RAND()*1000000 AS INT)
WHERE Typ= @OldName AND fiAuswertungFeld = @AuswertungGUID
--Update in Tabellen und Dokumenten und Präsentationen
UPDATE trel_wdData
SET TextInhalt = REPLACE(TextInhalt, @OldNameFull, @NewNameFull), replAvg = CAST(RAND()*1000000 AS INT)
WHERE CHARINDEX(@OldNameFull, TextInhalt) > 0
UPDATE tdta_wdTabZelle
SET ZelleInhalt = REPLACE(ZelleInhalt, @OldNameFull, @NewNameFull), replAvg = CAST(RAND()*1000000 AS INT)
WHERE CHARINDEX(@OldNameFull, ZelleInhalt) > 0
UPDATE tkey_ppFolie
SET fmText = REPLACE(fmText, @OldNameFull, @NewNameFull), replAvg = CAST(RAND()*1000000 AS INT)
WHERE CHARINDEX(@OldNameFull, fmText) > 0
UPDATE trel_ppFolie
SET fvText = REPLACE(fvText, @OldNameFull, @NewNameFull), replAvg = CAST(RAND()*1000000 AS INT)
WHERE CHARINDEX(@OldNameFull, fvText) > 0
UPDATE trel_ppFolie
SET fmText = REPLACE(fmText, @OldNameFull, @NewNameFull), replAvg = CAST(RAND()*1000000 AS INT)
WHERE CHARINDEX(@OldNameFull, fmText) > 0
FETCH NEXT FROM AuswertungsFeld INTO @AuswertungGUID
END
--COMMIT TRAN
March 1, 2008 at 6:17 pm
Even for an expected 50-100 records cursors are very inefficient when compared to set-based code. It is not just long-running queries that need to be fixed it is also the shorter, inefficient ones that run many times. My personal habit is to always try to come up with the most efficient way of doing things the first time.
Yes, you should post your table structures (with the create statement if you like), some dummy data, and your desired results. Then someone here will likely post an efficient set-based method to solve your problem.
Check out this article, http://qa.sqlservercentral.com/articles/Best+Practices/61537/
For tips on how to best post a problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply