cancel
Showing results for 
Search instead for 
Did you mean: 

What's reason cause the insert delay in tempdb and how to improve it?

Former Member
0 Kudos

I have ASE 12.5. The tempdb is on ramdisk.

Then I have a SP with a query like

select * from mytab....

...

When I run this SP, only 11 rows for the result. It took about 2 seconds.

Then I change the SP to keep result in a named temp table like:

Create table #tmptab...

Insert into #tmptab

select * from mytab....

then run it again, it's took about 6 seconds. big difference. As only 11 rows and everything should be done in memory, The speed should not slower than that. How to figure it out and improve the performance for this case?

Former Member
0 Kudos

thanks, Mark. Here is the results with your guide:

sp_helpdb tempdb

go               

    name db_size owner dbid created status 

1 tempdb                      2003.0 MB sa                       2 Oct 14, 2014       select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, abort tran on log full 

                

    name attribute_class attribute int_value char_value comments 

1 tempdb buffer manager cache binding 1 tempdb data cache [NULL] 

                

    device_fragments size usage created free kbytes   

1 master       3.0 MB data only           Apr  9 2011 11:25AM            1866   

2 tempdb_data    1500.0 MB data only           Apr  9 2011  1:27PM         1529940   

3 tempdb_logs     500.0 MB log only             Apr  9 2011  1:27PM not applicable   

                

              

1 log only free kbytes = 509930                                   

sp_helpdevice

    device_name physical_name description status cntrltype device_number low high 

go        

19 tempdb_data /db/oe/tempdb/tempdb_data.dat                 special, dsync off, physical disk, 1500.00 MB 2 0 3 50331648 51099647 

20 tempdb_logs /db/oe/tempdb/tempdb_logs.dat                 special, dsync off, physical disk, 500.00 MB 2 0 4 67108864 67364863         

---Without insert

exec sp_1 -- does the basic select * from mytab

      

    

        

      

1 Total actual I/O cost for this command: 0.   

2 Total writes for this command: 0   

3   

4 Execution Time 0.   

5 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 0 ms.   

        

      

1 10/15/2014 1:00:49.613 PM   

        

      

1 Total actual I/O cost for this command: 0.   

2 Total writes for this command: 0   

3   

4 Execution Time 0.   

5 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 0 ms.   

6 Total actual I/O cost for this command: 0.   

7 Total writes for this command: 0   

8   

9 Execution Time 0.   

10 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 3 ms.   

        

    status 

1 11150 C. 

2 7795 D. 

3 13618 DIR 

4 1445 EXE 

5 48 GMD 

6 55 IMD 

7 66471 M. 

8 374 NMD 

9 2383 REG 

        

      

1 Table: tab1 scan count 4, logical reads: (regular=124929 apf=0 total=124929), physical reads: (regular=0 apf=0 total=0), apf IOs used=0   

2 Table: tab2 scan count 204728, logical reads: (regular=235956 apf=0 total=235956), physical reads: (regular=0 apf=0 total=0), apf IOs used=0   

3 Table: tab2 scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=0 apf=0 total=0), apf IOs used=0   

4 Table: Worktable1  scan count 5, logical reads: (regular=151 apf=0 total=151), physical reads: (regular=0 apf=0 total=0), apf IOs used=0   

5 Total actual I/O cost for this command: 722082.   

6 Total writes for this command: 0   

7   

8 Execution Time 0.   

9 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 1796 ms.   

        

      

1 10/15/2014 1:00:51.413 PM   

        

      

1 Total actual I/O cost for this command: 0.   

2 Total writes for this command: 0   

3   

4 Execution Time 0.   

5 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 0 ms.   

        

---With insert

exec sp_2 -- does the basic select * from mytab

      

    

1 Total actual I/O cost for this command: 0. 

2 Total writes for this command: 0 

4 Execution Time 0. 

5 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 0 ms. 

      

    

1 10/15/2014 1:01:35.833 PM 

      

    

1 Total actual I/O cost for this command: 0. 

2 Total writes for this command: 0 

4 Execution Time 0. 

5 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 0 ms. 

6 Total actual I/O cost for this command: 0. 

7 Total writes for this command: 0 

9 Execution Time 0. 

10 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 0 ms. 

11 Table: #table1______02000450014068719 scan count 0, logical reads: (regular=18 apf=0 total=18), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 

12 Table: tab1 scan count 1, logical reads: (regular=121953 apf=0 total=121953), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 

13 Table: tab2 scan count 204728, logical reads: (regular=229304 apf=0 total=229304), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 

14 Table: tab2 scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 

15 Table: Worktable1  scan count 1, logical reads: (regular=29 apf=0 total=29), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 

16 Total actual I/O cost for this command: 702618. 

17 Total writes for this command: 0 

18 

19 Execution Time 31. 

20 SQL Server cpu time: 3100 ms.  SQL Server elapsed time: 3226 ms. 

      

    

1 10/15/2014 1:01:39.060 PM 

      

    

1 Total actual I/O cost for this command: 0. 

2 Total writes for this command: 0 

4 Execution Time 0. 

5 SQL Server cpu time: 0 ms.  SQL Server elapsed time: 0 ms. 

The difference is:

11 Table: #table1______02000450014068719 scan count 0, logical reads: (regular=18 apf=0 total=18), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Does this cause performance difference?

Accepted Solutions (0)

Answers (0)