Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / SQL

Using SQL Server Table Variables to Eliminate the Need for Cursors

Rate me:
Please Sign up or sign in to vote.
4.69/5 (7 votes)
15 Jan 2010CPOL2 min read 95K   14   14
Avoid using cursor now, just try Table Variables!

Situation

Today, I encountered a tricky problem about performance.

There is one table which is used to store both kinds of members including the Company Member(with RECORD_TYPE 'C') and the Individual Member(with RECORD_TYPE 'I').

When it comes to an individual one, it has a specific field 'COMPANY_ID', which stores the MB_ID as its company ID when it belongs to a company. The field can also be NULL. By comparison, a company one has a specific field 'MB_COUNT', which stores the total count of individual members whose COMPANY_ID equals the company member's MB_ID.

Now, we need to write a procedure to calculate the MB_COUNT of each Company Records. It will show how many individuals the company has.

The below pic show the original data with 'MB_COUNT' NULL:

Using Sql Server Table Variables to Eliminate the Need for Cursors

The below picture shows the result data with 'MB_COUNT' calculated:

res02.jpg

Preparation

You can create your own testing database, then run the SQL-Scripts stored in the ZIP file (or run the below 1-5 scripts manually) to create the testing table and dynamic testing data.

  1. Create your testing DB:
    SQL
    IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='TEST_DB1')
    	DROP DATABASE TEST_DB1
    GO
    CREATE DATABASE TEST_DB1
    GO
    USE TEST_DB1
    GO
  2. Drop Table MEMBERS if it exists:
    SQL
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = _
    	object_id(N'MEMBERS') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  
    	DROP TABLE MEMBERS
  3. Create Table MEMBERS:
    SQL
    CREATE TABLE MEMBERS(
    	MB_ID INT PRIMARY KEY,
    	MEMBER_NAME NVARCHAR(100),
    	RECORD_TYPE CHAR(1),
    	COMPANY_ID INT,
    	MB_COUNT INT
    )
  4. Insert Company Data with MB_ID from 1001 to 6000:
    SQL
    DECLARE @num INT
    SET @num = 1
    
    WHILE(@num <= 5000)
    BEGIN
    	INSERT INTO MEMBERS(MB_ID, MEMBER_NAME, RECORD_TYPE, _
    		COMPANY_ID, MB_COUNT)VALUES(1000+@num, 'Company' + _
    		convert(varchar, @num), 'C', NULL, NULL);
    	SET @num = @num + 1
    END
  5. Insert Individual Data with MB_ID from 10001 to 40000, utilize RAND() method to create random COMPANYID from 1001 to 6000.:
    SQL
    SET @num = 1
    
    WHILE(@num <= 30000)
    BEGIN
    	INSERT INTO MEMBERS(MB_ID, MEMBER_NAME, RECORD_TYPE, _
    		COMPANY_ID, MB_COUNT)VALUES(10000+@num, 'Individual' + _
    		convert(varchar, @num), 'I', 1001 + FLOOR(RAND()*5000), NULL);
    	SET @num = @num + 1
    END

Solution

Actually, I have two totally different ways to achieve the requirement. Use 'cursor' or 'table variable'.

SOLUTION ONE--Use 'cursor' to loop records the whole table when the record stands for company:

SQL
DECLARE @ID INT,
		@MemberCount INT

DECLARE CUR1 CURSOR FOR
	SELECT MB_ID FROM MEMBERS 
	WHERE RECORD_TYPE = 'C'

OPEN CUR1
     FETCH CUR1 INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @MemberCount = COUNT(1)FROM MEMBERS 
      WHERE MEMBERS.COMPANY_ID = @ID
	UPDATE MEMBERS
      SET MB_COUNT=@MemberCount
      WHERE MEMBERS.MB_ID = @ID
	
	FETCH NEXT FROM CUR1 INTO @ID
END
Close CUR1
DEALLOCATE CUR1

Solution One's Result:

solution01.jpg

Initialize the MB_COUNT, use it to initialize the MB_COUNT to NULL.

SQL
UPDATE MEMBERS SET MB_COUNT = NULL

SOLUTION TWO--Use 'table variable' to solve this issue:

SQL
DECLARE @TEMP_TABLE TABLE (COMPANY_MB_ID INT, MB_COUNT INT)

INSERT INTO 
	@TEMP_TABLE 
SELECT 
	COMPANY_ID, count(MB_ID) AS MB_COUNT FROM MEMBERS
WHERE 
	RECORD_TYPE = 'I' AND COMPANY_ID IS NOT NULL
GROUP BY COMPANY_ID
ORDER BY COMPANY_ID ASC

UPDATE 
	MEMBERS
SET 
	MB_COUNT=(SELECT T.MB_COUNT FROM @TEMP_TABLE T _
		WHERE MEMBERS.MB_ID = T.COMPANY_MB_ID)
WHERE 
	MEMBERS.MB_ID IN (SELECT T2.COMPANY_MB_ID FROM @TEMP_TABLE T2)

Solution Two's Result:

solution02.jpg

Conclusion

Now we come to a conclusion: Don't ever use cursors in your SQL statement unless you are DBAs. For cursors will lock tables and they would affect the performance of the whole system. Beginners may feel comfortable with using cursors without concerning its poor performance. Let me say it again: DON'T use cursors. Try to use Table Variables!

Remember: Almost everything that you may first envision as requiring cursors to achieve can actually be done using the new SQL Server TABLE type. Let’s discard cursors and meet the challenge!

History

  • 15th January, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer E5 Systems
China China
I am currently a Programmer/Analyst working at E5 Systems, and had been engaged in software outsourcing industry for two years. I have strong interest and passion in software architecture and web application security fields. I am proficient in Java and C#.

My Blog: http://blog.bigcay.com/

Comments and Discussions

 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey6-Jul-12 19:37
professionalManoj Kumar Choubey6-Jul-12 19:37 
GeneralMy vote of 5 Pin
Richard Waddell25-Sep-10 14:58
Richard Waddell25-Sep-10 14:58 
QuestionWhy use table variables? Pin
Member 199775318-Jan-10 13:43
Member 199775318-Jan-10 13:43 
The most optimal way is:

UPDATE dbo.MEMBERS
SET MB_COUNT = A.MB_COUNT
FROM
(
SELECT
COMPANY_ID,
COUNT(*) AS MB_COUNT
FROM
dbo.MEMBERS
WHERE
RECORD_TYPE = 'I'
AND COMPANY_ID IS NOT NULL
GROUP BY COMPANY_ID
) A
WHERE
MB_ID = A.COMPANY_ID

No cursors and no table variables...
AnswerRe: Why use table variables? Pin
Leo Bi19-Jan-10 13:16
Leo Bi19-Jan-10 13:16 
QuestionIt's the same use subQuery 2 update? Pin
Member 34159517-Jan-10 22:50
Member 34159517-Jan-10 22:50 
AnswerRe: It's the same use subQuery 2 update? Pin
defwebserver18-Jan-10 6:25
defwebserver18-Jan-10 6:25 
GeneralRe: It's the same use subQuery 2 update? Pin
Member 34159518-Jan-10 14:38
Member 34159518-Jan-10 14:38 
GeneralPerformance improvement mood Pin
Ronald Bosma17-Jan-10 3:08
Ronald Bosma17-Jan-10 3:08 
GeneralRe: Performance improvement mood Pin
Kiliman17-Jan-10 4:12
Kiliman17-Jan-10 4:12 
GeneralRe: Performance improvement mood Pin
Ronald Bosma17-Jan-10 5:42
Ronald Bosma17-Jan-10 5:42 
GeneralRe: Performance improvement mood Pin
Leo Bi17-Jan-10 16:01
Leo Bi17-Jan-10 16:01 
GeneralRe: Performance improvement mood Pin
Leo Bi17-Jan-10 16:01
Leo Bi17-Jan-10 16:01 
GeneralThank You! Pin
defwebserver16-Jan-10 17:34
defwebserver16-Jan-10 17:34 
GeneralRe: Thank You! Pin
Leo Bi17-Jan-10 16:00
Leo Bi17-Jan-10 16:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.