全部博文（13）
分类： Sybase
2008-11-10 11:17:26
(Note from Ed: It appears that with ASE 12, Sybase have solved the problem of select/into locking the system tables for the duration of the operation. The operation is now split into two parts, the creation of the table followed byt the insert. The system tables are only locked for the first part, and so, to all intents and purposes, the operation acts like a create/insert pair whilst remaining minimally logged.
Our shop would like to inform folks of a potential problem when using temporary tables in an OLTP environment. Using temporary tables dynamically in a OLTP production environment may result in blocking (single-threading) as the number of transactions using the temporary tables increases.
This warning only applies for SQL that is being invoked frequently in an OLTP production environment, where the use of "select into..." or "create table #temp" is common. Application using temp tables may experience blocking problems as the number of transactions increases.
This warning does not apply to SQL that may be in a report or that is not used frequently. Frequently is defined as several times per second.
Our shop was working with an application owner to chase down a problem they were having during peak periods. The problem they were having was severe blocking in tempdb.
What was witnessed by the DBA group was that as the number of transactions increased on this particular application, the number of blocks in tempdb also increased.
We ran some independent tests to simulate a heavily loaded server and discovered that the data pages in contention were in tempdb's syscolumns table.
This actually makes sense because during table creation entries are added to this table, regardless if it's a temporary or permanent table.
We ran another simulation where we created the tables before the stored procedure used it and the blocks went away. We then performed an additional test to determine what impact creating temporary tables dynamically would have on the server and discovered that there is a 33% performance gain by creating the tables once rather than re-creating them.
Your mileage may vary.
To make things better, do the 90's thing -- reduce and reuse your temp tables. During one application connection/session, aim to create the temp tables only once.
Let's look at the lifespan of a temp table. If temp tables are created in a batch within a connection, then all future batches and stored procs will have access to such temp tables until they're dropped; this is the reduce and reuse strategy we recommend. However, if temp tables are created in a stored proc, then the database will drop the temp tables when the stored proc ends, and this means repeated and multiple temp table creations; you want to avoid this.
Recode your stored procedures so that they assume that the temporary tables already exist, and then alter your application so that it creates the temporary tables at start-up -- once and not every time the stored procedure is invoked.
That's it! Pretty simple eh?
The upshot is that you can realize roughly a 33% performance gain and not experience the blocking which is difficult to quantify due to the specificity of each application.
Basically, you cannot lose.
