September 3, 2008 at 9:12 am
I am a newbie at this posting on SQL Central, so bare with me. I have been focusing more and more in strengthening my SQL skills in the last 5 years and am new at my current job. My first project assigned to me, was to take an existing stored procedure (hard coded within the whole thing) built by a non IT group and make it flexible for multiple users to gather data (like analytics) to review outcome and share with our researchers with some additional enhancements, modifications of course.
Task taken and I and a team member were assigned to this project and it worked out pretty well.
I during making coding changes and enhancements and performance tuning in this project, encountered a few learning/stumbling blocks along the way.
General Concept of Project:
We had to take and generate a baseline set of data for the end user whenever they executed the stored procedure each and every time and output to the query results window in an aggregated fashion based upon their requirements.
Concept of Dev work:
Now we used Global Temp table ## with a GUID as many, many samplers can run this stored procedure at the same time. Therefore in our thought process, this would allow our users to run concurrently without butting heads. Also utilized was the use of the 'EXEC' command on the @SQL strings we created on the fly.
(Keep in mind I do know and realize after a full code review we could use the sp_executesql command as it would use the same execution plan each and every time as it is identical each and every time vs. the EXEC.)
In a code review it was brought to our attention that we could use the SP_EXECUTESQL command along with the use of just a # table. so I have a few questions for anyone who can help me understand these.
First question:
If I want to use a # table within a stored procedure it this first off unique to each session that is open? Therefore if more than one person runs the stored procedure it will not drop from the TempDB and recreate, thus overwriting the other persons # table.
Second Question:
Will sp_executsql of your string variable (ie: @SQLStr) physically see the # table when you execute against it (ie: inserting data to it, etc.)
------------------------------------------------------------------------------------------
In my code snippet below we are using a ## table as we had problems executing the dynamic SQL to physically see a # table and we used EXEC (@SQLStr)
-- Declare Global temp table
DECLARE TEMPTABLEDETAILNAME varchar(50)
DECLARE SQLLOAD varchar(2000)
-- Create the global and Temp tables to be used in this process
SELECT @TEMPTABLEDETAILNAME = '##' + CONVERT(CHAR(36),NEWID())
Now create the structure for the temp table
set @sql = 'CREATE TABLE ' + Quotename(@TEMPTABLEDETAILNAME) + '
(
ID int,
Value1 varchar(150),
Value2 varchar(150),
Value3 varchar(150),
Value4 int
)'
exec(@sql)
/*
We have a permanent table filled with values to load the data based upon how the end user wants to see it, If they want to enter more than one value, they can to run multiple result sets as their output - PS we used two global #3 tables in ours one is - GLOBALBASELINE which holds our one time pull baseline to run multiple queries against to pull aggreated data for the output results to the ## Global TEMPTABLEDETAILNAME table*/
--load the data to the temp table replacing values as needed with actual @ values passed into the stored proc.
SELECT @SQLLoad = (SELECT REPLACE(REPLACE(REPLACE(REPLACE(SQLLOAD, '@VAR', '''' + @VAR + ''''), '@TEMPTABLEDETAILNAME', ''+ QUOTENAME(@TEMPTABLEDETAILNAME)+''), '@GLOBALBASELINE','' +QUOTENAME(@GLOBALBASELINE)+''),'@DemoReportGroupCode','''' + @DemoReportGroupCode + '''') AS SQL
FROM SAMPLINGTEXTDEMOVARIABLES
WHERE columnname = '' + @VAR + '')
exec(@SQLLOAD)
Is there a way to take what we have done above and implement it with the sp_executsql and use of # tables efficiently (and remove the use of a ##Global temp table with a GUID) and/or give an example to help me understand how this could work?
I am looking to learn and enhance my skills on performance tuning and best coding practices as I grow my skills in SQL and seek help and advice from the learning/education of others... so all input is welcome.
September 3, 2008 at 9:36 am
1) Yes, the #temp table should be unique to every stored procedure that opens it. Of course you want to make sure you drop the table at the end of the procedure.
2)I am not really sure what you are asking here. Are you asking if there is an easier way to create tables that return the information you need based upon values inputed? If so, I would create a Table-value function, pass the variables in, and then return the table. If this isn't what you are asking, please clarify.
September 3, 2008 at 9:44 am
Thank you Fraggle, you partially answerd my second question. I will try and clarify a bit.
Second Question:
Will sp_executsql of your string variable (ie: @SQLStr) physically see the # table when you execute against it (ie: inserting data to it, etc.) 😀
What I am trying to ask, is:
Would it be better to use the system execute sql (sp_executesql) command against the # local temp table variable when inserting data to it using dynamic sql instead of using the 'EXEC' @SQLSTR for performance/optimization since we are pretty much executing the same SQL statement every time.
I ask as I know using the 'EXEC' and sp_executesql use executin plans differently. We are pulling against a half a terabyte database for the data.
September 3, 2008 at 9:47 am
Well someone else may have a solid yes/no answer for you. I don't honest know.
However, If I were in your shoes, I would be testing both out to see which works better in your environment. That may be the only way to know for sure.
September 3, 2008 at 9:49 am
Thanks for the quick response. I'll check that out.:)
September 3, 2008 at 10:43 am
#Temp tables are unique per-session, not per procedure.
#Temp tables can span subordinate Batches, but is dropped when the batch that created it exits. EXEC('..') and sp_ExecutSQL() create subordinate batches within the current session/batch.
What this means is that if you want to communicate between you batches with a #Temp table, you need to first create it in the calling or outer batch.
The following script demonstrates how to do this:
--==== Make the Temp table in the Main batch session:
create table #MyTemp (foo varchar(1000))
--==== Test it from the Main batch Session
Insert into #MyTemp Select 'Data from main batch'
Select * from #MyTemp
--==== Test it from an EXEC sub-batch
EXEC('
-- test receiving data:
Select ''in EXEC sub-batch:'', * from #MyTemp
Delete from #MyTemp
-- test returning data
Insert into #MyTemp Select ''Data from EXEC sub batch''
Select * from #MyTemp
')
Select 'in main batch:', * from #MyTemp
--==== Test it from an sp_ExecuteSQL sub-batch
exec sp_ExecuteSQL N'
-- test receiving data:
Select ''in sp_ExecuteSQL sub-batch:'', * from #MyTemp
Delete from #MyTemp
-- test returning data
Insert into #MyTemp Select ''Data from sp_ExecuteSQL sub batch''
Select * from #MyTemp
'
Select 'in main batch:', * from #MyTemp
drop table #MyTemp
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:10 am
I appreciate the reply to my post. I have to say I learned something new from you today! Thank you. As with all thank you for the information, it has proved very helpful today indeed! 😎
September 3, 2008 at 11:15 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply