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.
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