October 24, 2013 at 7:17 pm
Have been spending a lot of time lately using the SQL Profiler to try and analyse why we get some severe performance hits. One of the stored procedures (a third party app) looks to be one of the culprits and creates a series of temporary tables as part of the procedure. My question is, if 2 or 3 people end up calling the same procedure at that same time, does SQL have to wait until the first user has finished the procedure before the next one can proceed? Are the temp tables unique to the SID ?
Thanks Brad
October 24, 2013 at 7:37 pm
Looks like I can answer my own question. After a bit more searching I see that if multiple users run the procedure that creates temp tables they all have there own version of those tables.
😛
October 25, 2013 at 5:44 am
Yeah, if you create a temporary table as a local temp table, using #temptablename, it's only for the connection using it. You can create a global temporary table using ##temptablename that can be accessed by multiple connections.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 25, 2013 at 8:30 am
It has been my experience that one of the most common ways un-informed SQL developers created temporary tables was to perform a SELECT <some data> INTO <some temp table> FROM <some source table>
My previous job this construct was used in so many places causing so many performance issues. It was purely a case where the developer had no clue as to what he/she was doing. When result sets in these temp table exceeded 100k rows performance really suffered. I started rewriting these pieces of code using properly structured table, creating primary keys and indexes. One procedure I recall before the rewrite literately caused a major performance issue on the machine, running for over 20 minutes before I killed the session. Within 20 minutes of my time, reworking the poorly constructed procedure I managed to get the same job to run within less than a second. The end user was so grateful as this report was required to run a lot more frequently.
The other issue you need to keep in mind is how your tempdb is laid out. If it isn't provisioned properly you will see issues with using temp tables...
There may not be much control over how a third party application has been designed. However if you can optimize what you have control over, such as how SQL Server has been provisioned on the server is a starting place. Additionally nothing stops you from looking for worse offending stored procedures and take the time to optimize them. Often times the worse offending procedures could be assisted with a few additional indexes and statistics.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 25, 2013 at 9:41 am
In the risk of hijacking this thread, I have to partially disagree with Mr. Zimmerman.
SELECT...INTO... statements aren't bad for performance. Actually, with good coding habits they can be a lot better. This is just on how the processes are logged.
A major problem with SELECT...INTO is the restriction on defining data types, NULLS constraints or computed columns contrary to the explicit declaration on a CREATE TABLE. At least, the data type issue can be solved with proper explicit data conversions (CAST or CONVERT).
If you're speaking of performance issues due to the lack of indexes, you can create them after creating the table (unlike table variables which don't allow it.) Some people state that creating the index after populating the table is faster but I haven't seen a great difference.
Just to be clear, I'm not saying that SELECT...INTO is better than a CREATE TABLE with an INSERT statement, I'm just saying that it depends and tests should be made for each case.
October 25, 2013 at 9:58 am
I read Kurt's post as saying that temp tables should not be used at all where not necessary. I've seen result sets be inserted into a temp table, only to be selected straight out again. Now that can be a performance killer.
John
October 25, 2013 at 10:07 am
.. I guess my post might have been somewhat confusing... I will use temp tables, table variables, CTEs where the are the best based upon a number of things which comes down to the number of rows being temporarily held. The point that I made before was had to do with a stored procedure produce somewhere up to a dozen temp tables which were unindexed heaps. These temp tables were then joined together to produce further result sets. The temporary result sets were in the 100k rows in size. Bottom line the entire stored procedure misused the temp table construct.
I was trying to state that my example was a poor use of temp tables and how they were misused. There are better ways of using temp tables by simply applying good coding practices.
Hope this clears the air.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 25, 2013 at 10:35 am
Luis Cazares (10/25/2013)
SELECT...INTO... statements aren't bad for performance. Actually, with good coding habits they can be a lot better. This is just on how the processes are logged.
Insert into and select into can both be minimally logged.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2013 at 10:54 am
GilaMonster (10/25/2013)
Luis Cazares (10/25/2013)
SELECT...INTO... statements aren't bad for performance. Actually, with good coding habits they can be a lot better. This is just on how the processes are logged.Insert into and select into can both be minimally logged.
I know, but it's easier with select into 🙂
Or am I wrong?
October 25, 2013 at 11:02 am
Luis Cazares (10/25/2013)
Or am I wrong?
The only thing you need is that the table is empty and the insert specifies a tablock. Note that TempDB's logging is optimised over user databases anyway
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply