Efficient method to avoid repeated select on same table

Question

0.00/5 (No votes)

See more:

Hi,
I need to perform select statement on table dbo.test,which is having data aroung 4000 records.And i need to perform the select statemnt around 8 times in my SP with differenct conditions and need to insert the 8 differenct record set to 8 different tales.Can you please provide solutions to my doubt

May i need to temporary store data in SP,or its better to perform the select statemnt 8 times on the table (dbo.test) itself.
If its better to store temporary in my SP, Which method will be efficient (using temp table or table variable or any other method) I need to create a SP to perform the above action.

Thanks in advance

Posted 15-Jan-12 20:10pm

soubins

Updated 15-Jan-12 20:16pm

Prasad_Kulkarni

Add a Solution

Comments

Prasad_Kulkarni 16-Jan-12 2:17am

Dont use capital letters in your question

1 solution

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 · 2012-01-15T20:56:00

Solution 1

If your are returning a set of records from your select to re-use later within the same SP, then you should use a temporary table. A table variable (presumably a field in the original table which indicates "selected") would not be a good idea, for performance and multi-user reasons: You would have to clear them all at the start just in case, set the ones you want, then clear them all at the end of the SP.

Posted 15-Jan-12 20:56pm

OriginalGriff

Comments

soubins 16-Jan-12 4:20am

Thanks for the reply.I reached at the conclusion that for 4K records, there is no use of storing data temporarily.
So Can you please tell me for what amount of data we need to take care about the temporary storing..
The 8 select condition are based on same column only (category).But we are loading to 8 different target table having different column names and all.

OriginalGriff 16-Jan-12 4:35am

It's not necessarily the number of records, but the complexity of the select, and the amount of data you are throwing around. And the performance will depend on many, many factors: memory in the server, number of servers and interconnections, number of users, and so on.
Try it: Do some performance testing and see what results you get!