CODEWITHSUNDEEP
sqlsql-server
kousha
kousha

Reputation: 782

out of memory sql execution

I have the following script:

SELECT 
    DEPT.F03 AS F03, DEPT.F238 AS F238, SDP.F04 AS F04, SDP.F1022 AS F1022, 
    CAT.F17 AS F17, CAT.F1023 AS F1023, CAT.F1946 AS F1946 
FROM 
    DEPT_TAB DEPT 
LEFT OUTER JOIN 
    SDP_TAB SDP ON SDP.F03 = DEPT.F03, 
CAT_TAB CAT 
ORDER BY 
    DEPT.F03

The tables are huge, when I execute the script in SQL Server directly it takes around 4 min to execute, but when I run it in the third party program (SMS LOC based on Delphi) it gives me the error 

<msg> out of memory</msg> <sql> the code </sql>

Is there anyway I can lighten the script to be executed? or did anyone had the same problem and solved it somehow?

Upvotes: 1

Views: 847

Answers (3)

StingyJack
StingyJack

Reputation: 19489

Consider adding paging to the user edit screen and the underlying data call. The point being you dont need to see all the rows at one time, but they are available to the user upon request.

This will alleviate much of your performance problem.

Upvotes: 1

Techie Joe
Techie Joe

Reputation: 867

I had a project where I had to add over 7 million individual lines of T-SQL code via batch (couldn't figure out how to programatically leverage the new SEQUENCE command). The problem was that there was limited amount of memory available on my VM (I was allocated the max amount of memory for this VM). Because of the large amount lines of T-SQL code I had to first test how many lines it could take before the server crashed. For whatever reason, SQL (2012) doesn't release the memory it uses for large batch jobs such as mine (we're talking around 12 GB of memory) so I had to reboot the server every million or so lines. This is what you may have to do if resources are limited for your project.

Upvotes: 0

deroby
deroby

Reputation: 6002

I remember having had to resort to the ROBUST PLAN query hint once on a query where the query-optimizer kind of lost track and tried to work it out in a way that the hardware couldn't handle.

=> http://technet.microsoft.com/en-us/library/ms181714.aspx

But I'm not sure I understand why it would work for one 'technology' and not another. Then again, the error message might not be from SQL but rather from the 3rd-party program that gathers the output and does so in a 'less than ideal' way.

Upvotes: 3

Related Questions