Hello,

I'm using Sybase ASE 12.5. I have try to do load tests, it means I insert
large amount of records into the table and then I select them all in the cycle page by bage.

First I have insert 5.000 records into the 1 table (table BF_USER). After this insert I executed query UPDATE ALL STATISTICS BF_USER.
I have a test that retrieves page by page (20 records per page)
all data from a table. It means I'm executing 250 selects in the cycle
for retrieving each page and I'm retrieving also time duration of some
of this selects.

I'm executing following 2 commands:


1. SELECT COUNT(*) FROM BF_USER WHERE condition
- this select gives me number of all particular records in the table. This
number I will use for retrieving specific records starting from row X to Y
- in reports (attached bellow) is time duration of this select described by
"Duration for executing count statement"

2. EXEC GET_LIST ?, ?
- this stored procedure returns specific records starting from row X to Y
- there is created temporary table and particular records are inserted
there. Final records are retrieved from this temporary table, There was
necessary to set up db option "dll in tran" = TRUE for "tempdb"
database.
- in reports (attached bellow) is time duration of this select described by
"Duration for executing final query"

These selects gives me particular data into my List in java.


Problem is, there are executed lots of selects, but suddenly sybase hangs up for a while and then continue. But this duration is too long (for example 45 seconds).
I think, there is a problem with server configuration - not with sql queries, but I'm not sure.
Can you advice me, what can I set up for better performance?


Thank you in advance,
with best regards,

Julian

--------------------------------------------------------------------------
Here I attach report log from the load tests:


INFO: Total duration to create 5000 data objects was 37.750
seconds which is 132 items/sec

1. Load test FROM FIRST TO LAST:
================================

INFO: Duration for executing count statement for page 1 (at
position 1) = 187 ms
INFO: Duration for executing final query = 203 ms
INFO: Total duration = 421 ms

INFO: Duration for executing count statement for page 2 (at
position 21) = 16 ms
INFO: Duration for executing final query = 16 ms
INFO: Total duration = 47 ms

INFO: Duration for executing count statement for page 3 (at
position 41) = 32 ms
INFO: Duration for executing final query = 15 ms
INFO: Total duration = 63 ms

INFO: Duration for executing count statement for page 4 (at
position 61) = 15 ms
INFO: Duration for executing final query = 16 ms
INFO: Total duration = 47 ms

INFO: Duration for executing count statement for page 5 (at
position 81) = 15 ms
INFO: Duration for executing final query = 32 ms
INFO: Total duration = 62 ms

INFO: Duration for executing count statement for page 100
(at position 1981) = 0 ms
INFO: Duration for executing final query = 47 ms
INFO: Total duration = 63 ms

INFO: Duration for executing count statement for page 101
(at position 2001) = 15 ms
INFO: Duration for executing final query = 63 ms
INFO: Total duration = 93 ms

INFO: Duration for executing count statement for page 102
(at position 2021) = 16 ms
INFO: Duration for executing final query = 78 ms
INFO: Total duration = 110 ms

INFO: Duration for executing count statement for page 103
(at position 2041) = 15 ms
INFO: Duration for executing final query = 47 ms
INFO: Total duration = 78 ms

INFO: Duration for executing count statement for page 104
(at position 2061) = 16 ms
INFO: Duration for executing final query = 46 ms
INFO: Total duration = 62 ms

INFO: Duration for executing count statement for page 105
(at position 2081) = 32 ms
INFO: Duration for executing final query = 32 ms
INFO: Total duration = 94 ms

!!! FAILURE !!!
There is poor perforance for loaded data in the list
starting at the position 3261 (total duration of the SQL select is 13.141 seconds)



2. Load test FROM LAST TO FIRST:
================================

INFO: Duration for executing count statement for page 205
(at position 4081) = 0 ms
INFO: Duration for executing final query = 93 ms
INFO: Total duration = 109 ms

INFO: Duration for executing count statement for page 204
(at position 4061) = 0 ms
INFO: Duration for executing final query = 94 ms
INFO: Total duration = 109 ms

INFO: Duration for executing count statement for page 203
(at position 4041) = 16 ms
INFO: Duration for executing final query = 94 ms
INFO: Total duration = 110 ms

INFO: Duration for executing count statement for page 202
(at position 4021) = 15 ms
INFO: Duration for executing final query = 125 ms
INFO: Total duration = 140 ms

INFO: Duration for executing count statement for page 201
(at position 4001) = 16 ms
INFO: Duration for executing final query = 93 ms
INFO: Total duration = 125 ms

INFO: Duration for executing count statement for page 200
(at position 3981) = 32 ms
INFO: Duration for executing final query = 62 ms
INFO: Total duration = 110 ms

INFO: Duration for executing count statement for page 105
(at position 2081) = 15 ms
INFO: Duration for executing final query = 47 ms
INFO: Total duration = 62 ms

INFO: Duration for executing count statement for page 104
(at position 2061) = 16 ms
INFO: Duration for executing final query = 62 ms
INFO: Total duration = 78 ms

INFO: Duration for executing count statement for page 103
(at position 2041) = 16 ms
INFO: Duration for executing final query = 47 ms
INFO: Total duration = 79 ms

INFO: Duration for executing count statement for page 102
(at position 2021) = 15 ms
INFO: Duration for executing final query = 63 ms
INFO: Total duration = 78 ms

INFO: Duration for executing count statement for page 101
(at position 2001) = 15 ms
INFO: Duration for executing final query = 63 ms
INFO: Total duration = 78 ms

INFO: Duration for executing count statement for page 100
(at position 1981) = 15 ms
INFO: Duration for executing final query = 47 ms
INFO: Total duration = 62 ms

!!! FAILURE !!!
There is poor perforance for loaded data in the list starting at the position 1801
(total duration of the SQL select is 45.453 seconds)