• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • How to cancel a long running query in PowerServer CloudApp

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

Resolved How to cancel a long running query in PowerServer CloudApp

  1. How-to
0
Votes
Undo
  1. Kari Paukku
    Kari Paukku
  2. PowerServer
  3. Tuesday, 19 September 2023 18:43 PM UTC

Hi,

we have in our PB application, when running in C/S mode, the possibility to cancel a query returning large number of rows by issuing DBCancel(). In C/S mode it works fine.

But in PS mode, it doesn't appear to do anything.

Is there a way to cancel this kind of query when running in PS mode?

Thanks,

Kari

 

  1. PowerServer
  2. Query
PowerServer and calling overloaded database funct...
How to start PowerServer application without admin...
Responses (3)
Kari Paukku
Kari Paukku Accepted Answer Pending Moderation
  1. Wednesday, 20 September 2023 02:35 AM UTC
  2. PowerServer
  3. # 1

Hi,

solved this issue by preventing it from happening in the first place. In our case these long-running queries were search-queries. 

We use Oracle, so we added to the search SQL simply "FETCH FIRST 5000 ROWS ONLY".

If that limit is reached, there is a message "Try to be more specific".

Kari

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 20 September 2023 03:51 AM UTC
You could have also used the dw menu "rows > retrieve as needed".

The only problem I see is that your result set looses significance when you want to ORDER the results of your query, as the list is incomplete. If you'd order by a field like for example "price", you would not know if the lowest or highest price is being displayed.

  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 20 September 2023 03:53 AM UTC
RetrieveAsNeeded not supported for PowerServer.
  1. Helpful 1
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 20 September 2023 08:34 AM UTC
I wasn't aware this was powerserver, makes sense!
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 20 September 2023 03:45 AM UTC
  2. PowerServer
  3. # 2

A technique I've seen used for queries where it may either take a long time or a LOT of rows may be returned is to first take the Select statement and replace the list of retrieval columns (everything between "SELECT" and the "FROM" clause keyword) with "Count(*)". If the tables are properly indexed, this nearly always returns a count very quickly. Then the app can tell the user how many rows will be retrieved and ask if they wish to proceed. If so, then issue the actual, original query.

Comment
There are no comments made yet.
Logan Liu @Appeon
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 20 September 2023 08:25 AM UTC
  2. PowerServer
  3. # 3

Hi Kari,

The current design of PowerServer’s cloud app does not retrieve data row by row like a native PowerBuilder app. As a result, it is difficult to cancel the retrieval process once it has started.

Thanks for sharing this "FETCH FIRST 5000 ROWS ONLY" solution. 

Regards, Logan

Comment
mike S
  1. mike S
  2. Wednesday, 20 September 2023 12:04 PM UTC
SQL server / SQL Any/ ASE can also use: TOP N which is easier to add to the sql (especially at runtime)



oracle can also use:

where rownum <= N

which is documented as being faster than using FETCH FIRST and generally works better than fetch as it doesn't care if you have the same column name more than once . however, if you use an "order by" then you must use FETCH. all of this might possibly change depending on the oracle version. we use rownum method UNLESS there is an order by since it generally seems to work better.



Postgre

use FETCH FIRST or limit



other databases use limit (my sql)



  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.