Optimizing TSQL Stored Procedure Code, need a better alternative for performance.
-
Hello, I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated. Regards.
DECLARE @Iterator INT
SET @Iterator = 0WHILE (@Iterator < @totalIds)
BEGINIF(@Iterator = 0) BEGIN SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds END ELSE BEGIN SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50))) FROM #deletedIds WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds); END SET @Iterator = @Iterator + 1END
-
Hello, I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated. Regards.
DECLARE @Iterator INT
SET @Iterator = 0WHILE (@Iterator < @totalIds)
BEGINIF(@Iterator = 0) BEGIN SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds END ELSE BEGIN SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50))) FROM #deletedIds WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds); END SET @Iterator = @Iterator + 1END
That looks a real bottleneck: for N entries in the result list, you perform 2N-1 SELECT statements, and N-1 string concatenations, which must be as bad in SQL as they are in any OO language (remember the purpose of StringBuilder versus string in C#). Are you sure you really need a comma separated list at the SQL level? If you were to perform a simple SELECT statement, you would get a result table that contains the data you want, one result element per row. At practically no cost. Too often IMO people tend to find an SQL-only solution to a problem. This may well be one more example of that. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Hello, I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated. Regards.
DECLARE @Iterator INT
SET @Iterator = 0WHILE (@Iterator < @totalIds)
BEGINIF(@Iterator = 0) BEGIN SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds END ELSE BEGIN SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50))) FROM #deletedIds WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds); END SET @Iterator = @Iterator + 1END
I agree with Luc, do it on the client. But there is also this: Stupid CTE tricks -- string concatenation[^] And this: http://charleskong.com/blog/2009/04/use-coalesce-function-to-concatenate-field-values/[^] Or write a CLR aggregate function.
-
That looks a real bottleneck: for N entries in the result list, you perform 2N-1 SELECT statements, and N-1 string concatenations, which must be as bad in SQL as they are in any OO language (remember the purpose of StringBuilder versus string in C#). Are you sure you really need a comma separated list at the SQL level? If you were to perform a simple SELECT statement, you would get a result table that contains the data you want, one result element per row. At practically no cost. Too often IMO people tend to find an SQL-only solution to a problem. This may well be one more example of that. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Hi Luc, Thanks for your reply. Yes, thats what I realize, I would never do the same thing in my programming. (I am a C# programmer) But the problem is, I dont know the correct TSQL Syntax / operators that can let me get rid of this loop. My intention to get data from stored procedure in the following structure ------------------------- string product_name, int affectedRows, bool hasError, int[] someIds ----------------------- Now, everything was fine until I reached the need to get int[] someIds. I was lost, how I could do that. If I just needed to return SomeIds (: int[]) then, yes, I could simply return the result set, but as I needed to get the other string, bool, int etc type data so I created a temporary table in my procedure to store the int values (someIDs : int[]) then, I used the specified procedure to read back the int ids from the temporary table and serialize as comma separated string to return. Would you please show me any technique to get such data structure from stored procedure ?
-
Hi Luc, Thanks for your reply. Yes, thats what I realize, I would never do the same thing in my programming. (I am a C# programmer) But the problem is, I dont know the correct TSQL Syntax / operators that can let me get rid of this loop. My intention to get data from stored procedure in the following structure ------------------------- string product_name, int affectedRows, bool hasError, int[] someIds ----------------------- Now, everything was fine until I reached the need to get int[] someIds. I was lost, how I could do that. If I just needed to return SomeIds (: int[]) then, yes, I could simply return the result set, but as I needed to get the other string, bool, int etc type data so I created a temporary table in my procedure to store the int values (someIDs : int[]) then, I used the specified procedure to read back the int ids from the temporary table and serialize as comma separated string to return. Would you please show me any technique to get such data structure from stored procedure ?
I wouldn't know how to do that, and that is due to the fact that I would never want that to begin with. Get a table result, and have the client code iterate over it. That is simple and inexpensive. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Hello, I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated. Regards.
DECLARE @Iterator INT
SET @Iterator = 0WHILE (@Iterator < @totalIds)
BEGINIF(@Iterator = 0) BEGIN SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds END ELSE BEGIN SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50))) FROM #deletedIds WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds); END SET @Iterator = @Iterator + 1END
The difference between a conventional programming language like C# and a data oriented language like T-SQL is that T-SQL is "set" oriented. In other words, it has been designed to work with "sets" of data rather than a single piece of information. Your code seems to work with one piece of information at a time which defeats the purpose of using T-SQL. Also I noted that you have temporary tables. Using Table Variables[^] may improve performance. Note that Table Variables have certain restrictions.
-
I agree with Luc, do it on the client. But there is also this: Stupid CTE tricks -- string concatenation[^] And this: http://charleskong.com/blog/2009/04/use-coalesce-function-to-concatenate-field-values/[^] Or write a CLR aggregate function.
Hi Piebald, Thank you sooooooooooooo much for the kind help. Yes, the COALESCE function is what solved my entire problem. In fact, the link you provided discussed the exactly same problem what I had to solve, so that page just did my task completely. I highly appreciate your help.
-
The difference between a conventional programming language like C# and a data oriented language like T-SQL is that T-SQL is "set" oriented. In other words, it has been designed to work with "sets" of data rather than a single piece of information. Your code seems to work with one piece of information at a time which defeats the purpose of using T-SQL. Also I noted that you have temporary tables. Using Table Variables[^] may improve performance. Note that Table Variables have certain restrictions.
Hi Shameel, Thank you very much for your suggestions. I got the problem solved by COALESCE function that was suggested by the previous reply. But The Table Variable over temporary variable is a nice thing that learned from your link. Regards.
-
Hello, I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated. Regards.
DECLARE @Iterator INT
SET @Iterator = 0WHILE (@Iterator < @totalIds)
BEGINIF(@Iterator = 0) BEGIN SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds END ELSE BEGIN SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50))) FROM #deletedIds WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds); END SET @Iterator = @Iterator + 1END
Oops, sorry, this is my first post and I couldn't see that the question was already answered. Please ignore. I am not sure if I understand the question but here are the results of my testing... -- Original query with bits added in so I get a result set DECLARE @totalIds INT; SET @totalIds = 2 DECLARE @DeletedIDArr VARCHAR(50) DROP table #deletedIds SELECT * INTO #deletedIds FROM (SELECT 1 AS Id UNION SELECT 2) a DECLARE @Iterator INT SET @Iterator = 0 WHILE (@Iterator < @totalIds) BEGIN IF(@Iterator = 0) BEGIN SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds END ELSE BEGIN SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50))) FROM #deletedIds WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds); END SET @Iterator = @Iterator + 1 END SELECT @DeletedIDArr -- My version SET @DeletedIDArr='' SELECT @DeletedIDArr = @DeletedIDArr+CAST(Id AS varchar(50))+',' FROM #deletedIds SET @DeletedIDArr = LEFT(@DeletedIDArr,LEN(@DeletedIDArr)-1) SELECT @DeletedIDArr