December 13, 2002 at 8:01 am
I have the following SQL which retrieves a set of translated text through a generic translation stored procedure, but I cannot see a way of translating multiple rows without the use of the cursor.
Does anyone know a way ?
DECLARE @Description nvarchar(50)
DECLARE @PopupText nvarchar(300)
DECLARE @tablename nvarchar(50)
DECLARE @keyname nvarchar(50)
DECLARE @languageid int
DECLARE @keyvalue int
DECLARE @usr nvarchar(50)
SET @usr='brownjohn'
CREATE TABLE #MyApps (MyDesc nvarchar(50) , MyPop nvarchar(300))
DECLARE app_cursor CURSOR
FOR
SELECT ApplicationID,LanguageID FROM EDB.dbo.tblSecUserAccess WHERE
EmployeeID in (SELECT EmployeeID FROM EDB.dbo.tblEmployee WHERE NTLogon=@usr)
OPEN app_cursor
FETCH NEXT FROM app_cursor
INTO @keyvalue, @languageid
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC usp_translatetext @tablename = 'tblApplication',
@keyname = 'ApplicationID',
@languageID = @languageID,
@entityID = @keyvalue,
@Description = @Description OUTPUT,
@PopupText = @PopupText OUTPUT
INSERT INTO #MyApps VALUES (@Description, @PopupText)
-- Get the next application.
FETCH NEXT FROM app_cursor
INTO @keyvalue, @languageid
END
CLOSE app_cursor
DEALLOCATE app_cursor
SELECT * FROM #MyApps
GO
December 13, 2002 at 8:12 am
Could you move the logic in the translation stored procedure to a user defined function?
Regards,
Andy Jones
.
December 13, 2002 at 9:00 am
The logic is in an sp because it is performing a parameterised lookup where the tablename, keyfield name,keyfield value are passed in and the text values are passed out. The sp is building a sql string and then executing the string via the sp_executesql statement
December 13, 2002 at 9:59 am
At times cursors are the best way to do things. You might also think about using a table variable that has an identity column and then doing a while loop looking up the data using the identity column value. I've done that several times and had performance improvements. The only way to really know if it is faster for that scenario is to do it both ways and test to see what way is faster.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 15, 2002 at 8:09 pm
Yes! you can avoid cursors most of the time. I found this "cursor-less cursor" script on one of the SQL Sites, either this one, Swynk or another, a few months ago and have used it a few times since. It works great! My only regret is I have lost the original and so I can't remember TO WHOM it should be credited. If the author sees this and would step forward to claim credit, that would be cool, too.
Anyway, here is (more or less) what your same cursor would look like using this technique. I may have dropped a variable or something but this is the general gist of it.
/********** begin T-SQL ******************/
/*Create temporary table using SELECT INTO statement*/
DECLARE @Description nvarchar(50)
DECLARE @PopupText nvarchar(300)
DECLARE @tablename nvarchar(50)
DECLARE @keyname nvarchar(50)
SET @I = 1
SET NOCOUNT ON
SELECT IDENTITY(int, 1,1) AS tmpID,
x.ApplicationID, x.LanguageID
INTO #TempTable
FROM
(SELECT ApplicationID,LanguageID
FROM EDB.dbo.tblSecUserAccess
WHERE EmployeeID in
(SELECT EmployeeID
FROM EDB.dbo.tblEmployee
WHERE NTLogon=@usr)) x
SET @Row_Count = (SELECT COUNT(*) FROM #TempTable)
WHILE @I <= @Row_Count
BEGIN
DECLARE @languageid int
DECLARE @keyvalue int
/*Simulate fetch statement*/
SELECT ApplicationID, LanguageID
FROM #TempTable
WHERE tmpID = @I
SET @languageid = #TempTable.LanguageID
SET keyvalue = #TempTable.ApplicationID
EXEC usp_translatetext @tablename = 'tblApplication',
@keyname = 'ApplicationID',
@languageID = @languageID,
@entityID = @keyvalue,
@Description = @Description OUTPUT,
@PopupText = @PopupText OUTPUT
/*Show the result*/
SELECT @tmpID, ApplicationID, LanguageID
/* set the counter */
SET @I = @I + 1
END
print 'Total rows: ' + Cast(@Row_Count as varchar(8))
DROP TABLE #TempTable
SET NOCOUNT OFF
/************ end T-SQL ******************/
G. Milner
December 16, 2002 at 2:37 am
Which is worse? I suggest that the use of the temporary table, which is being created with a SELECT INTO with all of it's inherent problems with locking, is actually going to cause you more trouble than the cursor...
December 16, 2002 at 10:32 am
While I have done the while loop on the identity column as I stated before, I never use Select Into. I always manually create the table or declare the table ahead of time at the top of the SP. I also try to make sure to specify if the column is nullable or not.
When I converted from 6.5 to 7.0 a couple years ago I found that using Select Into almost always caused a recompile of the sp. In fact I also found that having the temp table created at the top of the sp usually helped lessen the number of recompiles and so I have tried to make sure to put all declarations at the top of sp's ever since.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 17, 2002 at 4:42 am
Unfortunately I cannot convert this to a UDF because the SELECT statement is parameterised and is run using the SP_EXECUTESQL command.
This cannot be run from within a function.
I cannot return a table variable from within the stored procedure.
December 17, 2002 at 7:22 am
If the cursor is not that big you could always use a memory table....
Works just like a real table but is faster than a cursor...
Declare @Table Table
( id int,
text varchar(100)
)
will create a memtable you can loop a mem table by it's id....
The nice thing is the mem table will only exist in the sproc, so when the sproc finishes the table is gone as well.....
December 17, 2002 at 7:27 am
You could well find that having a permanently defined table to hold the working data set will be far faster because you can put indexes on it.
Variable of table type look good, but just look at a query plan with a big stored procedure that uses them. See all those Table Scans (=big performance hit)?
The other problem could be memory impact - a few rows in a table variable is no big deal, but a few thousand long rows is going to eat memory.
December 18, 2002 at 8:57 am
That are you doing inside of the usp_translatetext? You are calling it 1 line at a time, thus requiring a cursor like structure. Could usp_translatetext perhaps be changed to translate multiple lines of text at one time?
December 18, 2002 at 9:01 am
The procedure performs the following
CREATE PROCEDURE usp_translatetext
@tablename sysname,
@keyname sysname,
@languageID int,
@entityID int,
@Description nvarchar(50) OUTPUT,
@PopupText nvarchar(300) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @sqlstr nvarchar(2000)
SET @sqlstr = 'if exists (SELECT [Description], PopupText FROM EDB.dbo.' + @tablename + 'Translation WHERE ' + @keyname + '= @entityID AND LanguageID = @languageID)
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + 'Translation WHERE ' + @keyname + '= @entityID AND LanguageID = @languageID
else
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + ' WHERE ' + @keyname + '= @entityID'
EXEC sp_executesql @sqlstr, N'@languageID int, @entityID int, @Description varchar(50) OUTPUT, @PopupText varchar(300) OUTPUT', @languageID, @entityID, @Description OUTPUT, @PopupText OUTPUT
SET NOCOUNT OFF
END
December 19, 2002 at 6:43 am
Do a SELECT with a WHILE loop.
PlanetJam Media Group
PlanetJam Media Group
December 19, 2002 at 8:28 am
While cursors are best avoided in most situations, there are processes that benefit from a cusor. It is one of many tools that you can use, so don't be compelled to never use a cursor.
In the past, we have used a While loop similar to that outlined by gmilner above. But if the SARG field is not indexed, you could end up with a table scan everytime it looped.
Perhaps the most common use of a curosr is to avoid any concurrency issues when you are doing a large update of a table. With a cursor, locks are kept to a minimum. Better a cursor, than to lock out users.
Always focus on the business problem first, technology second.
Andrew
What's the business problem you're trying to solve?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply