I have an sql anywhere database installed on my local computer (I7, windows 11, 16Gb). Running a specific query takes 55 seconds. Looking at the size of the database this is normal.

I copy this database to a server (Intel Xeon Gold 6130 CPU, 40Gb, Windows Server 2019, 64bit). It is a virtual machine (HyperV) with 8 virtual processors. The engine dbsrv12 is running 185 databases. Running the same query now takes 10 minutes and 20 seconds. When the query is running CPU usage is about 20%, memory usage is 20Gb. There are 33 connections (1 on the database I test).

What should I check to find out why performancee is 20x slower on this server.

asked 26 Apr '23, 09:43

ontsnapt's gravatar image

ontsnapt
125121218
accept rate: 0%

Have you made sure cache usage is not an issue here (say, by re-doing the same query several times or preloading cache or other means)?

IMHO experience, queries are often waaayyyy slower than expected when intra-query parallelism is used, so for several queries we explicitly set max_query_tasks to 1 temporarily.

Of course you should compare plans of runs of both queries and try to find relevant differences...

(27 Apr '23, 04:49) Volker Barth
1

In addition to what Volker said, what are the disk sub-systems in play? SSD on your PC, but iSCSI over ethernet on the server would have a negative impact.

(27 Apr '23, 12:29) Bud Durland MRP
Replies hidden
Comment Text Removed

Thanks, I isolated the sql script from the powerbuilder application and used dbisql to start testing. Performance was good to my surprise. Then I noticed that in the where clause is a function (where order_date >= today() - 365). When order_date >= today() - 365 is replaced with order_date >= '2022-04-28' then the report takes 2 minutes. Powerbuilder uses a ODBC connection. Is dbisql also using a ODBC connection and why is there a performance difference?

(28 Apr '23, 06:23) ontsnapt

DBISQL is a Java application and JDBC. The deprecated dbisqlc tool uses ESQL. I've neer used PowerBuilder so can't tell whether it may deal with conditions internally, but many "4GL" DB tools do...

If the culprit is the date condition, you might also check whether "order_date >= dateadd(yy, -1, current date)" or the like perform better.

As stated, a graphical plan of the particular query run on both systems may help. And of course, an index on a heavily queried column may help, as well.

(28 Apr '23, 07:01) Volker Barth

When you use a date function instead of a static value SQLA will not use any existing index on the column order_date, so static dates are always to be preferred

(28 Apr '23, 08:03) Martin

HyperV like any other virtualisation system requires there to a full set of free cores (in this case 8) before it will do anything. As long as the host machine has more cores than has been assigned to the virtual machines it's not a problem, whereas if there are more assigned core than pysical you will see low cpu usage and apaulling performance. Then there is RAM and the caching, 185DB's sharing 20GB of RAM gives each DB effectivly nothing so the system will be reading from disk which as we all know is SLOW...

permanent link

answered 28 Apr '23, 06:06

TimScotland's gravatar image

TimScotland
101136
accept rate: 0%

Thanks, I've always wondered how we can optimize this. Should we allocate more memory? Or is it not possible with this number of databases?

(28 Apr '23, 06:33) ontsnapt
Your answer
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:

×275

question asked: 26 Apr '23, 09:43

question was seen: 884 times

last updated: 28 Apr '23, 08:03