From: tshad on
I am looking at changing about 10 procedures that use a Permanent table for
nothing more than temporary storage to load and store various records from a
select statement and run a Join on this table to update other records.

It then deletes all the records it put into the table. The table is used by
multiple procedures at the same time keyed by a Guid it always gets at the
start of the routines.

There are about 4 indexes on this table.

There can be any from 1 to 1000 records involved in this.

I was thinking about dropping the table and just using a temp table. When
the table is created I would then have to create 3 indexes - would drop one
index (the Guid).

Then the table would go away when I exit the procedure. I don't think there
is any necessity to drop the table (and indexes) as it would go away anyway.

But now I don't have to delete the records at the end or create the Guid at
the beginning.

Anything I am missing here?

Thanks,

Tom


From: --CELKO-- on
In the 1960's we would write records (not rows) to a scratch tape and
share that tape among several procedures. It saved us the trouble of
dismounting the tape. One of the fundamental principles of software
engineering is that loosely coupled modules are better than what you
have. Look up "data coupling', "functional coupling" and "procedural
coupling"in any book on software engineering. YOu seem to have one or
more of these design problems.

For no more than 1000 rows (not records), why not re-write the code to
use a derived table in each procedure? The procedures would become
independent, loosely coupled modules. You will probably have to do
some more work than just that, but it is a start.
From: John Bell on
On Mon, 5 Apr 2010 11:32:29 -0700, "tshad" <tfs(a)dslextreme.com> wrote:

>I am looking at changing about 10 procedures that use a Permanent table for
>nothing more than temporary storage to load and store various records from a
>select statement and run a Join on this table to update other records.
>
>It then deletes all the records it put into the table. The table is used by
>multiple procedures at the same time keyed by a Guid it always gets at the
>start of the routines.
>
>There are about 4 indexes on this table.
>
>There can be any from 1 to 1000 records involved in this.
>
>I was thinking about dropping the table and just using a temp table. When
>the table is created I would then have to create 3 indexes - would drop one
>index (the Guid).
>
>Then the table would go away when I exit the procedure. I don't think there
>is any necessity to drop the table (and indexes) as it would go away anyway.
>
>But now I don't have to delete the records at the end or create the Guid at
>the beginning.
>
>Anything I am missing here?
>
>Thanks,
>
>Tom
>
Hi Tom

Creating many temp tables can create a bottleneck on tempdb See
http://support.microsoft.com/kb/328551

Have you looked at whether you need a table to hold this?

John
From: Tony Rogerson on
> For no more than 1000 rows (not records), why not re-write the code to
> use a derived table in each procedure? The procedures would become
> independent, loosely coupled modules. You will probably have to do
> some more work than just that, but it is a start.

1000 rows? From which planet did you pluck that figure from?

No statistics are held on table expressions like a derived table,
performance can be horrendous.

Temporary tables (akin to a relvar) are widely used and rightly too in SQL
Server, statistics are held on a temporary table so you get the best plan
available for what you are doing.

Temporary tables are in fact real tables and conform to Codd's rules.

What's your problem other than the syntax we use in SQL Server doesn't
explicitly follow ISO SQL?

--ROGGIE--


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:506ec56a-e829-447c-9896-ee1613b35a6e(a)g30g2000yqc.googlegroups.com...
> In the 1960's we would write records (not rows) to a scratch tape and
> share that tape among several procedures. It saved us the trouble of
> dismounting the tape. One of the fundamental principles of software
> engineering is that loosely coupled modules are better than what you
> have. Look up "data coupling', "functional coupling" and "procedural
> coupling"in any book on software engineering. YOu seem to have one or
> more of these design problems.
>
> For no more than 1000 rows (not records), why not re-write the code to
> use a derived table in each procedure? The procedures would become
> independent, loosely coupled modules. You will probably have to do
> some more work than just that, but it is a start.

From: Sylvain Lafontaine on
First, it's not only a question of speed of deleting a full table in
comparison to deleting multiple records but also because the temporary
tables are created in the TempDB database and therefore, don't pollute the
file space of your main databases. You should do so even if you were to
keep the same table for all the procedures along with the use of a GUID to
separate them. By the way, I don't see the point of using a GUID instead of
something like an identity value based on an integer field.

Second, you should consider dropping all the indexes as well because you
have volatil data and usually, it doesn't make sense to add indexes to
volatil data because the amount of work required to create the indexes,
updating them, searching them and finally, destroying them will be far
greater than the amount of work saved by retrieving a portion of the table
using a seek operation. Of course, if you need to retrieve in a single
operation 100% of the rows that you have inserted, then the indexes are
probably even more useless.

You should carefully consider your query plans to see if you really need any
index at all for your volatil data. The only one that is probably useful
will be the one for the GUID used to separate the records betweent the
procedures in the case of a unique, global permanent table. (And in this
case, probably that you should use an identity field instead of a GUID.).

If you are using a cursor to update your rows one by one, then the
conclusion about the need of the indexes might be different (but not
necessarily).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"tshad" <tfs(a)dslextreme.com> wrote in message
news:eNdIY5O1KHA.6104(a)TK2MSFTNGP06.phx.gbl...
>I am looking at changing about 10 procedures that use a Permanent table for
>nothing more than temporary storage to load and store various records from
>a select statement and run a Join on this table to update other records.
>
> It then deletes all the records it put into the table. The table is used
> by multiple procedures at the same time keyed by a Guid it always gets at
> the start of the routines.
>
> There are about 4 indexes on this table.
>
> There can be any from 1 to 1000 records involved in this.
>
> I was thinking about dropping the table and just using a temp table. When
> the table is created I would then have to create 3 indexes - would drop
> one index (the Guid).
>
> Then the table would go away when I exit the procedure. I don't think
> there is any necessity to drop the table (and indexes) as it would go away
> anyway.
>
> But now I don't have to delete the records at the end or create the Guid
> at the beginning.
>
> Anything I am missing here?
>
> Thanks,
>
> Tom
>
>