More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Temp Tables and OLTP in Sybase
Does it affect my application?
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.
Why? Why? Why?
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.
How do I fix this?
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.
Summary
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.
Solution in pseudo-code
If you have an application that creates the same temp table many times within one connection, here's how to convert it to reduce and reuse temp table creations. Raymond Lew has supplied a detailed example for trying this.
Old
open connection
loop until time to go
exec procedure vavoom_often
/* vavoom_often creates and uses #gocart for every call */
/* eg: select * into #gocart from gocart */
go
.
.
.
loop-end
close connection
New
open connection
/* Create the temporary table outside of the sproc */
select * into #gocart from gocart where 1 =2 ;
go
loop until time to go
exec procedure vavoom_often
/* vavoom_often reuses #gocart which */
/* was created before exec of vavoom_often */
/* - First statement may be a truncate table #gocart */
/* - Execute with recompile */
/* if your table will have more than 10 data pages */
/* as the optimizer will assume 10 data pages for temp tables */
go
.
.
.
loop-end
close connection
Note that it is necessary to call out the code to create the table and it becomes a pain in the butt because the create-table statement will have to be replicated in any stored proc and in the initialization part of the application - this can be a maintenance nuisance. This can be solved by using any macro package such as m4 or cpp. or by using and adapting the scripts from Raymond Lew.
Brian Black posted a stronger notice than this to the SYBASE-L list, and I would agree, that any use of select/into in a production environments should looked at very hard. Even in DSS environments, especially if they share tempdb with an OLTP environment, should use select/into with care.
From: Raymond Lew
At our company, we try to keep the database and the application loosely coupled to allow independent changes at the frontend or the backend as long as the interface stays the same. Embedding temp table definitions in the frontend would make this more difficult.
To get away from having to embed the temp table definitions in the frontend code, we are storing the temp table definitions in the database. The frontend programs retrieve the definitions and declare the tables dynamically at the beginning of each session. This allows for the change of backend procedures without changes in the frontend when the API does not change.
Enclosed below are three scripts. The first is an isql script to create the tables to hold the definitions. The second is a shell script to set up a sample procedure named vavoom. The third is shell script to demonstrate the structure of application code.
I would like to thank Charles Forget and Gordon Rees for their assistance on these scripts.
--start of setup------------------------------------------------------
/* Raymond Lew - 1996-02-20 */
/* This isql script will set up the following tables:
gocart - sample table
app_temp_defn - where temp table definitions are stored
app_temp_defn_group - a logical grouping of temp table definitions
for an application function
*/
/******************************/
/* gocart table - sample table*/
/******************************/
drop table gocart
go
create table gocart
(
cartname char(10) null
,cartcolor char(30) null
)
go
create unique clustered index gocart1 on gocart (cartname)
go
insert into gocart values ('go1','blue ')
insert into gocart values ('go2','pink ')
insert into gocart values ('go3','green ')
insert into gocart values ('go4','red ')
go
/****************************************************************/
/* app_temp_defn - definition of temp tables with their indexes */
/****************************************************************/
drop table app_temp_defn
go
create table app_temp_defn
(
/* note: temp tables are unique only in first 13 chars */
objectname char(20) not null
,seq_no smallint not null
,defntext char(255) not null
)
go
create unique clustered index app_temp_defn1
on app_temp_defn (objectname,seq_no)
go
insert into app_temp_defn
values ('#gocart',1,'select * into #gocart')
insert into app_temp_defn
values ('#gocart',2,' from gocart where 1=2 ')
go
insert into app_temp_defn
values ('#gocartindex',1,
"create unique index gocartindex on #gocart (cartname) ")
go
insert into app_temp_defn
values ('#gocart1',1, 'select * into #gocart1 from gocart where 1=2')
go
/***********************************************************************/
/* app_temp_defn_group - groupings of temp definitions by applications */
/***********************************************************************/
drop table app_temp_defn_group
go
create table app_temp_defn_group
(
appname char(8) not null
,objectname char(20) not null
)
go
create unique clustered index app_temp_defn_group1
on app_temp_defn_group (appname,objectname)
go
insert into app_temp_defn_group values('abc','#gocart')
insert into app_temp_defn_group values('abc','#gocartindex')
go
/***********************************************************/
/* get_temp_defn - proc for getting the temp defn by group */
/***********************************************************/
drop procedure get_temp_defn
go
create procedure get_temp_defn
(
@appname char(8)
)
as
if @appname = ''
select defntext
from app_temp_defn
order by objectname, seq_no
else
select defntext
from app_temp_defn a
, app_temp_defn_group b
where a.objectname = b.objectname
and b.appname = @appname
order by a.objectname, a.seq_no
return
go
/* let's try some tests */
exec get_temp_defn ''
go
exec get_temp_defn 'abc'
go
--end of setup ----------------
--- start of make.vavoom -----------
#!/bin/sh
# Raymond Lew - 1996-02-20
#
# bourne shell script for creating stored procedures using
# app_temp_defn table
#
# demo procedure vavoom created here
#
# note: you have to change the passwords, id and etc. for your site
# note: you might have to some inline changes to make this work
# check out the notes within the body
# get the table defn's into a text file
#
# note: next line :you will need to end the line immediately after eot \
isql -Ukryten -Pjollyguy -Sstarbug -w255 <;< eot \
| grep -v '\-\-\-\-' | grep -v 'defntext ' | grep -v ' affected' > tabletext
exec get_temp_defn ''
go
eot
# note: prev line :you will need to have a newline immediately after eot
# go mess around in vi
vi tabletext
#
# create the proc vavoom after running the temp defn's into db
#
isql -Ukryten -Pjollyguy -Sstarbug -e <;< eot |more
`cat tabletext`
go
drop procedure vavoom
go
create procedure vavoom
(
@color char(10)
)
as
truncate table #gocart1 /* who knows what lurks in temp tables */
if @color = ''
insert #gocart1 select * from gocart
else
insert #gocart1 select * from gocart where cartcolor=@color
select @color '@color', * from #gocart1
return
go
exec vavoom ''
go
exec vavoom 'blue'
go
eot
# note: prev line :you will need to have a newline immediately after eot
exit
# end of unix script
--- end of make.vavoom --------------
--- start of defntest.sh ---------
#!/bin/sh
# Raymond Lew 1996-02-01
#
# test script: demonstrate with a bourne shell how an application
# would use the temp table definitions stored in the database
#
# note: you must run setup and make.vavoom first
#
# note: you have to change the passwords, id and etc. for your site
# note: you might have to some inline changes to make this work
# check out the notes within the body
# get the table defn's into a text file
#
# note: next line :you will need to end the line immediately after eot \
isql -Ukryten -Pjollyguy -Sstarbug -w255 << eot \
| grep -v '\-\-\-\-' | grep -v 'defntext ' | grep -v ' affected' > tabletext
exec get_temp_defn ''
go
eot
# note: prev line :you will need to have a newline immediately after eot
# go mess around in vi
vi tabletext
isql -Ukryten -Pjollyguy -Sstarbug -e << eot | more
`cat tabletext`
go
exec vavoom ''
go
exec vavoom 'blue'
go
eot
# note: prev line :you will need to have a newline immediately after eot
exit
# end of unix script
--- end of defntest.sh ------
(Continued on next question...)
