index tepmorary table

Hi All,
I'm looking at a query at the moment which builds up a large-ish
(50,000 rows) temporary table, then goes about performing various
updates and selects. This is taking ages as the table is too large, so
full table scans are being performed on ab out 8 or 9 separate selects.
I've tried adding a few indexes to the temporary table, but with little
improvement, however it has prompted the following questions:
is there ever a good situation to index a local temporary table, or
does the overhead of building and maintaining the index always outweigh
the benefits?
will there be concurrency issues for the index? you can have several
temporary tables of the same name, if I create an index against it,
will another temporary table be able to have an index of that name
created against it as well (or is there something like a temporary
index)?
Like I said, in this case indexes don't appear to help anyway, but it
would be nice to know if it's ever an option.
Cheers
WillWill
> is there ever a good situation to index a local temporary table, or
> does the overhead of building and maintaining the index always outweigh
> the benefits?
Well defined indexes will improve performance

> will there be concurrency issues for the index? you can have several
> temporary tables of the same name, if I create an index against it,
> will another temporary table be able to have an index of that name
> created against it as well (or is there something like a temporary
> index)?
Can you be more specific? Are you going to JOIN the tables ?

> Like I said, in this case indexes don't appear to help anyway, but it
> would be nice to know if it's ever an option.
What are you trying to achive, can you explain ? DDL+ sample data +
expectedresult will be helpful.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144673353.232351.101530@.i39g2000cwa.googlegroups.com...
> Hi All,
> I'm looking at a query at the moment which builds up a large-ish
> (50,000 rows) temporary table, then goes about performing various
> updates and selects. This is taking ages as the table is too large, so
> full table scans are being performed on ab out 8 or 9 separate selects.
> I've tried adding a few indexes to the temporary table, but with little
> improvement, however it has prompted the following questions:
> is there ever a good situation to index a local temporary table, or
> does the overhead of building and maintaining the index always outweigh
> the benefits?
> will there be concurrency issues for the index? you can have several
> temporary tables of the same name, if I create an index against it,
> will another temporary table be able to have an index of that name
> created against it as well (or is there something like a temporary
> index)?
> Like I said, in this case indexes don't appear to help anyway, but it
> would be nice to know if it's ever an option.
> Cheers
> Will
>|||If you create a local temp table (i.e. name begins with a single #), then
there are no concurrency issues. As for performance, they often do help.
I'd populate the table first and then add the indexes. there are no hard
and fast rules, other than to try it and see if it helps.
If you post your DDL and the code that runs slowly, perhaps we can
troubleshoot further.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144673353.232351.101530@.i39g2000cwa.googlegroups.com...
Hi All,
I'm looking at a query at the moment which builds up a large-ish
(50,000 rows) temporary table, then goes about performing various
updates and selects. This is taking ages as the table is too large, so
full table scans are being performed on ab out 8 or 9 separate selects.
I've tried adding a few indexes to the temporary table, but with little
improvement, however it has prompted the following questions:
is there ever a good situation to index a local temporary table, or
does the overhead of building and maintaining the index always outweigh
the benefits?
will there be concurrency issues for the index? you can have several
temporary tables of the same name, if I create an index against it,
will another temporary table be able to have an index of that name
created against it as well (or is there something like a temporary
index)?
Like I said, in this case indexes don't appear to help anyway, but it
would be nice to know if it's ever an option.
Cheers
Will|||Uri, Tom,
Thanks for your replies. I can't post the exact code as I don't own it,
but the basic layout is as follows:
Create table #result(col1 nvarchar(100), col2 nvarchar(100), ... ,...,
coln decimal(18,2))
INSERT INTO #result
select stuff
from applicationtables
Update #Result
SET col5 = col4*col6
FROM #Result as r
JOIN applicationTable as a on a.Col9 = r.Col9
WHERE Col1 = 'a fixed filter'
The code then does a few more updates with different formulas and joins
on different columns, but the same basic statement
I thought that it would be made more efficient by indexing #Result such
that the updates could more quickly complete the table scans (e.g.
create an index on #results.col9), however the improvement was
negligeable.
In this case it was just a bit of opportunistic optimisation, I was
really posting the question more to know if this was a route to attempt
if I encounter any other queries with large temp tables - which it
appears it can be.
Thanks for the help
Will|||Be sure that when you do the joins that you have identical datatypes. For
example, if you join an nvarchar to a varchar, it will usually skip the
index.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144676705.952576.294480@.t31g2000cwb.googlegroups.com...
Uri, Tom,
Thanks for your replies. I can't post the exact code as I don't own it,
but the basic layout is as follows:
Create table #result(col1 nvarchar(100), col2 nvarchar(100), ... ,...,
coln decimal(18,2))
INSERT INTO #result
select stuff
from applicationtables
Update #Result
SET col5 = col4*col6
FROM #Result as r
JOIN applicationTable as a on a.Col9 = r.Col9
WHERE Col1 = 'a fixed filter'
The code then does a few more updates with different formulas and joins
on different columns, but the same basic statement
I thought that it would be made more efficient by indexing #Result such
that the updates could more quickly complete the table scans (e.g.
create an index on #results.col9), however the improvement was
negligeable.
In this case it was just a bit of opportunistic optimisation, I was
really posting the question more to know if this was a route to attempt
if I encounter any other queries with large temp tables - which it
appears it can be.
Thanks for the help
Will|||This may sound a little backward, but have you considered using a real table
to do the job?
I'll explain a little more. Add another column to your temp table called
say, ExecutionGUID, as a GUID column. Then inside your SP, create a GUID
variable, and populate it with NewID(), Now for all queries, including the
initial insert use your GUID to identify your new subset.
If you pass the Temp table to another stored procedure, this will cause the
procedure to recompile, which is extremely expensive. Using a real table
the procedure does not have to recompile every execution.
You can also combine this code with a cleanup job, which runs automatically,
you'll need to know what you can delete but it'll mean your client isn't
hanging about whilst the delete takes place.
This may not be suitable for your situation, but it might be worth a try.
Colin.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144673353.232351.101530@.i39g2000cwa.googlegroups.com...
> Hi All,
> I'm looking at a query at the moment which builds up a large-ish
> (50,000 rows) temporary table, then goes about performing various
> updates and selects. This is taking ages as the table is too large, so
> full table scans are being performed on ab out 8 or 9 separate selects.
> I've tried adding a few indexes to the temporary table, but with little
> improvement, however it has prompted the following questions:
> is there ever a good situation to index a local temporary table, or
> does the overhead of building and maintaining the index always outweigh
> the benefits?
> will there be concurrency issues for the index? you can have several
> temporary tables of the same name, if I create an index against it,
> will another temporary table be able to have an index of that name
> created against it as well (or is there something like a temporary
> index)?
> Like I said, in this case indexes don't appear to help anyway, but it
> would be nice to know if it's ever an option.
> Cheers
> Will
>|||Hi Colin,
Thanks for the reply,
In this case it's not possible however...
You see, first off the procedure is building up for a crystal report
(it's where I see the most horrendous use of temporary tables).
Therefore this has to be able to have 2 people running the report at
the same time (though I dread to think of the load on the server).
If I start using Guids and globalising this behaviour I'll start
getting issues with 2 competing processes locking this table, when in
fact the data is entirely separate, and can be kept that way. Also, the
data is temporary, and I don't see the benefit in actually creating the
table, just to drop it afterwards.
I don't quite get where you're coming from with the "pass to another
stored procedure issue" - this was never presented as an issue.
Everything is done in just the one procedure, and works fine. The issue
is that it's got no indexes, and therefore takes a couple of minutes to
process all the data it's just created in the temporary table.
Finally, and most critically, it was (as is usual for the people
posting and trying to fix the issue) not me that wrote this.
it's written, live, and I can't justify such a change without a lot of
procedure, and strong arguments of the benefit compared to the cost and
risk.
I agree however that there are times where a good use of a table would
have exceeded the use of a temporary one, this is just unfortunately
not one of them
Cheers
Will|||Hi Will,
I've inserted my comments in the text below...
<snip>
> In this case it's not possible however...
> You see, first off the procedure is building up for a crystal report
> (it's where I see the most horrendous use of temporary tables).
> Therefore this has to be able to have 2 people running the report at
> the same time (though I dread to think of the load on the server).
Ooo yuck, I hate Crystal Reports. I used to love it, but over the last 5
years, my opinion has changed, it tends to be extremely expensive on DB
Resources. You have my sympathy :-p

> If I start using Guids and globalising this behaviour I'll start
> getting issues with 2 competing processes locking this table, when in
> fact the data is entirely separate, and can be kept that way. Also, the
> data is temporary, and I don't see the benefit in actually creating the
> table, just to drop it afterwards.
There's a couple of interesting points here...
1. You can avoid the competing process issue by using the NoLock table hint.
Normally, GUID's generated are completely different, so in theory you should
be working on different parts of the table when inserting/updating, however
you can use the RowLock table hint to ensure that Page/Table locks do not
occur. This should allow 2 or more people to work on the table at the same
time.
2. The thing about a temporary table is that it's not really a temporary
table. It's a real one. It's just that SQL has added a cleanup routine to
so that the table is destroyed when the connection closes (or scope changes
to a parent level), it all intents and purposes it has all the same problems
as a real table, but you also need to allocate resources to create the
temporary table every time that the code is executed.

> I don't quite get where you're coming from with the "pass to another
> stored procedure issue" - this was never presented as an issue.
> Everything is done in just the one procedure, and works fine. The issue
> is that it's got no indexes, and therefore takes a couple of minutes to
> process all the data it's just created in the temporary table.
>
Your using just one procedure, so that's not an issue. If your stored proc
used either exec or sp_executesql the called code will need to be recompiled
every time, this happens because the temp table is in fact a different table
every time that it is called. To test this create a temporary table using
SSMS or QA then execute Select * from tempdb.sysobjects where xtype='U' your
temporary table will have a suffix which changes every session!
The problem is that as SQL needs to re-compile everytime, this can take alot
of processor resources.

> Finally, and most critically, it was (as is usual for the people
> posting and trying to fix the issue) not me that wrote this.
> it's written, live, and I can't justify such a change without a lot of
> procedure, and strong arguments of the benefit compared to the cost and
> risk.
I completely understand that problem.
Regards
Colin Dawson
www.cjdawson.com|||>> I'm looking at a query at the moment which builds up a large-ish
(50,000 rows) temporary table, then goes about performing various
updates and selects. This is taking ages as the table is too large, so
full table scans are being performed on ab out 8 or 9 separate selects.
<<
Why can you not avoid proprietary temp table code and procedural
processing with a single query? What you have describes (vaguely) is
the way we wrote code with magnetic tape and punch cards -- multiple
passes over the data.
Good SQL programmers do things in one statement, not by mimicking
magnetic tape file from the 1950's.|||Celko,
I can't avoid the temporary table code as I'm not allowed to re-write
the stored procedure. However I would argue that temporary tables have
their place just as anything else in SQL (even cursors). The reason
they are of benefit here is that the stored procedure is having to
produce a result set for crystal reports. This means that there has to
be a large amount of presentation logic within the stored procedure
(which I really hate, in my opinion crystal and other reporting tools
should provide a c# mid layer to allow that sort of manipulation of the
data). The requirements of the report mean that we need to update rows
based on others already in the table, and generally manipulate the
table to get it into the right format for crystal. There may be a
purely set based solution, but in this case I think it would be
detrimental to performance.
I would love it if there was a way to keep the queries simple
functional requests, but with no mid layer it's not that simple.