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

more ▼

asked Jan 15, 2012 at 11:08 PM in Default

avatar image

21 3 3 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I would say it will depend. If you could provide some sample data and the conditions to be applied, we will be in a better position to guide you.

As far as temporary tables are concerned, If there would be different conditions for all 8 SELECTs, what benefit would you achieve by storing it into a temporary table? I think it will only add more cost to the query. Since it involves only 4K records, indexing would only give you little boost as compared to huge tables. But again, if it involves indexing different columns for the table, then adding 8 indexes for that would not be ideal either.

more ▼

answered Jan 15, 2012 at 11:24 PM

avatar image

Usman Butt
14.9k 6 13 21

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.

Jan 16, 2012 at 01:20 AM soubins

As it is already said by @Usman Butt, it will depend upon different situations where could you use the temporary tables. Like if you had to run the same query 8 times with the same criteria, then it would have make sense to store the data in temporary table. But in this case since you are using CATEGORY column as a filter, but with different conditions, you could add index on this column to speed up the query.

Jan 16, 2012 at 01:47 AM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left

I don't know what technical requirements you're trying to meet, but it sounds incredibly convoluted. I would focus on trying to reduce the complexity of the solution rather than ways to optimize it in place. In general focusing on a set-based solution rather than 8 different queries that have to be run on each of 4000 rows (that's the definition of a cursor) is going to get you places.

more ▼

answered Jan 17, 2012 at 04:42 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left

IF you consider overhead for each storage type this is the order from lowest to highest it is as follows 1. CTE 2. Table Variable 3. Temporary table

Make your decision with structrue that fulfils the requirements

more ▼

answered Jan 17, 2012 at 07:02 AM

avatar image

211 18 19 25

Wait, a CTE doesn't "store" anything. It's just a query so any excess overhead associated it with it is exactly the same as any other query.

Then, I'd want to see how you determine that table variables have less overhead than temp tables. The only real difference between the two is the existance of stats in temp tables, which increases some aspects of processing, but decreases others. Frequently the existence of stats makes temp table the less expensive proposition, not the more expensive one.

Jan 17, 2012 at 07:53 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 15, 2012 at 11:08 PM

Seen: 1389 times

Last Updated: Jan 16, 2012 at 05:45 AM

Copyright 2018 Redgate Software. Privacy Policy