Help optimizing a query

[DirtyB]

We have an asp page that displays different tables for different users. The table structures and data are stored in our SQL database. The tables are stored into our sql database. The table is as follows: TemplateID, Cols, Rows, CellData. So if we have a templateID of 97 then that may be a table with three rows and two columns, and the data for those cells is stored in this table as well. The TemplateID however is not unique.

here's an example of the table:


templateID COLS ROWS DATA
3 1 1 celldata
3 2 1 celldata
3 2 2 celldata
4 1 1
4 2 1
4 3 1
4 4 1

So, table template three would have two cols and two rows, and the cell data. Template 4 would have 4 colms and one row and the cell data.
So we haven't yet determined a key(which would likely be a composite of the first three fields)

We pass in a HUGE string that is delimited by ASCII characters, and it contains the rows and colms. We want to run an update on the celldata depending on what the new data that's passed in is. Here is our query...It takes FOREVER and pounds our system. Can anyone think of a better way to arrange it? (I don't really expect anyone to re-write it, just some hints would be very helpful)


CREATE PROCEDURE sp_ActivityUpdateDatabaseStreamline



(
@template_id int,
@rows int,
@columns int,
@celldata_string varchar(8000)
)
AS

SET NOCOUNT OFF
DECLARE @y int
DECLARE @x int
DECLARE @temp varchar(50)

Set @y = 1
Set @x = 1

WHILE CHARINDEX ('¿',@celldata_string) > 0

BEGIN
WHILE @x < (@columns + 1)
BEGIN
SELECT @temp = left(@celldata_string,CHARINDEX ('¿',@celldata_string)-1)
SELECT @celldata_string = right(rtrim(@celldata_string),rtrim(len(@celldata_string)) - CHARINDEX ('¿',@celldata_string))

UPDATE ActivityTemplateCellData
SET CellData = @temp
WHERE
TemplateID = @template_id
AND
RowNbr = @y
AND
ColumnNbr = @x

SET @x = @x + 1
END
SET @y = @y + 1
SET @x = 1
END

 

[jasmak]

I think tlbroadbent would probably know best as to what the correct way to do this one would be, but I would re-write it as:

SET NOCOUNT ON
DECLARE @y int
DECLARE @x int
DECLARE @temp varchar(50)
DECLARE @strSQL varchar(8000)

Set @y = 1
Set @x = 1

SET @strSQL = 'SELECT * INTO ##UPDATE '
SET @strSQL = @strSQL + ' FROM ('
WHILE CHARINDEX ('¿',@celldata_string) > 0

BEGIN
WHILE @x < (@columns + 1)
BEGIN
SELECT @temp = left(@celldata_string,CHARINDEX ('¿',@celldata_string)-1)
SELECT @celldata_string = right(rtrim(@celldata_string),rtrim(len(@celldata_string)) - CHARINDEX ('¿',@celldata_string))
SET @strSQL = @strSQL + ' SELECT '+CAST(@template_id AS char(5))+' as TemplateID, '+CAST(@y AS char(2))+' as RowNbr, '+CAST(@x AS char(2))+' as ColumnNbr, '+CHAR(39)+@temp+CHAR(39)+' as CellData '
SET @x = @x + 1
IF @x<(@columns + 1)
BEGIN
SET @strSQL = @strSQL + ' UNION '
END

END
SET @y = @y + 1
SET @x = 1

IF LEN(@strSQL)>7000
BEGIN
set @STRsql = @STRsql + ') a '
--PRINT LEN(@strSQL)
--PRINT @strSQL
EXEC(@strSQL)
UPDATE ActivityTemplateCellData SET
CellData = U.CellData
FROM ##UPDATE as U
Inner Join ActivityTemplateCellData as A
ON U.TemplateID = A.TemplateID AND
U.RowNbr = A.RowNbr AND
U.ColumnNbr = A.ColumnNbr
DROP TABLE ##UPDATE
SET @strSQL = 'SELECT * INTO ##UPDATE '
SET @strSQL = @strSQL + ' FROM ('
END
ELSE IF @y<=@rows
BEGIN
SET @strSQL = @strSQL + ' UNION '
END
END

set @STRsql = @STRsql + ') a '
--PRINT LEN(@strSQL)
--PRINT @strSQL
EXEC(@strSQL)
UPDATE ActivityTemplateCellData SET
CellData = U.CellData
FROM ##UPDATE as U
Inner Join ActivityTemplateCellData as A
ON U.TemplateID = A.TemplateID AND
U.RowNbr = A.RowNbr AND
U.ColumnNbr = A.ColumnNbr
DROP TABLE ##UPDATE

That way you get rid of all of those updates that you had in the loop prior to this. We do this by using dynamic SQL. Create the string that you want dynamically and then execute it using the EXEC command. We need to be careful though because the maximum length a variable can be in SQL is 8000 characters. That is why there is an IF statement to check the string each succession of loops to make sure that it does not overflow. When it exceeds 7000 characters, it will execute the statement, join the temp table to the perm table, and do the update in one pass. Of course you may have to tweak the 7000 number a bit. It depends on how many characters of data per row your user's can send in. Hope this helps, ask if you have any questions.

jasmak

 

[DirtyB]

Thanks a lot. I wasn't expecting a re-write, but that definitely works. What's the ## before the UPDATE anyway?

 

[jasmak]

## Creates a global temp table. We need a global temp table in this example because if it is not declared global, it will be in scope only while the EXEC statement runs. This means we won't be able to reference it later to do our update. Hope that helps.