cancel
Showing results for 
Search instead for 
Did you mean: 

ASE1570 VS ASE1254

Former Member
0 Kudos

I have a simple test about insert speed between ASE1570 and ASE1254.

Any idea to improve the throughput about ASE1570.

Testing Env:

ASE1570 SP130

ASE1254 ESD11.2

running on the same box.

Here's the testing steps:

use tempdb

go

create table MM (

MM01   int                              not null  ,

MM02  numeric(8,0)                     identity  ,

MM03  char(1)                          not null  ,

MM04  char(8)                              null  ,

MM05  datetime                         not null  ,

MM06  char(7)                          not null  ,

MM07  char(5)                          not null  ,

MM08  char(1)                          not null  ,

MM09  char(7)                          not null  ,

MM10  char(7)                          not null  ,

MM11  char(1)                          not null  ,

MM12  char(20)                         not null  ,

MM13  char(10)                             null  ,

MM14  char(10)                             null  ,

MM15  char(1)                          not null  ,

MM16  char(1)                              null  ,

MM17  char(1)                              null  ,

MM18  char(1)                          not null  ,

MM19  char(4)                          not null  ,

MM20  char(1)                              null  ,

MM21  char(3)                              null  ,

MM22  char(1)                              null  ,

MM23  smallmoney                       not null  ,

MM24  smallmoney                           null  ,

MM25  smallmoney                           null  ,

MM26  smallint                         not null  ,

MM27  smallint                             null  ,

MM28  smallint                             null  ,

MM29  int                                  null  ,

MM30  int                                  null  ,

MM31  smallint                             null  ,

MM32  char(1)                              null  ,

MM33  char(1)                              null  ,

MM34  char(1)                              null  ,

MM35  char(1)                              null  ,

MM36  char(1)                              null  ,

MM37  char(7)                          not null  ,

MM38  char(1)                          not null  ,

MM39  char(6)                          not null  ,

MM40  char(5)                          not null  ,

MM41  char(1)                          not null  ,

MM42  char(7)                          not null  ,

MM43  char(1)                          not null  ,

MM44  char(6)                          not null  ,

MM45  char(5)                          not null  ,

MM46  char(1)                              null  ,

CONSTRAINT PK_MMTSF PRIMARY KEY NONCLUSTERED ( MM01,MM02 ))

go

edit a sql file for example 1.sql:

use tempdb

go

truncate table MM

go

select getdate() as DT into #TT

go

INSERT INTO MM (MM01,MM03,MM04,MM05,MM06,MM07,MM08,MM09,MM10,MM11,MM12,MM13,MM14,MM15,MM16,MM17,MM18,MM19,MM20,MM21,MM22,MM23,MM24,MM25,MM26,MM27,MM28,MM29,MM30,MM31,MM32,MM33,MM34,MM35,MM36,MM37,MM38,MM39,MM40,MM41,MM42,MM43,MM44,MM45,MM46) VALUES

(1,'I','549     ','2015-01-01 10:08:17.306','A100000','ZZ001','O','A100000','0000333','1','TTO0100000          ','          ','          ','S',' ',' ','0','F905','S','000','L',100.0000,0.0000,0.0000,10,0,0,10,0,0,'O','O',' ','I',' ','TXO    ','C','201508','09000',' ','       ',' ','      ','     ','1')

go 5000

select datediff(ms,DT,getdate()) from #TT

go

and using isql to exec the 1.sql:

the result will look like:

ASE1570

/home/sybase/ase/phsu > isql -Usa -SSYBASE -Psybase -i1.sql

(1 row affected)

(1 row affected)

5000 xacts:

          

-----------

        6176

ASE1254

/home/sybase/ase/phsu > isql -Usa -SASE1254 -P -i1.sql

(1 row affected)

(1 row affected)

5000 xacts:

          

-----------

        3476

We can see ASE1254 is faster then ASE1570, any idea to improve ASE1570 throughput?

View Entire Topic
former_member182259
Contributor
0 Kudos

Actually, this most likely stems back to the 12.5 to 15.0 migration fun that you may not remember.   Remember, in 15.0, there was a huge change in the optimizer adding a lot of new capabilities - those new capabilities extended the code path, which made optimization longer.  You are issuing 5000 individual discrete insert statements - each of which has to be individually optimized before execution.   Cory gave a couple of applicable hints:

1 - enable the statement cache PLUS turn on literal autoparameterization.    This will cause static SQL to be more like the fully prepared statements it should have been in an OLTP app.

2 - while the data writes might not get staged to disk directly, with 5000 individual statements, you are pummeling the transaction log.  A key difference (as others have alluded to) is *any* physical difference in HW, file system, OS configuration etc. dealing with device IO as related to the log device.  A key metric to consider is #3 in Cory's email - specifically focusing on WaitEventID's 54 & 55 - which can be grabbed from both the 12.5 and 15.x servers by prefacing the query with 'select * from master..monProcessWaits where WaitTime>0 order by WaitEventID' - then run the SQL - then repeat the same query and delta the WaitEvents by ID to see the diffs

....all the other comments made by others also apply.