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

Resolved Datastore & millions of rows

  1. Advice
0
Votes
Undo
  1. Brage Mogstad
    Brage Mogstad
  2. PowerBuilder
  3. Thursday, 27 July 2023 08:09 AM UTC

Hello,
Anyone managed to work with "big data" in PB & Adaptive Server Anywhere?
I'm using PB 19 and 32-bit ODBC.
So far I've only been able to solve manipulation of data with either SQL statements in string & Execute immediate, or simply triggering a stored proc.
But I'm really missing that datastore & datawindow magic.
Really need those Find, Filter, Update() functions.
Did I miss some setting in the datawindow object, 64-bit ODBC setting or is this simply a size limitation of PB datastore & datawindow?

Regrettably during retrieval PB crashes - every single time.

Regards
Brage





 

  1. 32-bit
  2. 64-bit
  3. DataWindow
  4. Large File
  5. PowerBuilder
  6. DataStore
PB messing with my PATH
Deployment version format
Accepted Answer
Brage Mogstad
Brage Mogstad Accepted Answer Pending Moderation
  1. Thursday, 27 July 2023 13:03 PM UTC
  2. PowerBuilder
  3. # Permalink
Thanks, I managed to work around it and now it actually works to retrieve millions of rows from the db in PB2022 IDE(!) Steps to make this work in PB IDE: Made a 32 bit ODBC profile that connects to my default 32 bit SQL Anywhere db Made a 64-bit ODBC with unique name to use later (does not show anywhere in PB2022 IDE) Made a new template app using default 32 bit ODBC as db connection profile. Do a quick search-replace in the connectservice object - made sure to use the 64 bit profile name instead. FROM: "ConnectString='DSN=Test32bit;UID=dba;PWD=sql'" TO: "ConnectString='DSN=Test64bit;UID=dba;PWD=sql'" Deployed the app as a 64-bit debug session in PB IDE (make sure to choose 64-bit in the PB2022 IDE) I could see some GB's rise in my memory usage. Regards Brage
Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Friday, 28 July 2023 02:06 AM UTC
Thanks for sharing the solution!
  1. Helpful
There are no comments made yet.
Responses (4)
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 27 July 2023 08:20 AM UTC
  2. PowerBuilder
  3. # 1

With "million of rows" I guess you will run out of memory.

You could try:

- Deploy your application as 64-bit application. You will also need 64-bit database driver in this case.

- Deploy PB.INI with this settings:

[Data Window]

Accessibility=0

[DataStore Behavior]

UseHwnd=No

 

Comment
Brage Mogstad
  1. Brage Mogstad
  2. Thursday, 27 July 2023 10:49 AM UTC
Thanks, I'll try it out. The need comes from an actual place of "practice deployment", as a colleague managed to make update of ten s of thousands of rows go 100s of times faster with a dwo. Like from 2-3 hours to like, literally secounds. I guess its mainly due to changes on a few rows and a few columns to a large dataset in a dwo saves a lot faster to a database than some homemade update & insert SQL-statements based on a cursor. That said, I'm really impressed by the cursor performance in PB as well, sometimes its really fast. Whats the issue this time around, is at some point there's probably just too mutch data for a 32-bit application. I'll try 64-bit.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 27 July 2023 08:41 AM UTC
  2. PowerBuilder
  3. # 2

in addition to what René said: It's going to be very, very slow to retrieve all of those rows.

All retrieved data have to fit into RAM memory, so that's why PB is blowing up when using 32 bits, which is limited to around 2GB (3GB if you do some registry hack) on 32 bit. (2GB is like reserved for windows).

Comment
Load more comments
Benjamin Gaesslein
  1. Benjamin Gaesslein
  2. Tuesday, 22 August 2023 08:15 AM UTC
Of course, Miguel. Although during my (admittedly limited) tests, I haven't found a hard memory limit for a 64 bit PB app so far. I don't know if the idea of PB using "its own memory heap" really makes sense. From what I understand, heap memory is just memory that is explicitly allocated and deallocated by an application. If more memory is needed, more can be requested at any time. Unless the PBVM somehow limits that but I don't see why that should be done.

It is of course true that there's little to no control over how PB will allocate heap memory.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 22 August 2023 10:11 AM UTC
Maybe memory management has improved for 64 bit. For 32 bit, it's very easy to reach pb's heap limits. (which are not constant by the way). Sometimes you can read a file that's 400 MB, using FileReadEx() and other times it blows up reading only 250 or 300 MB.

regards
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 22 August 2023 10:13 AM UTC
"When I have some time", I'll make a small test app in 64 bits and do FileReadEx() to see where the limit lies, if there is any.

:)
  1. Helpful
There are no comments made yet.
Brage Mogstad
Brage Mogstad Accepted Answer Pending Moderation
  1. Thursday, 27 July 2023 11:46 AM UTC
  2. PowerBuilder
  3. # 3

Okay, Im into testing this, but the those SQL Anywhere 17 64 bits ODBC profiles does not pop up in Appeon PB2022 IDE. Perhaps this means Sybase 64 bits ODBC's are unsupported by Powerbuilder. A 64-bit SQL server ODBC sure won't cut it.

Regards 
Brage 

Comment
Andreas Mykonios
  1. Andreas Mykonios
  2. Thursday, 27 July 2023 12:00 PM UTC
Hi.

PB IDE is still 32-bit. So through the IDE you can connect to databases only using 32-bit drivers. What has changed in PB 2022, is that now you can debug a 64-bit application. By the way, 64-bit executable will be avle to connect to a 64-bit database deiver, including sql anywehere 64-bit odbc.

Andreas.
  1. Helpful 3
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.