SQL anywhere 12 performance

[former_SQLA_member1694909]

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?

[Breck_Carter]

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 '

[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

[former_SQLA_member1694909]

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

[Breck_Carter]

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

[former_SQLA_member1694909]

Oeps, here they are

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

[VolkerBarth]

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.

[former_SQLA_member1694909]

AutoMultiProgrammingLevel   1
AutoMultiProgrammingLevelStatistics No
CurrentMultiProgrammingLevel    20
MaxMultiProgrammingLevel    80
MinMultiProgrammingLevel    16
MultiProgrammingLevel   20

What tool can I use to get the plans?

[VolkerBarth]

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

[former_SQLA_member1694909]

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?

[Breck_Carter]

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

[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