SQL anywhere 12 performance

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?

performance

asked 09 Apr '21, 14:37

ontsnapt
125●12●12●18
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 '21, 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 '21, 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 '21, 08:27) ontsnapt

Replies hidden

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

(11 Apr '21, 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 '21, 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 '21, 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 '21, 03:00) ontsnapt

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

(12 Apr '21, 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 '21, 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 '21, 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 '21, 14:17) Breck Carter

showing 4 of 11 show all flat view

Be the first one to answer this question!

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

performance ×276

question asked: 09 Apr '21, 14:37

question was seen: 802 times

last updated: 12 Apr '21, 14:17

SAP SQL Anywhere

SQL anywhere 12 performance

Follow this question

Related questions