Re: Temp files in Stored Procedures
From: John Oakes (john_at_nospam.networkproductions.net)
Date: 05/25/04
- Next message: Aaron Bertrand - MVP: "Re: Stored Procedure: Incorrect syntax near 'END'"
- Previous message: Phil Davy: "Stored Procedure: Incorrect syntax near 'END'"
- In reply to: Andrew J. Kelly: "Re: Temp files in Stored Procedures"
- Next in thread: Andrew J. Kelly: "Re: Temp files in Stored Procedures"
- Reply: Andrew J. Kelly: "Re: Temp files in Stored Procedures"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 May 2004 10:03:25 -0400
According to sql-server-performance.com:
"If you need to use the SELECT INTO option, keep in mind that it can lock
system tables, preventing others users from accessing the data they need. If
you do need to use SELECT INTO, try to schedule it when your SQL Server is
less busy, and try to keep the amount of data inserted to a minimum. [6.5,
7.0, 2000] Added 11-28-2000"
http://www.sql-server-performance.com/transact_sql.asp
Perhaps the query you are currently running will finish faster; however, it
does have an adverse effect on the rest of the system. We have a fairly
large database here (approx. 1 terabyte, 5 billion records.) We use SQL2K.
If one of our users runs a query that returns a small result set using
SELECT INTO, we don't notice a performance degradation. If a user uses
SELECT INTO with a large result set, it has a significant adverse effect on
our system.
I read somewhere that if you're not using the FULL recovery model, SELECT
INTO is minimally logged, which is why people see faster times when they use
it. I also read, but haven't been able to verify, that this isn't the case
in 2000. However, in 2000 SELECT INTO can do inserts in blocks making it
faster. I also read that SQL 2000 doesn't have a lot of the locking
problems listed above.
Regardless of how SQL 2000 does SELECT INTO, I know from experience that
SELECT INTO can very seriously impact the database performance, whereas a
similar query using INSERT INTO has a much lesser impact. Maybe we see
these problems more here because we do mostly large queries. I have also
read several other postings where people claim SELECT INTO can take their
server to its knees. For us, this is certainly the case. Whenever our
server slows down and users start complaining about speed, it is almost
always a SELECT INTO causing the problem.
Maybe our situation is unique here and SELECT INTO works great for most
people. I would love to hear other opinions.
-John Oakes
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:uyeu9HdQEHA.2408@tk2msftngp13.phx.gbl...
> A SELECT INTO is most likely going to outperform an INSERT INTO. What are
> you basing that statement on?
>
> --
>
> Andrew J. Kelly
> SQL Server MVP
>
>
> "John Oakes" <john@nospam.networkproductions.net> wrote in message
> news:%234r8NDdQEHA.3420@TK2MSFTNGP11.phx.gbl...
> > It is also advisable to use INSERT INTO rather than SELECT INTO. There
> can
> > be very serious performance degradation when using SELECT INTO
> >
> > -John Oakes
> >
> >
> > "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
> > news:euxgWWbQEHA.3744@TK2MSFTNGP10.phx.gbl...
> > The ideal method is to avoid using temp tables within stored procedures.
> > Sometimes this is unavoidable. When you run into these times you can
> SELECT
> > ... INTO #SomeTable FROM....
> >
> > Notice the #? It specifies that the table that we are SELECTing INTO
> should
> > be a true temporary table. Temp tables created in this manner are
unique
> to
> > the connection that called it. Therefore you could have multiple people
> or
> > processes calling your stored procedure and each one would have their
own
> > temp table.
> >
> > People will often recommend that you create the temp table(s) as the
very
> > first step within your stored procedure and then insert into it (them)
> later
> > in the stored procedure. You could also create a table variable (SQL
> Server
> > 2000 and higher) and insert into it. This helps prevent stored
procedure
> > recompilation, which can happen if you perform a SELECT INTO within your
> > stored procedures.
> >
> > --
> > Keith
> >
> >
> > "Jerry" <JerryOfBorg@yahoo.com> wrote in message
> > news:uxa$AJbQEHA.3916@TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > When one does a "SELECT INTO" in SQL 2000 and specifies a Temp table
> does
> > > this table actually get called by the name you give it or is it some
> > unique
> > > name?
> > > I want to make sure that two people will not try to create the table
at
> > the
> > > same time. If this is an issue how do you avoid conflicts?
> > >
> > > Regards
> > >
> > >
> >
> >
>
>
- Next message: Aaron Bertrand - MVP: "Re: Stored Procedure: Incorrect syntax near 'END'"
- Previous message: Phil Davy: "Stored Procedure: Incorrect syntax near 'END'"
- In reply to: Andrew J. Kelly: "Re: Temp files in Stored Procedures"
- Next in thread: Andrew J. Kelly: "Re: Temp files in Stored Procedures"
- Reply: Andrew J. Kelly: "Re: Temp files in Stored Procedures"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
