-- -- Benchmark for Tony Andrews: DDL for Tables Used -- PerformanceDBA 28 Nov 2010 -- -- Sybase ASE 15.0.3 680MB cache -- on Windows XP 1.0GB Allocated -- on Parallels -- on MacOS 10.4.11 2.0GB -- on Intel Core Duo 2.16 GHz -- Entire 186MB database fits into cache, so no PIO expected -- (we are testing processing, from a resident table) -- Idle server; 2 x engines; parallelism turned off (1 engine used) -- Single Client & Server on the same machine use staging go ----------------------------------------------------------------------------- -- DDL for Table 'staging.dbo.REF_Customer' ----------------------------------------------------------------------------- -- Population: 1000 Random CustomerIds create table REF_Customer ( CustomerId CustomerId not null , -- SMALLINT ShortName _DescriptionSmall not null , FullName _Description not null , CreatedDate _Date not null , UserStamp _UserStamp not null , TimeStamp _TimeStampSmall not null ) lock allpages on 'ref_seg' partition by roundrobin 1 on ('ref_seg') EXEC sp_bindcache 'ref_ca', 'staging', 'REF_Customer' create unique clustered index UC_ShortName on staging.dbo.REF_Customer(ShortName) on 'ref_seg' create unique nonclustered index U_CustomerId on staging.dbo.REF_Customer(CustomerId) on 'NCI_seg' exec sp_primarykey 'staging.dbo.REF_Customer', 'CustomerId' go ----------------------------------------------------------------------------- -- DDL for Table 'staging.dbo.CustomerTransaction' ----------------------------------------------------------------------------- -- Population: 2M rows -- distributed evenly across the 1000 CustomerIds -- 2000 per CustomerId: 1000 Credits; 1000 Debits create table CustomerTransaction ( CustomerId CustomerId not null , -- SMALLINT SequenceNo _IntSmall not null , Date _Date not null , TransactionCode TransactionCode not null , -- CHAR(1) Amount _MoneySmall not null ) lock allpages on 'data_seg' partition by roundrobin 1 on ('data_seg') create unique clustered index UC_PK on staging.dbo.CustomerTransaction(CustomerId, SequenceNo) on 'data_seg' go