insufficient memory error

Question

insufficient memory error

SSC-Addicted

Points: 454

March 15, 2004 at 3:13 am

I have recently wrote a XML stored procedures that has about 115 UNIONS to return about 450 different fields. I have tried to execute this procedure, and I am getting this error message - 'There is insufficient system memory to run this query'. I tried changing the 'min memory query' configuration option, but this did not work. Any help to alleviate this. I really cannot write the stored procedure because it has to be written this way.

Patrick Quinn

TRMD Database Administrator

-------------------------

Patrick Quinn

TRMS Database Administrator

Anteon Corporation

09641-83-7722, DSN 475-7722

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Wesley Brown SSChampion Points: 12045 More actions · Answer 1

If you have to have 115 unions then you have other problems than memory issues. You need to break the query up into pieces try subselects or dumping sections to temparary tables then joining a few of those together to get your results. I have never seen more than 20 or so unions or joins work worth a damn. Most of the time you get a max paralleism error before you run out of memory. I'm dead serious, if you have 115 unions I don't think all the memory in the world will run that query. There are limits to what any RDBMS will handle.

Wes

Peter Evans Ten Centuries Points: 1187 More actions · Answer 2

Try building your edge table in tempdb instead of using all the unions. Also, maybe a call to an XSLT in the template or in the final xml result might be useful in this case instead of having the server do all this work. Just a suggestion.

You might want to try the approach of keeping it simple when returning the xml from the SQL Server and letting the XSLT tranform do the complicated work.

This way either the IIS or the UA client is doing all the work.

ALSO, it sounds like your XML that is stored in the DB is highly dependent on document order and using sql 2K which isn't as effecient at maintaining document order. So, you might want to try getting in on the YUKON beta program. So that you can use the native XML type and it indexes that help manage document ordered data.

Peter Evans (__PETER Peter_)

powerdude SSC-Addicted Points: 454 More actions · Answer 3

I was given a mandate to return info about certain table field characteristics as well as the data in a certain XML format. In order to accomplish that, I needed to have one select for each table field since the fields were in different tables. Using a temp table, was not an option because the where clause was different for each table field, so I had to pull each table field distinctive. Anyways, I explained the offsets of what I was encountering, we decided to return the info via stored procedure in a number of recordsets. Much faster.

Patrick Quinn

TRMS Database Administrator

Peter Evans Ten Centuries Points: 1187 More actions · Answer 4

Can I infer that you aren't using XML via the FOR XML clause for the task now and using another way to get it to XML?

I ask because I ran into this kind of problem before when trying to get the XML into a natural document order usable for conversion to that you XHTML. The problem wasn't the memory, but just the hit at the server.

Conceptually FOR XML features are nice, but they still want relational data in SQL 2K. However document management data really isn't orderless data there tends to be document order needs no matter what.

Peter Evans (__PETER Peter_)