Out of Memory when using DBQuery

[migro]

Hi!

I want to use DBQuery to create a recordset for further processing from a MS-SQL table with 5 columns and around 650,000 up to 1.500.000 rows, but it runs each time out of memory. Running the query will allocate around 1.3 up to 1.8 GB of Memory until it stops executing. With other processes running on the machine it exceeds the 3.6 GB for 32bit applications.
When I export the data from the beneeth query into a CSV file it requires only 40MB of HD space.
Im using MS 14.5.2 and running the script on a Windows 8.1 x64 with 8GB of RAM.

Here is the code I'm using:

Let>connstr=Driver={SQL Server}; Server=%strSrvName%; Database=%strDatabase%;
DBConnect>connstr,dbH
Let>SQL=SELECT * FROM ImgTmp
DBQuery>dbH,SQL,rsImage,numRecs,numFields
# ...Some processing here...
DBClose>dbH

Any suggestions or will there possibly be a 64bit version of MacroSheduler in the future available?
Thanks in advance for your answers.

[Marcus Tettmar]

I think you are going to need to break that SQL query down into smaller chunks. E.g. use the LIMIT and OFFSET keywords to break the query into a number of smaller queries, and loop it.

[migro]

Thanks Marcus for the hint.

Can you please explain that a little bit closer? I can not find the two keywords searching the MS help.
Maybe you can give me a short sample how to use them?

Thanks in advance.

[Marcus Tettmar]

You won't find Microsoft SQL Server SQL Language help in the Macro Scheduler help file.

In any case I realise these are MySQL statements. But there are equivalent ways of doing it in SQL Server:

https://stackoverflow.com/questions/213 ... sql-server

The point is you need to change your query to return LESS rows. Paginate it. So make a loop which returns 1000 at a time. Whichever flavour of SQL you use will have ways of constructing a query to do that. For help with that you need to turn to your database of choice, as that's not a Macro Scheduler thing. The query you supply to Macro Scheduler in DBQuery is unknown to Macro Scheduler. Macro Scheduler is just the conduit to your database server. Only the database server will understand your SQL.

[JRL]

I don't have much time to explain today but it seems to me your problem is that you're creating "columns" X "rows" = "number of variables". (5 cols x 1.5 million rows is 7.5 million variables) In my view you need to be writing everything to disk. I like to use the SQLToCSV vbscript found HERE. After writing data to the CSV file, use SQLToCSV again for subsequent queries of the originally generated CSV file. If you've never queried a text file searching this forum or microsoft for "schema" might be a starting point for you.

[migro]

Hi JRL,

thanks for your advice. I'm using SqlToCsv already in other cases to provide a C++ routine with the data which need to be processed. In this actual Script I thought I can do all with Macro Scheduler functions without using VB or an external executable.

@Marcus: Sorry was a misunderstanding by me. Thought you ment with "LIMIT and OFFSET keywords" MacroScheduler parameters which can be used with DBQuery. But again my question from the first Post: Is there a 64 Bit version planned for Macro Scheduler?

[Marcus Tettmar]

Hi,

We have no plans to introduce a 64 bit version. Some years ago now we ported the main script engine code to 64 bit, so we have the ability in production but after exhaustive research we decided there was no real benefit vs the extra cost and hassle associated with maintaining double the number of builds, double the size of installation etc. A 64 bit version of Macro Scheduler will offer no benefits to the user.

[migro]

Hi Marcus,

thanks for the information and sorry to hear that.
> A 64 bit version of Macro Scheduler will offer no benefits to the user.
One of the advantages would be the larger address space. Regardless, Macro scheduler is a great tool that we do not want to miss.