Link to home
Create AccountLog in
Avatar of JohnModig
JohnModig

asked on

Optimizing a cursor query in MS SQL 2005

Dear Experts,
I am trying to create a stored procedure that returns multiple tables. The database contains two tables for this, one for accounts and one for costs. I need my stored procedure to create a table with all the costs for each account in a specific project.

I have managed to do this using a cursor (see code below). My sp is working but being a beginner I need to make sure that I am doing this in the best way possible. I have read that I should stay away from cursurs if possible due to resource problems. Is there an alternative for me? Can my code be optimized or re-written without using a cursor?
USE [project_management]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [projects].[spGetProjectCosts_cursor]
	-- parameter for project id
	@pId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	-- Declare the variables to store the values returned by FETCH.
	DECLARE @aId int, @aNo int
 
	-- create a cursor to find cost for distinct accounts of the project
	DECLARE account_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
	SELECT DISTINCT t1.account_id, t2.account_number 
	FROM projects.costs t1
	INNER JOIN finance.accounts t2 ON t1.account_id = t2.id 
	WHERE project_id = @pId
	ORDER BY t2.account_number ASC
 
	OPEN account_cursor
 
	-- Perform the first fetch and store the values in variables.
	-- Note: The variables are in the same order as the columns in the SELECT statement.
	FETCH NEXT FROM account_cursor
	INTO @aId, @aNo
 
	-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
	WHILE @@FETCH_STATUS = 0
	BEGIN
 
		-- Get the costs for this specific account
		-- I have limited the number of columns to one for EE
		SELECT cost_name
		FROM projects.costs
		WHERE account_id = @aId AND project_id = @pId
 
		-- This is executed as long as the previous fetch succeeds.
		FETCH NEXT FROM account_cursor
		INTO @aId, @aNo
	END
 
CLOSE account_cursor
DEALLOCATE account_cursor
 
END

Open in new window

Avatar of JohnModig
JohnModig

ASKER

Just to clarify further, "account" in this case is a financial post and not similar to "user accounts". Here are the structure and example data for the relevant tables:


finance.accounts
---------------------------------------------------------------------------------
id      account_number      name
---------------------------------------------------------------------------------
1      4111            Food
2      4112            Beverages
3      4122            Entertainment
4      4123            Decoration


project.costs
---------------------------------------------------------------------------------
id      account_id      project_id            cost_name
---------------------------------------------------------------------------------
1      1            1            Buffet
2      1            1            Desserts
3      2            1            Soda
4      2            1            Mineral water
5      4            1            Balloons
6      3            1            DJ


Using above data, I want the sp to return four tables, like so:


Table 1 (for account 4111 Food)
-----------------------------------------
cost_name
-----------------------------------------
Buffet
Desserts


Table 2 (for account 4112 Beverages)
-----------------------------------------
cost_name
-----------------------------------------
Soda
Mineral water


Table 3 (for account 4122 Entertainment)
-----------------------------------------
cost_name
-----------------------------------------
DJ


Table 4 (for account 4123 Decoration)
-----------------------------------------
cost_name
-----------------------------------------
Balloons


Hope this helps,
John
ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of chapmandew
I am not sure what you are trying to accomplish with your cursor....
I am aware that this might be a somewhat unusual scenario. That is why I need help. :)

The application I am coding needs several tables. I know that I can do one simple select query and then let my BLL divide the results into multiple temp tables. But if I can make the sp do that for me it would be faster and require less work on the client side, right?

Please note that the code I have is giving the results wanted. It is the very concept of using cursors I'm not sure about (after reading articles like this: http://www.sql-server-performance.com/tips/cursors_p1.aspx). Any help or input on the subject is greatly appreciated.

If you think I should take another approach on this then please help me understand why.
So, you want your stored procedure to return 4 result sets, right?  Just like what you have shown?

Table 1 (for account 4111 Food)
-----------------------------------------
cost_name
-----------------------------------------
Buffet
Desserts


Table 2 (for account 4112 Beverages)
-----------------------------------------
cost_name
-----------------------------------------
Soda
Mineral water


Table 3 (for account 4122 Entertainment)
-----------------------------------------
cost_name
-----------------------------------------
DJ


Table 4 (for account 4123 Decoration)
-----------------------------------------
cost_name
-----------------------------------------
Balloons
@ chapmendew:

Yes, that is what I want.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you very much for your answers. I feel that I am getting closer to the solution here.

@ dportas:

Reading your post makes me believe that I should make the application code do the job of dividing the result up into tables instead of having the sp doing it. However, if it would be possible of doing it with a set-based operation it would still be grand.

@ chapmandew:

Unfortunately the account numbers are dynamic. So it's not always four result sets with those exact account numbers. That only works with this example data. Sorry if I was unclear and thank you very much for trying to help out.
Well....how do you divide the sets into 4 then?
JohnModig: What would be the purpose of creating 4 tables? Why would you want to create tables in an application anyway?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you all for your answers. I now understand that this is an odd way to accompish what I want. I will probably follow your advices and make a simple sp and let my app code take care of the division for me. If I for some reson would choose not to do so, I thank Scott for confirming that the cursor is as good as anything else.