I retrieve data (orders) from SQL anywhere 12 with Powerbuilder (datawindow). Retrieve is ready within 5 seconds.

Now I open a second instance of the application. I retrieve a report that takes about 3 minutes before retrieval is ready. During this retrieve I retrieve the orders again. Now it takes 90 seconds instead of 5.

The database is started as a server (dbsrv12). The computer has 1 socket with 16 logical processors. I checked if there is a lock in the database which is not the case. Maximum number of physical processors the server (dbsrv12) will use: 1

I can not understand why the retrieval of a report is slowing down the retrieval of the orders. There is 16Gb of memory, 16 logical processors and only 1 of these processors is 100% during retrieve.

Is this a development error?

asked 09 Apr, 14:37

ontsnapt's gravatar image

ontsnapt
1107717
accept rate: 0%

Show use all the dbsrv12.exe options used to start the server.

For example,

SELECT PROPERTY ( 'CommandLine' );

PROPERTY('CommandLine')
'-dt C:\\HASETUP2-V17\\partner2\\temp -n partner2_server 
-o C:\\HASETUP2-V17\\partner2\\dbsrv17_partner2_log.txt 
-oe C:\\HASETUP2-V17\\partner2\\dbsrv17_partner2_startup_log.txt 
-su **** -x tcpip(port=55503;dobroadcast=no) -zp -zl -zt 
C:\\HASETUP2-V17\\partner2\\database\\ddd.db -xp on '
(09 Apr, 15:36) Breck Carter

Also show us this:

SELECT PropName, Value FROM sa_eng_properties() 
WHERE PropName LIKE 'Is%'
   OR PropName LIKE 'License%'
   OR PropName LIKE 'N%'
   OR PropName LIKE 'Processor%'
   OR PropName LIKE 'Product%'
ORDER BY PropName;

PropName,Value
'IsEccAvailable',No
'IsFipsAvailable',No
'IsIQ',No
'IsNetworkServer',Yes
'IsPortableDevice',No
'IsRsaAvailable',Yes
'IsRuntimeServer',No
'IsService',No
'LicenseCount',3
'LicensedCompany',Restricted Use
'LicensedUser',Developer Edition
'LicenseKey',*****-*****-*****-*****-*****
'LicenseType',networked seat (per-seat)
'Name',ddd12
'NativeProcessorArchitecture',X86_64
'NumLogicalProcessors',8
'NumLogicalProcessorsUsed',8
'NumPhysicalProcessors',1
'NumPhysicalProcessorsUsed',1
'ProcessorArchitecture',X86_64
'ProductName',SQL Anywhere
'ProductVersion',12.0.1.4231
(09 Apr, 15:53) Breck Carter

Commandline:

-ha -n PEServer d:\Pe\data\company\pigexpert.db -n pe -hn0,21756:1396

Sa_eng_properties:

IsEccAvailable  No
IsFipsAvailable No
IsIQ    No
IsNetworkServer Yes
IsPortableDevice    No
IsRsaAvailable  Yes
IsRuntimeServer No
IsService   No
LicenseCount    4
LicenseKey  *****-*****-*****-*****-*****
LicenseType cpu-based
NativeProcessorArchitecture X86_64
(10 Apr, 08:27) ontsnapt
Replies hidden

Where are the rest of the properties? N%, P%

(11 Apr, 11:20) Breck Carter

Oeps, here they are

NumLogicalProcessors    16
NumLogicalProcessorsUsed    16
NumPhysicalProcessors   1
NumPhysicalProcessorsUsed   1
ProcessorArchitecture   X86
ProductName SQL Anywhere
ProductVersion  12.0.1.3797

(12 Apr, 01:43) ontsnapt

Could you add those for "MultiProgrammingLevel" - i.e. the number of tasks the database server can be active at the same time:

SELECT PropName, Value FROM sa_eng_properties() 
WHERE PropName LIKE '%multiProgramming%'
ORDER BY PropName;

Other than that, have you compared the plans of both instances of the fast and slow performance of the orders query? Does it use the same indexes?


FWIW, version 12.0.1. has long been EOL'ed - but you are using a rather old v12.0.1 build itself, the last one on Windows is 12.0.1.4484, AFAIK, you may have a look at the fixes here.

(12 Apr, 02:41) Volker Barth
Replies hidden
AutoMultiProgrammingLevel   1
AutoMultiProgrammingLevelStatistics No
CurrentMultiProgrammingLevel    20
MaxMultiProgrammingLevel    80
MinMultiProgrammingLevel    16
MultiProgrammingLevel   20

What tool can I use to get the plans?

(12 Apr, 03:00) ontsnapt

It's within the default DBISQL tool, see Plan Viewer and graphical plans in Interactive SQL .

(12 Apr, 03:14) Volker Barth

I checked if the plan changed. This seems not be the case.

I copied the database to a ssd disk and tested again. Now, the speed is correct. So, probably the disk is the problem. Dbsrv12 is using only 345Mb of memory. The db itself is 191 Mb. Can I force sql anywhere to use more memory or even load the entire database in memory?

(12 Apr, 06:43) ontsnapt
Comment Text Removed

dbsrv12 -c200M

...or bigger, you can never have too much RAM cache

http://dcx.sybase.com/index.html#1200/en/dbadmin/c-database-dbengine.html

(12 Apr, 14:14) Breck Carter

> I checked if the plan changed

Run a "Graphical Plan With Statistics", Save As to *.saplan, send me the file: breck carter gmail

(12 Apr, 14:17) Breck Carter
showing 4 of 11 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×266

question asked: 09 Apr, 14:37

question was seen: 116 times

last updated: 12 Apr, 14:17