Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Using SQL Server Table Variables to Eliminate the Need for Cursors

, 15 Jan 2010
Rate this:
Please Sign up or sign in to vote.
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:
    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:
    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:
    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:
    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.:
    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:

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.

UPDATE MEMBERS SET MB_COUNT = NULL

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

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)

Share

About the Author

Leo Bi
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/
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermanoj kumar choubey6-Jul-12 19:37 
GeneralMy vote of 5 PinmemberRichard Waddell25-Sep-10 14:58 
QuestionWhy use table variables? PinmemberMember 199775318-Jan-10 13:43 
AnswerRe: Why use table variables? Pinmemberbml3i19-Jan-10 13:16 
QuestionIt's the same use subQuery 2 update? PinmemberMember 34159517-Jan-10 22:50 
AnswerRe: It's the same use subQuery 2 update? Pinmemberdefwebserver18-Jan-10 6:25 
GeneralRe: It's the same use subQuery 2 update? PinmemberMember 34159518-Jan-10 14:38 
GeneralPerformance improvement mood PinmemberRonald Bosma17-Jan-10 3:08 
GeneralRe: Performance improvement mood PinmemberKiliman17-Jan-10 4:12 
GeneralRe: Performance improvement mood PinmemberRonald Bosma17-Jan-10 5:42 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 15 Jan 2010
Article Copyright 2010 by Leo Bi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid