February 24, 2012 at 7:42 am
So I am sort of looking for an alternative solution to this problem:
I have a stored procedure (xsp_run_sproc) that SELECTs values FROM a table and passes them 1 by 1 as a variable (@value) to a cursor (shoot me), which in return runs another stored procedure (xsp_run_sproc2) that creates a CSV based on the @value.
Sort of like (this is xsp_run_sproc):
select distinct value
into #temp
from tbl_woogidy
declare @value int ;
declare sucky cursor for
select *
from #temp
open sucky
fetch sucky into @value
while @@fetch_status = 0
Begin
exec xsp_run_sproc2 @value
fetch sucky into @value
END
close sucky
deallocate sucky
... And then xsp_run_sproc2 ends up creating a CSV with a bunch of data based on each @value passed.
SO, my question:
Is there a better alternative to the dreaded cursor? I know a while loop could be used, but to me that option sucks too. I am very novice with SSIS, but maybe that would be a good option (details on how would be very welcome)??
Just trying to learn any alternatives that may exist out there (I am using both SQL Server 2005 and SQL Server 2008 R2).
-Stephen
February 24, 2012 at 7:51 am
It seems to me that what you are doing is an itterative, sequential process, and a CURSOR is an appropriate way to solve the problem.
I don't know of a way to call a procedure multiple times in a single statement. But I'll stick around to read answers from others; maybe I'll learn something!
February 24, 2012 at 8:06 am
fahey.jonathan (2/24/2012)
It seems to me that what you are doing is an itterative, sequential process, and a CURSOR is an appropriate way to solve the problem.I don't know of a way to call a procedure multiple times in a single statement. But I'll stick around to read answers from others; maybe I'll learn something!
I may have some "wishful thinking" here, but I am hoping one of the Guru's might pop in and have some wonderful alternative (oh yeah, this is wishful).
-Stephen
February 24, 2012 at 8:08 am
It's a little difficult to say, because we don't know exactly what that second proc is doing...but my somewhat generic advice would be the following:
I would re-write the second proc to accept all the values from the source table, process them (as a unit) and insert the results into some staging table. I am assuming of course that the data/results generated from each iteration is uniquely identifiable by some means...but even if it isn't, you could probably adjust the second proc to include the original source value as identifier.
Once all this is done I would employ a "for each loop" in SSIS to iterate and extract the data for each unique source value, generating the required CSV as output.
That's my 2 cents...not sure if it would work with your process, but it could be worth a try.
February 24, 2012 at 8:21 am
Martin Schoombee (2/24/2012)
It's a little difficult to say, because we don't know exactly what that second proc is doing...but my somewhat generic advice would be the following:I would re-write the second proc to accept all the values from the source table, process them (as a unit) and insert the results into some staging table. I am assuming of course that the data/results generated from each iteration is uniquely identifiable by some means...but even if it isn't, you could probably adjust the second proc to include the original source value as identifier.
Once all this is done I would employ a "for each loop" in SSIS to iterate and extract the data for each unique source value, generating the required CSV as output.
That's my 2 cents...not sure if it would work with your process, but it could be worth a try.
Would the SSIS for-each loop be faster (theoretically) ?
as for the 2nd sproc (xsp_run_sproc2), it accepts the parameter @value, gathers data from various tables based on @value, and then uses BCP to create a CSV file.
For example: I have 10 cities in tbl_cities. Each City has an ID, City_ID, and is set to @value and passed (using the cursor) to xsp_run_sproc2. Then, xsp_run_sproc gathers demographic data from various tables (related by the City_ID) and exports it to a CSV file using BCP.exe. Then the whole process repeats for each City_ID. Note: I must have a seperate CSV file for each City, because they are sent to different clients, and do not need to see each others information.
February 24, 2012 at 8:29 am
stephen99999 (2/24/2012)
Would the SSIS for-each loop be faster (theoretically) ?
as for the 2nd sproc (xsp_run_sproc2), it accepts the parameter @value, gathers data from various tables based on @value, and then uses BCP to create a CSV file.
For example: I have 10 cities in tbl_cities. Each City has an ID, City_ID, and is set to @value and passed (using the cursor) to xsp_run_sproc2. Then, xsp_run_sproc gathers demographic data from various tables (related by the City_ID) and exports it to a CSV file using BCP.exe. Then the whole process repeats for each City_ID. Note: I must have a seperate CSV file for each City, because they are sent to different clients, and do not need to see each others information.
The for each loop won't necessarily be faster if you are just replacing the cursor with it. And I am not suggesting that you do...
What I am suggesting is that you generate the data for all cities (based on your example) at the same time, and then use the for each loop in SSIS to iterate through the results for each city and generate the csv (for that city). Effectively you will be removing the bcp functionality from the proc.
Remember that the SQL language is set-based. From your explanation above, it definitely seems possible to generate the results for all cities at the same time. From that point, the only thing you would need a loop for is the generation of the csv files, which can easily be facilitated.
Does that make sense?
February 24, 2012 at 8:31 am
stephen99999 (2/24/2012)
Martin Schoombee (2/24/2012)
It's a little difficult to say, because we don't know exactly what that second proc is doing...but my somewhat generic advice would be the following:I would re-write the second proc to accept all the values from the source table, process them (as a unit) and insert the results into some staging table. I am assuming of course that the data/results generated from each iteration is uniquely identifiable by some means...but even if it isn't, you could probably adjust the second proc to include the original source value as identifier.
Once all this is done I would employ a "for each loop" in SSIS to iterate and extract the data for each unique source value, generating the required CSV as output.
That's my 2 cents...not sure if it would work with your process, but it could be worth a try.
Would the SSIS for-each loop be faster (theoretically) ?
as for the 2nd sproc (xsp_run_sproc2), it accepts the parameter @value, gathers data from various tables based on @value, and then uses BCP to create a CSV file.
For example: I have 10 cities in tbl_cities. Each City has an ID, City_ID, and is set to @value and passed (using the cursor) to xsp_run_sproc2. Then, xsp_run_sproc gathers demographic data from various tables (related by the City_ID) and exports it to a CSV file using BCP.exe. Then the whole process repeats for each City_ID. Note: I must have a seperate CSV file for each City, because they are sent to different clients, and do not need to see each others information.
I've done the same thing and the limitting factor is that I can't seem to find a way to create individual files or bcp invokations using "set oriented" coding, sending email is another problematic application.
February 24, 2012 at 8:33 am
Martin Schoombee (2/24/2012)
stephen99999 (2/24/2012)
Would the SSIS for-each loop be faster (theoretically) ?
as for the 2nd sproc (xsp_run_sproc2), it accepts the parameter @value, gathers data from various tables based on @value, and then uses BCP to create a CSV file.
For example: I have 10 cities in tbl_cities. Each City has an ID, City_ID, and is set to @value and passed (using the cursor) to xsp_run_sproc2. Then, xsp_run_sproc gathers demographic data from various tables (related by the City_ID) and exports it to a CSV file using BCP.exe. Then the whole process repeats for each City_ID. Note: I must have a seperate CSV file for each City, because they are sent to different clients, and do not need to see each others information.
The for each loop won't necessarily be faster if you are just replacing the cursor with it. And I am not suggesting that you do...
What I am suggesting is that you generate the data for all cities (based on your example) at the same time, and then use the for each loop in SSIS to iterate through the results for each city and generate the csv (for that city). Effectively you will be removing the bcp functionality from the proc.
Remember that the SQL language is set-based. From your explanation above, it definitely seems possible to generate the results for all cities at the same time. From that point, the only thing you would need a loop for is the generation of the csv files, which can easily be facilitated.
Does that make sense?
Most definitely makes sense. At least that way, we would only be generating the City data one time vs. 10 times. I could even still end up using an ugly while loop (SSIS may not be an option atm) and bcp to generate the CSV files, after the city data has been generated. This is definitely a good step in the right direction.
February 24, 2012 at 8:37 am
stephen99999 (2/24/2012)
Most definitely makes sense. At least that way, we would only be generating the City data one time vs. 10 times. I could even still end up using an ugly while loop (SSIS may not be an option atm) and bcp to generate the CSV files, after the city data has been generated. This is definitely a good step in the right direction.
And that's precisely the point I am trying to make. Do all the database stuff in a set-based manner. Then (if you have to), you can use a while loop or any kind of loop to produce the output.
You should see a dramatic improvement in performance if you do that. Cursors are bad on so many levels...
February 24, 2012 at 8:44 am
It seems that all the suggestions that involve generating the city data in a single step would need to store that city data in a table somewhere, whereas the stored procedure (I suspect) is using BCP to export the results of a query, with no table storage needed. That may need to be taken into consideration.
February 24, 2012 at 8:55 am
Is dynamic SQL not an option here?
February 24, 2012 at 8:58 am
Divine Flame (2/24/2012)
Is dynamic SQL not an option here?
Not sure...I guess it depends on exactly what you are trying to do. How are you envisioning the use of dynamic sql here?
February 24, 2012 at 9:14 am
Martin Schoombee (2/24/2012)
Divine Flame (2/24/2012)
Is dynamic SQL not an option here?Not sure...I guess it depends on exactly what you are trying to do. How are you envisioning the use of dynamic sql here?
I am thinking of something like this:
IF OBJECT_ID('tempdb..#MyTestTable24Feb2012') IS NOT NULL
BEGIN
DROP TABLE #MyTestTable24Feb2012
END
CREATE TABLE #MyTestTable24Feb2012
(
ID INT IDENTITY(1,1),
Value VARCHAR(20)
)
GO
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param1')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param2')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param3')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param4')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param5')
GO
DECLARE @SQLCommand VARCHAR(4000)
SET @SQLCommand=''
SELECT @SQLCommand = @SQLCommand+'Execute xsp_run_sproc2 '+ Value+'; ' FROM #MyTestTable24Feb2012
PRINT @SQLCommand
EXEC (@SQLCommand)
I think it might work if he needs to execute procs one by one.
February 24, 2012 at 9:21 am
Divine Flame (2/24/2012)
I am thinking of something like this:
IF OBJECT_ID('tempdb..#MyTestTable24Feb2012') IS NOT NULL
BEGIN
DROP TABLE #MyTestTable24Feb2012
END
CREATE TABLE #MyTestTable24Feb2012
(
ID INT IDENTITY(1,1),
Value VARCHAR(20)
)
GO
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param1')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param2')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param3')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param4')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param5')
GO
DECLARE @SQLCommand VARCHAR(4000)
SET @SQLCommand=''
SELECT @SQLCommand = @SQLCommand+'Execute xsp_run_sproc2 '+ Value+'; ' FROM #MyTestTable24Feb2012
PRINT @SQLCommand
EXEC (@SQLCommand)
I think it might work if he needs to execute procs one by one.
Good point, and definitely a better option than a cursor...I hate cursors with a passion.
Thanks for providing another alternative 🙂
February 24, 2012 at 12:16 pm
Martin Schoombee (2/24/2012)
Divine Flame (2/24/2012)
I am thinking of something like this:
IF OBJECT_ID('tempdb..#MyTestTable24Feb2012') IS NOT NULL
BEGIN
DROP TABLE #MyTestTable24Feb2012
END
CREATE TABLE #MyTestTable24Feb2012
(
ID INT IDENTITY(1,1),
Value VARCHAR(20)
)
GO
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param1')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param2')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param3')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param4')
INSERT INTO #MyTestTable24Feb2012 VALUES ('Param5')
GO
DECLARE @SQLCommand VARCHAR(4000)
SET @SQLCommand=''
SELECT @SQLCommand = @SQLCommand+'Execute xsp_run_sproc2 '+ Value+'; ' FROM #MyTestTable24Feb2012
PRINT @SQLCommand
EXEC (@SQLCommand)
I think it might work if he needs to execute procs one by one.
Good point, and definitely a better option than a cursor...I hate cursors with a passion.
Thanks for providing another alternative 🙂
So martins idea gets rid of the cursor, and your idea gets rid of the nasty while loop I was going to end up doing... This is glorious, if it will be faster 😎
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply