|
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. |
|
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... 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
|
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...
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.
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?
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.
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