Sybase's staging database tempdb

In this article, you will start by adjusting the location of the temporary library, step through the optimization of the staging database, and learn some of the methods and rules for optimizing the staging and staging tables in this process. Experimental environment requirements: you should have the privileges of the system administrator, the system has auths and article table. Steps:

First step: Adjust the location of the temporary library

The tempdb database is placed on the master device by default, and it is preferable to send the staging database to the detached device.

1 initialize a device to hold the staging database

Diskinit

Name= "Tempdb_dev",

Physname= "D:\sybase\example\tempdb.dat",

Vdevno=13,

size=15360

(Note: If you put the tempdb database on multiple devices, you need to initialize multiple database devices)

2 Extend the staging database to the device

Alterdatabasetempdbontempdb_dev=30

3 Open the tempdb database and remove the master device from the segment

Sp_dropsegment "Default", Tempdb,master

Sp_dropsegmentlogsegment,tempdb,master

4) Issue the following command to check whether the master device is no longer included in the default section

Selectdbid,name,segmapfromsysusages,sysdevices

Wheresysdevices.low<=syusages.size+vstart

Andsysdevices.high>;=sysusages.size+vstart-1

anddbid=2

and (status=2orstatus=3)

Note: If the temporary database is placed on multiple disk devices, the query performance can be improved by using the parallel query feature better.

Step two: Bind the staging database to the cache

Because temporary tables are created, used, and the staging database uses the data cache frequently, a cache should be created for the staging database so that it can reside in memory and contribute to decentralized I/O:

1. Create a named cache

Sp_cacheconfig "Tempdb_cache", "10m", "mixed"

2. Reboot Server

3, bundled temporary database to Tempdb_cache cache

Sp_bindcache "Tempdb_cache", tempdb

4, if there is a large I/O, configure the memory pool

Step three: Optimizing temporary tables

The use of most temporary tables is simple and requires little optimization. But requiring complex access to temporary tables should separate the creation and indexing of tables by using multiple procedures or batches. The following two techniques can improve the optimization slash of temporary tables;

Create an index on a temporary table

1) temporary table must exist

2 Statistics page must exist (that is, you cannot create an index on an empty table)

Slash the complex use of temporary tables into multiple batches or procedures to provide information to the optimizer.

The following process needs to be optimized:

Createprocbase_proc

As

Select*into#huge_resultfromauths

Select*fromarticle, #huge_resultwherearticle. author_code=

#huge_result. author_codeandsex= "0"

Better performance with two processes

1)

Createprocbase_proc

As

select*

Into#huge_result

Fromauths

Execselect_proc

2)

Createprocselect_proc

As

Select*fromarticle, #huge_result

Wherearticle.author_code= #huge_result. author_codeandsex= "0"

Description: In the same stored procedure or batch, when a table is created and used, the query optimizer cannot determine the size of the table.

Conclusion: Through this experiment we know that the temporary database optimization can greatly improve the performance of the system. In the actual work, must consider the concrete application situation, needs the long time experience accumulation.