Out of Memory when using DBQuery

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
User avatar
migro
Macro Veteran
Posts: 151
Joined: Thu Nov 06, 2003 5:23 pm
Location: Germany
Contact:

Out of Memory when using DBQuery

Post by migro » Tue Dec 18, 2018 12:52 am

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.
regards
migro

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Out of Memory when using DBQuery

Post by Marcus Tettmar » Wed Dec 19, 2018 10:04 pm

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.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

User avatar
migro
Macro Veteran
Posts: 151
Joined: Thu Nov 06, 2003 5:23 pm
Location: Germany
Contact:

Re: Out of Memory when using DBQuery

Post by migro » Wed Dec 19, 2018 11:49 pm

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.
regards
migro

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Out of Memory when using DBQuery

Post by Marcus Tettmar » Thu Dec 20, 2018 3:09 pm

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.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

User avatar
JRL
Automation Wizard
Posts: 3497
Joined: Mon Jan 10, 2005 6:22 pm
Location: Iowa

Re: Out of Memory when using DBQuery

Post by JRL » Thu Dec 20, 2018 8:08 pm

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.

User avatar
migro
Macro Veteran
Posts: 151
Joined: Thu Nov 06, 2003 5:23 pm
Location: Germany
Contact:

Re: Out of Memory when using DBQuery

Post by migro » Thu Dec 20, 2018 11:46 pm

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?
regards
migro

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Out of Memory when using DBQuery

Post by Marcus Tettmar » Fri Dec 21, 2018 7:54 am

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.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

User avatar
migro
Macro Veteran
Posts: 151
Joined: Thu Nov 06, 2003 5:23 pm
Location: Germany
Contact:

Re: Out of Memory when using DBQuery

Post by migro » Fri Dec 21, 2018 1:24 pm

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.
regards
migro

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts