-2

I'm using Delphi 6 for developing windows application and have a stored procedure which returns around 17 million rows. It takes 3 to 4 minutes while returning data in SQL Server Management Studio.

And, I'm getting an "out of memory" exception while I'm trying to access the result dataset. I'm thinking that the sp.execute might to executed fully. Do I need to follow any steps to fix this or shall I use sleep() to fix this issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
test12345
  • 367
  • 1
  • 21
  • 3
    Have you considered using a tool that's less than 20 years old? – David Heffernan Jan 12 '23 at 20:05
  • Which tool do i need to use? – test12345 Jan 12 '23 at 20:19
  • Delphi 6 is ancient, why not use a modern tool – David Heffernan Jan 12 '23 at 20:22
  • 3
    Why would you load 17 million records to memory? Or that's what I assume you meant with *crore*, which by the way is not understood globally at all... – James Z Jan 12 '23 at 20:25
  • 2
    If your app needs to store a huge amount of records in memory, you obviously need a 64-bit application. Delphi 6 can only make 32-bit apps. – Andreas Rejbrand Jan 12 '23 at 20:50
  • 1
    @test12345 use a current version of Delphi! – Delphi Coder Jan 13 '23 at 00:30
  • 1
    Delphi 6 was released back around 1991. It produces only 32-bit applications. A 32-bit application can't allocate enough memory to hold 17M records from a database. The first thing to do is stop returning that much data. It's highly unlikely you need all of those records - a user clearly can't scroll through that many rows, and if you need to calculate something you can do that in the stored procedure and return only those results. And no, `Sleep()` isn't going to change anything. Out of memory is out of memory. – Ken White Jan 13 '23 at 01:36
  • 1
    @KenWhite, Delphi 6 was released 2001. Nevertheless, a shift to a 64 bit compiler is a must in this case. – LU RD Jan 13 '23 at 06:37
  • This user [rarily accepts an answer](https://stackoverflow.com/users/4623435/test12345?tab=questions). – AmigoJack Jan 13 '23 at 10:40
  • @AmigoJack - well observed – Rohit Gupta Jan 13 '23 at 12:10
  • @LURD:: Of course you're right. I mistakenly calculated from 1986 instead of 1996 for Delphi 1's release. Must have been tired. Thanks for the correction. D6 is still ancient today anyway. – Ken White Jan 13 '23 at 14:08
  • Thanks all, I'm trying to use pagination concept as the application can not be migrated to Delphi version. – test12345 Jan 13 '23 at 20:05

1 Answers1

0
  • Delphi 6 can only compile 32 bit executables.
  • 32 bit executables running on a 32 bit Windows have a memory limit of 2 GiB. This can be extended to 3 GiB with a hardware boot switch.
  • 32 bit executables running on a 64 bit Windows have the same memory limit of 2 GiB. Using the "large address aware" flag they can at max address 4 GiB of memory.
  • 32 bit Windows executables emulated via WINE under Linux or Unix should not be able to overcome this either, because 32 bit can at max store the number 4,294,967,295 = 2³² - 1, so the logical limit is 4 GiB in any possible way.
  • Wanting 17 million datasets on currently 1,9 GiB of memory means that 1,9 * 1024 * 1024 * 1024 = 2,040,109,465 bytes divided by 17,000,000 gives a mean of just 120 bytes per dataset. I can hardly imagine that is enough. And it would even only be the gross load, but memory for variables are still needed. Even if you manage to put that into large arrays you'd still need plenty of overhead memory for variables.

Your software design is wrong. As James Z and Ken White already pointed out: there can't be a scenario where you need all those dataset at once, much less the user to view them all at once. I feel sorry for the poor souls that yet had to use that software - who knows what else is misconcepted there. The memory consumption should remain at sane levels.

AmigoJack
  • 5,234
  • 1
  • 15
  • 31