SQL Recordsets on table variable... O.K?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

During all my training and code reviews etc. we are always reminded to avoid Record Sets in our DB code because it hampers performance.

Firstly, I would like to know why.

Secondly, does it have the same performance drain if I create a Record Set on a table variable? (e.g. @myTable). I'd think no since the data being accessed is located in the RAM (And in my case it's only a hand full of records, so the amount of RAM used should be very small)

Comments?

Posted 4-Jul-11 0:08am

MatthysDT

Add a Solution

2 solutions

Solution 1

I'm not sure what you mean by record sets. Do you mean with in the DB ? Subqueries do hamper performance, b/c they create a nested loop, so each iteration on the outer loop, runs through the inner loop again. But, no matter what, your records live in RAM. And yes, if your tables are not going to be very big, ever, then the problem is probably non-existant. But, never make the mistake of testing with smaller data sets when your client will end up with large ones.

Posted 4-Jul-11 0:11am

Christian Graus

Comments

MatthysDT 4-Jul-11 6:30am

Well, best way to describe what I mean is by code. The following code is in a Stored Procedure on the DB:

DECLARE rst CURSOR LOCAL
FOR
SELECT a.field, b.field, c.field from myATable a
join.... -- join for myBTable
join.... -- join for myCTable
join.... etc
WHERE..... AND.... AND....
ORDER BY........ --(get the picture?)
OPEN rst
FETCH NEXT FROM rst INTO @a, @b, @c
WHILE (@@fetch_status = 0)
BEGIN
Print @a + @c + @b
FETCH NEXT FROM rst INTO @a, @b, @c
END

In this example, would it be faster to create a table variable, say, myABCTable and populate it with the required data, and use @myABCTable in the record set instead of the DB directly. (This means all WHERE clauses and JOINS are moved out of the record set's select)

I avoid sub-queries where possible and try to make all queries work with JOINS instead.

Thanks!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2011-07-04T00:12:00

Solution 2

Try it. If you aren't sure about timings, create two versions of the same code and use the Stopwatch class[^] to time them over say, 1000 iterations.
Then you will have numbers to base a decision on, which you can bring up at your next code review...

Posted 4-Jul-11 0:12am

OriginalGriff