• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • will PowerServerLabel Merge statements run SQLs parallelly(Async) on DB?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved will PowerServerLabel Merge statements run SQLs parallelly(Async) on DB?

  1. Misc
1
Votes
Undo
  1. Kirankumar Chavadi
    Kirankumar Chavadi
  2. PowerServer
  3. Monday, 27 May 2024 16:48 PM UTC

we are trying to improve the performance of our application, we have a couples of Embedded SQLs and Datawindow retrieves called one after another.

so to improve the performance, we moved all the SQL DB calls in a PowerServerLabel Merge statement which reduces Multiple server calls to a single call. However we observed that these calls on not executed parallelly on DB server (noted via SQL Trace)and there is not much improvement seen at performance.

is there any setting we need to do on Powerserver project? so that these call are executed parallelly on DB

if not any plans to implement the same?

 

 

  1. Multi Threading
  2. PowerServer
  3. SQL Server
How to point to correct Applications Json files
Datawindow working in Client server but not workin...
Responses (4)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 30 May 2024 13:18 PM UTC
  2. PowerServer
  3. # 1

Hi, Kirankumar -

After careful review of your original and follow-up posts/replies, I note that you have not mentioned what DBMS (vendor and version) you are using, so I don't know what database capabilities/features are available to you.

Not knowing the details of what your application is doing, I wonder if the steps being performed in the client could instead be refactored into one or more stored procedures and/or if performance "tuning" of the SQL would provide better performance. Either one (or both) can sometimes provide marked improvement in performance.

Perhaps something to consider?

Best regards, John

Comment
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 28 May 2024 19:18 PM UTC
  2. PowerServer
  3. # 2

Hi Kirankumar, I think you misunderstood the purpose of the PowerServerLabel feature.  The purpose is to compensate for network performance issue of roundtripping many times to the Web server.  It is not going to boost the performance of your database itself or make the app faster than it would otherwise be if you deployed over LAN.  If you want to boost DB response time (while also reducing roundtripping across the network), I would suggest you look at this technique in the performance tuning guide: https://docs.appeon.com/ps2022r3/Partitioning_transactions.html

Comment
There are no comments made yet.
Kirankumar Chavadi
Kirankumar Chavadi Accepted Answer Pending Moderation
  1. Tuesday, 28 May 2024 04:52 AM UTC
  2. PowerServer
  3. # 3

Hello Chris,

We were under the impression that PowerServer would execute all SQL queries in parallel using multithreading, checking for completion before returning the results to PowerBuilder.

Do Appreon have any plans regarding improvements to the way SQL commands are executed in PowerServer?

or should SQL commands be executed synchronously even when they are independent ?

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 28 May 2024 11:15 AM UTC
Hi KiranKumar;

While that would be nice "in theory", many SQL queries could require a result set from a previous query in order to run the next query with dependant WHERE information needed from the previous one.

So if PS were to support asynchronous DML threads, there would need to be a "dependency" mechanism for series of parallel queries for the above reason. So in the near term, I can't see this happening.

Of course, PS mutithreaded when it comes to various multiple App concurrent threads in progress.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 27 May 2024 19:26 PM UTC
  2. PowerServer
  3. # 4

Hi KiranKumar;

  They are executed synchronously as PS needs to return to the PB app whether or not those collective commands worked as a whole. Then the PB app can signal a successful "end of processing" via a commit command before processing the next batch. It would be the same as a called That then calls other various SP''s. If all SP's work ok then the primary SO can issue a commit before returning the overall completion status back to the the caller.

Regards ... Chris

Comment
mike S
  1. mike S
  2. Tuesday, 28 May 2024 15:01 PM UTC
chris, i don't think that it is required to run synchronously (from a logic standpoint) with selects. when you send up a set of sql commands via the labels, you send up just a bunch of sql commands. results from the first sql command is not available to the following sql command when running labels. as per help: "The merge labels should only be used to merge independent requests. Independent requests means the execution of one request does not rely on the execution result of another request in the same merged request, or one request does not use the return value of another request in the same merged request."





The return of the entire set of commands is broken down in the return so you know where the error occurred but does not require that all sql command are run synchronously.



if you are doing a set of updates and there is referential integrity, then yes, you *may* need to run synchronously. however, if it is a set of just selects then running synchronously does not really need to happen.



so it could be a great enhancement for selects - would require that all the sql be looked at by PS webapi to see whether it is all selects. maybe have an option for updates (non key updates would *probably* be fine), but that would be a whole other thing.

  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 28 May 2024 16:25 PM UTC
Hi Mike;

I can certainly envisage scenarios where Asynchronous would be OK. I would suggest though that either you or KiranKumar open an enhancement support ticket for this feature so that we can a) get Engineering's clear feedback on the current mechanism and b) their feedback on the enhancement "viability" and "impact" itself. Just my $0,02. HTH

Regards .. Chris
  1. Helpful
Kirankumar Chavadi
  1. Kirankumar Chavadi
  2. Thursday, 30 May 2024 09:09 AM UTC
Thank you Mike and Chris for your inputs.

we will create an enhancement ticket with Appeon.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.