Re: Temp files in Stored Procedures
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 05/25/04
- Next message: tim: "catching bcp errors"
- Previous message: mike: "transaction log file"
- In reply to: John Oakes: "Re: Temp files in Stored Procedures"
- Next in thread: Joe Celko: "Re: Temp files in Stored Procedures"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 May 2004 11:36:34 -0400
OK I see where your coming from now. Since a SELECT INTO does hold some
locks on the system tables until it is finished it can have an adverse
effect on some other type of operations.
-- Andrew J. Kelly SQL Server MVP "John Oakes" <john@nospam.networkproductions.net> wrote in message news:%23LZOMEmQEHA.3944@tk2msftngp13.phx.gbl... > 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: tim: "catching bcp errors"
- Previous message: mike: "transaction log file"
- In reply to: John Oakes: "Re: Temp files in Stored Procedures"
- Next in thread: Joe Celko: "Re: Temp files in Stored Procedures"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
