asked
ADVICE ON TEMPORARY DATA STORAGE
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
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.
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.
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.
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.
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
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.
