March 28, 2023 at 1:56 pm
We have a stored procedure, working for yonks, which has a CREATE session temp table (e.g. #TempTable), then SELECT INTO and then - CREATE INDEX on this #TempTable.
There was now a minor change to another area within the above stored procedure, out of #TempTable scope, but our DBA rejected the change, stating "Rejecting. Shouldn't be creating temp tables or indexing them in stored procedures. SELECT INTO ... doesn't allow for concurrency."
Dr Google doesn't seem to explicitly discourage this approach, there are even examples on how to.
Can someone advise why we should not use temp tables in stored procedures, please? Also I don't see any concurrency issue in what we are doing.
Much obliged, as always.
March 28, 2023 at 2:03 pm
The DBA's right about the concurrency issue. Also, you should really only create a clustered index on a temp table and it should be created before the table is loaded.
You can use "SELECT ... INTO" to create the temp table, but use "TOP (0)" to create an empty table first. Then create the clus index (if any). Then load the table using a standard INSERT INTO ... SELECT ... FROM.
Here's a generic example:
SELECT TOP (0) * INTO #temp FROM sys.objects /*WHERE ...*/
CREATE UNIQUE CLUSTERED INDEX [temp__CL] ON #temp ( object_id );
INSERT INTO #temp SELECT * FROM sys.objects /*WHERE ...*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 28, 2023 at 2:05 pm
There should indeed be performance related advantages when using #temp objects.
According to your use case, an index on a #temp object may indeed help out.
performance - performance - performance - and interference avoidance for other consumers.
Test it - compare it. Make your case.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 28, 2023 at 2:05 pm
The DBA's right about the concurrency issue. Also, you should really only create a clustered index on a temp table and it should be created before the table is loaded.
You can use "SELECT ... INTO" to create the temp table, but use "TOP (0)" to create an empty table first. Then create the clus index (if any). Then load the table using a standard INSERT INTO ... SELECT ... FROM.
Here's a generic example:
SELECT TOP (0) * FROM sys.objects /*WHERE ...*/ INTO #temp
CREATE UNIQUE CLUSTERED INDEX [temp__CL] ON #temp ( object_id );
INSERT INTO #temp SELECT * FROM sys.objects /*WHERE ...*/
Thank you, Scott. I always assumed CREATE index should come after, as it significantly impacts the performance of INSERT INTO.
March 28, 2023 at 2:11 pm
ScottPletcher wrote:The DBA's right about the concurrency issue. Also, you should really only create a clustered index on a temp table and it should be created before the table is loaded.
You can use "SELECT ... INTO" to create the temp table, but use "TOP (0)" to create an empty table first. Then create the clus index (if any). Then load the table using a standard INSERT INTO ... SELECT ... FROM.
Here's a generic example:
SELECT TOP (0) * INTO #temp FROM sys.objects /*WHERE ...*/
CREATE UNIQUE CLUSTERED INDEX [temp__CL] ON #temp ( object_id );
INSERT INTO #temp SELECT * FROM sys.objects /*WHERE ...*/Thank you, Scott. I always assumed CREATE index should come after, as it significantly impacts the performance of INSERT INTO.
It depends. Remember, though, if you do it afterward, the entire table must be read, sorted and written again. Usually, it's better instead to just sort the data as it's initially loaded.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 29, 2023 at 9:48 am
The DBA's right about the concurrency issue. Also, you should really only create a clustered index on a temp table and it should be created before the table is loaded.
You can use "SELECT ... INTO" to create the temp table, but use "TOP (0)" to create an empty table first. Then create the clus index (if any). Then load the table using a standard INSERT INTO ... SELECT ... FROM.
Here's a generic example:
SELECT TOP (0) * INTO #temp FROM sys.objects /*WHERE ...*/
CREATE UNIQUE CLUSTERED INDEX [temp__CL] ON #temp ( object_id );
INSERT INTO #temp SELECT * FROM sys.objects /*WHERE ...*/
Scott,
With regards to the CLUSTERED index. I have INCLUDED segment in mine, which doesn't go with CLUSTERED.
Would you advise to remove the included section or to leave it non-clustered, please?
March 29, 2023 at 10:57 am
can you post your indexes create statement ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2023 at 11:58 am
ScottPletcher wrote:The DBA's right about the concurrency issue. Also, you should really only create a clustered index on a temp table and it should be created before the table is loaded.
You can use "SELECT ... INTO" to create the temp table, but use "TOP (0)" to create an empty table first. Then create the clus index (if any). Then load the table using a standard INSERT INTO ... SELECT ... FROM.
Here's a generic example:
SELECT TOP (0) * INTO #temp FROM sys.objects /*WHERE ...*/
CREATE UNIQUE CLUSTERED INDEX [temp__CL] ON #temp ( object_id );
INSERT INTO #temp SELECT * FROM sys.objects /*WHERE ...*/Scott,
With regards to the CLUSTERED index. I have INCLUDED segment in mine, which doesn't go with CLUSTERED. Would you advise to remove the included section or to leave it non-clustered, please?
Pick one direction or the other (well, test both). If an index is CLUSTERED, it automatically has the columns at the leaf level. To add columns at the leaf level for NONCLUSTERED indexes, you use INCLUDE. So, it's about testing what works better in your situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2023 at 2:32 pm
You can't include any columns in a clustered index (by definition it automatically includes all columns in the table anyway).
Just use the key column(s) you need. It's OK, and often much better, to have multiple columns in the clus key. If you lookup from the table using, say, three columns, then clus the table on those three columns.
If you can explicitly make the index UNIQUE, that will reduce overhead. For example, if the table has an identity column, make it the last column in the key to guarantee a unique key.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 29, 2023 at 3:51 pm
The DBA's right about the concurrency issue.
Please be specific as to what kind of blocking occurs and how long it actually lasts.
Then, let's see some proof or an article that contains such proof.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2023 at 6:34 pm
ScottPletcher wrote:The DBA's right about the concurrency issue.
Please be specific as to what kind of blocking occurs and how long it actually lasts.
Then, let's see some proof or an article that contains such proof.
Hello, Jeff
Is there any article or example you can point me to, advising it is not the case, please?
I will be happy to share it with out DBAs.
Much appreciated
March 30, 2023 at 5:58 am
Jeff Moden wrote:ScottPletcher wrote:The DBA's right about the concurrency issue.
Please be specific as to what kind of blocking occurs and how long it actually lasts.
Then, let's see some proof or an article that contains such proof.
Hello, Jeff Is there any article or example you can point me to, advising it is not the case, please?
I will be happy to share it with out DBAs.
Much appreciated
That's what I'm trying to get out of Scott. I'm never seen a major or frequent problem since the original hotfix way back in 6.5 prior to 6.5 SP1 where most of the fixes were made permanent. They've also made some additional improvements along the way.
What you might want to do is ask your DBA the same question... can (s)he prove that there will be contentious blocking?
We use SELECT INTO and create Clustered Indexes on Temp Tables a lot. I'm not saying that it's a panacea but the worst thing I've ever seen happen is that it blocked the object explorer window for a bit and that hasn't happened for years (since we installed 2016 years back).
The one thing that WILL cause contentious blocking is if you used a named constraint (which must be unique in a database) but you can make let the system name your constraints and you won't have blocking.
I WILL say that you're using twice the space for your temp tables while you build a Clustered Index on them. It would be better to pre-build the temp table WITH a clustered index (indexes can have the same name) and make sure that your inserts are "Minimally Logged".
And what makes you think you need a clustered index on your temp table, anyway? Ostensibly, they should only contain the data you need and they're not going to help you if you need to read the whole temp table. And, if you're doing deletes on a temp table, then you really need to fix your code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2023 at 7:47 am
BOR15K wrote:Jeff Moden wrote:ScottPletcher wrote:The DBA's right about the concurrency issue.
Please be specific as to what kind of blocking occurs and how long it actually lasts.
Then, let's see some proof or an article that contains such proof.
Hello, Jeff Is there any article or example you can point me to, advising it is not the case, please?
I will be happy to share it with out DBAs.
Much appreciated
That's what I'm trying to get out of Scott. I'm never seen a major or frequent problem since the original hotfix way back in 6.5 prior to 6.5 SP1 where most of the fixes were made permanent. They've also made some additional improvements along the way.
What you might want to do is ask your DBA the same question... can (s)he prove that there will be contentious blocking?
We use SELECT INTO and create Clustered Indexes on Temp Tables a lot. I'm not saying that it's a panacea but the worst thing I've ever seen happen is that it blocked the object explorer window for a bit and that hasn't happened for years (since we installed 2016 years back).
The one thing that WILL cause contentious blocking is if you used a named constraint (which must be unique in a database) but you can make let the system name your constraints and you won't have blocking.
I WILL say that you're using twice the space for your temp tables while you build a Clustered Index on them. It would be better to pre-build the temp table WITH a clustered index (indexes can have the same name) and make sure that your inserts are "Minimally Logged".
And what makes you think you need a clustered index on your temp table, anyway? Ostensibly, they should only contain the data you need and they're not going to help you if you need to read the whole temp table. And, if you're doing deletes on a temp table, then you really need to fix your code.
Thank you, Jeff
We don't have any DELETE from our temp table nor noticed any locking.
With regards to the NONCLUSTERED index with INCLUDE I found out it works faster in my specific scenario.
As to the constrains naming we have a policy around it, so no duplicate name in our case either.
March 30, 2023 at 2:10 pm
BOR15K wrote:Jeff Moden wrote:ScottPletcher wrote:The DBA's right about the concurrency issue.
Please be specific as to what kind of blocking occurs and how long it actually lasts.
Then, let's see some proof or an article that contains such proof.
Hello, Jeff Is there any article or example you can point me to, advising it is not the case, please?
I will be happy to share it with out DBAs.
Much appreciated
We use SELECT INTO and create Clustered Indexes on Temp Tables a lot. I'm not saying that it's a panacea but the worst thing I've ever seen happen is that it blocked the object explorer window for a bit and that hasn't happened for years (since we installed 2016 years back).
We used insert into followed by create clustered index a lot on temporary tables on some data warehouse processing. It was all working fine until we moved it to an AWS RDS SQL Server machine, the creation of the clustered index after inserting the data was taking 6 minutes on the old server but taking over 70 minutes on the new high spec AWS RDS machine. It is odd because the index creation even on my work laptop with the same data was also only taking only 6 minutes. To fix it I had to create the temporary table first with the clustered index then insert the data. This get complicated as some of the tables had a lot of columns from very complicated queries. It also makes it harder to maintain as a change to the query needs another change to the table definition.
March 31, 2023 at 2:06 am
To fix it I had to create the temporary table first with the clustered index then insert the data.
When I said "we", I should have said "they". What you wrote above is my preferred method along with following the requirements (which are pretty easy, as you know) for "Minimal Logging" to make it light-weight and nasty fast. That's if a clustered index is actually necessary on a Temp Table. Ostensibly, you've only got the correct data in the temp table anyway and probably don't actually need the CI.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply