December 2, 2021 at 4:32 pm
Hello All,
I have a temp table inside my procedure and its taking lot of time.
Could anyone suggest how to optimize this..
Below is my piece of code.
declare @User table
(
UserName varchar(200),
InstalledDate varchar(max)
)
insert into @User
select main.UserName,
left(main.installs,len(main.installs)-1) as "Installs"
From
(
select distinct ins2.UserName,
(
select convert(varchar(200),ins.FirstSeenDate)+', ' as [text()]
from @installs ins
where ins.UserName=ins2.UserName
order by ins.Username
for XML PATH('')
) [installs]
from @installs ins2
)[Main]
December 2, 2021 at 5:40 pm
Looking at that, the temp table is a heap, so unless tempdb is on slow disk OR tempdb is nearly full and your INSERT is causing a lot of auto-grow, it isn't likely the temp table (technically table variable) that is causing your problem - it is your SELECT.
Try commenting out the "INSERT INTO" part of your query and see how long that select takes to complete. You likely need to tune your @installs table OR change your nested select. Pretty sure that nested select is going to be what hurts your performance the most.
I would recommend the following to help with query tuning:
1 - post the DDL for all relevant objects
2 - post sample INSERT statements to get some data into the table
3 - post the expected output
4 - for performance tuning, posting the execution plan (not an image of it, but the XML so we can consume it and review it) is your best bet. OR you can review it yourself and may see what needs tuning.
With that particular query, you may get a performance boost by adding some indexes onto the @installs table (will need to convert it to a temp table rather than a table variable if I remember right).
FYI:
DECLARE @table TABLE <-- table variable
CREATE TABLE #table <-- temp table
Table variables exist within the transaction, temp tables exist within the session.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 3, 2021 at 3:56 pm
That's not a temporary table (I mean, it's temporary and it's a table, but it's not what SQL Server defines as a temporary table). That is a table variable. Table variables are just like temp tables in every major regard except one; they do not have statistics. So, if you're going to filter on the data within a temporary storage area, you want to use actual temporary tables, defined using the hash as @Mr Brian Gale shows above: #temptable. That will get you statistics, whether or not you choose to add indexes to it. That will help some on the performance.
Of course, why load the temporary storage at all? Why not simply run a single SELECT statement for the whole thing? Or, put the SELECT statement that fills the temporary storage into a sub-select in the second query (also known as a derived table, you can treat a query as a table)? Either of those is likely to be a lot more efficient than loading up temporary storage, which involves data movement and I/O, then running a query after the fact.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply