Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query returns System.OutOfMemoryException

Posted on 2011-03-04
6
Medium Priority
?
594 Views
Last Modified: 2012-05-11
Hi experts,
Using SQL reporting services 2005, Creating reports with Visual Studio 2005.

I have a report which fails when running for a large period of time. (More than 6 months).
Runs fine for time periods smaller than 6 months, so I guess it comes down to number of records returned.

When run for a full year, the SQL Query returns about 1 million records and is executed in 56 seconds using SQL Management Studio.

The Query goes like this:

 
SELECT     VP_TOTALS.PERSONFULLNAME, VP_TOTALS.PERSONNUM, VP_TOTALS.LABORLEVELNAME1, VP_TOTALS.LABORLEVELNAME2, 
VP_TOTALS.LABORLEVELNAME3, VP_TOTALS.PAYCODENAME, VP_TOTALS.APPLYDATE, VP_TOTALS.TIMEINSECONDS, VP_TOTALS.PAYCODEID, VP_TOTALS.LABORLEVELDSC2, VP_TOTALS.LABORLEVELDSC1, ASSIGNPAYRULE.EXPIRATIONDTM, ASSIGNPAYRULE.PAYRULEID AS Expr1
FROM VP_TOTALS INNER JOIN MYWTKEMPLOYEE ON VP_TOTALS.EMPLOYEEID = MYWTKEMPLOYEE.EMPLOYEEID INNER JOIN
ASSIGNPAYRULE ON VP_TOTALS.EMPLOYEEID = ASSIGNPAYRULE.EMPLOYEEID
WHERE     (VP_TOTALS.APPLYDATE >= CONVERT(DATETIME, '2010-01-01 00:00:00', 102)) AND (VP_TOTALS.APPLYDATE <= CONVERT(DATETIME, '2010-12-31 00:00:00', 102)) AND (VP_TOTALS.PAYCODEID = 101 OR
                      VP_TOTALS.PAYCODEID = 102 OR
                      VP_TOTALS.PAYCODEID = 103 OR
                      VP_TOTALS.PAYCODEID = 104 OR
                      VP_TOTALS.PAYCODEID = 105 OR
                      VP_TOTALS.PAYCODEID = 106 OR
                      VP_TOTALS.PAYCODEID = 107 OR
                      VP_TOTALS.PAYCODEID = 111 OR
                      VP_TOTALS.PAYCODEID = 115 OR
                      VP_TOTALS.PAYCODEID = 116 OR
                      VP_TOTALS.PAYCODEID = 117 OR
                      VP_TOTALS.PAYCODEID = 123 OR
                      VP_TOTALS.PAYCODEID = 125 OR
                      VP_TOTALS.PAYCODEID = 127 OR
                      VP_TOTALS.PAYCODEID = 301 OR
                      VP_TOTALS.PAYCODEID = 505 OR
                      VP_TOTALS.PAYCODEID = 553 OR
                      VP_TOTALS.PAYCODEID = 554 OR
                      VP_TOTALS.PAYCODEID = 555 OR
                      VP_TOTALS.PAYCODEID = 561 OR
                      VP_TOTALS.PAYCODEID = 566 OR
                      VP_TOTALS.PAYCODEID = 502 OR
                      VP_TOTALS.PAYCODEID = 507 OR
                      VP_TOTALS.PAYCODEID = 851) AND (VP_TOTALS.LABORLEVELNAME1 = N'006' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'007' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'011' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'091' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'095' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'097' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'126' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'371') AND (MYWTKEMPLOYEE.SESSIONID = 117343) AND 
                      (ASSIGNPAYRULE.EXPIRATIONDTM = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)) AND (ASSIGNPAYRULE.PAYRULEID <> 451)
ORDER BY VP_TOTALS.LABORLEVELNAME1, VP_TOTALS.LABORLEVELNAME2, VP_TOTALS.PERSONFULLNAME, VP_TOTALS.APPLYDATE

Open in new window


I have used the WHERE clausules to limit the number of records returned.

Can anyone see a way to optimize this query so the report runs?
The report contains two groups and no details section:
Group 1 = Fields!LABORLEVELNAME1.Value
Group 2 = Fields!LABORLEVELNAME1.Value & Fields!LABORLEVELNAME2.Value

Thanks,
Bjorn
0
Comment
Question by:intimenordic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 498 total points
ID: 35035209
A little update:
SELECT VP_TOTALS.PERSONFULLNAME, VP_TOTALS.PERSONNUM, VP_TOTALS.LABORLEVELNAME1, VP_TOTALS.LABORLEVELNAME2, 
VP_TOTALS.LABORLEVELNAME3, VP_TOTALS.PAYCODENAME, VP_TOTALS.APPLYDATE, VP_TOTALS.TIMEINSECONDS, VP_TOTALS.PAYCODEID, VP_TOTALS.LABORLEVELDSC2, VP_TOTALS.LABORLEVELDSC1, ASSIGNPAYRULE.EXPIRATIONDTM, ASSIGNPAYRULE.PAYRULEID AS Expr1
FROM VP_TOTALS INNER JOIN MYWTKEMPLOYEE ON VP_TOTALS.EMPLOYEEID = MYWTKEMPLOYEE.EMPLOYEEID INNER JOIN
ASSIGNPAYRULE ON VP_TOTALS.EMPLOYEEID = ASSIGNPAYRULE.EMPLOYEEID
WHERE (VP_TOTALS.APPLYDATE >= CONVERT(DATETIME, '2010-01-01 00:00:00', 102)) AND (VP_TOTALS.APPLYDATE <= CONVERT(DATETIME, '2010-12-31 00:00:00', 102)) AND (VP_TOTALS.PAYCODEID in (101, 102, 103, 104, 105, 106, 107, 111, 115, 116, 117, 123, 125, 127, 301, 505, 553, 554, 555, 561, 566, 502, 507, 851) AND (VP_TOTALS.LABORLEVELNAME1 = N'006' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'007' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'011' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'091' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'095' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'097' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'126' OR
                      VP_TOTALS.LABORLEVELNAME1 = N'371') AND (MYWTKEMPLOYEE.SESSIONID = 117343) AND 
                      (ASSIGNPAYRULE.EXPIRATIONDTM = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)) AND (ASSIGNPAYRULE.PAYRULEID <> 451)
ORDER BY VP_TOTALS.LABORLEVELNAME1, VP_TOTALS.LABORLEVELNAME2, VP_TOTALS.PERSONFULLNAME, VP_TOTALS.APPLYDATE

Open in new window

I see the delay is from the number of records scanned and returned.
0
 

Author Comment

by:intimenordic
ID: 35035278
Thanks for getting back.

Query completes in 54 seconds from Enterprise Manager, but report still fails with System.OutOfMemoryException.

I need all records to get correct result from the report, so might have to wait until server can be upgraded to 64 bit with extended memory...

Any other ideas are welcome!

Regards,
Bjorn
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 501 total points
ID: 35035401
hi

you can try removing the ORDER BY  to free memory on execution .
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:deighton
ID: 35036677
could you move some of the reporting services logic to a stored procedure, then feed a smaller set of results to reporting services?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 501 total points
ID: 35041295
You should find the query below a tad more efficient, especially if you index it appropriately
SELECT  t.PERSONFULLNAME,
        t.PERSONNUM,
        t.LABORLEVELNAME1,
        t.LABORLEVELNAME2,
        t.LABORLEVELNAME3,
        t.PAYCODENAME,
        t.APPLYDATE,
        t.TIMEINSECONDS,
        t.PAYCODEID,
        t.LABORLEVELDSC2,
        t.LABORLEVELDSC1,
        a.EXPIRATIONDTM,
        a.PAYRULEID AS Expr1
FROM    VP_TOTALS t
        INNER JOIN MYWTKEMPLOYEE  e ON t.EMPLOYEEID = e.EMPLOYEEID
        INNER JOIN ASSIGNPAYRULE a ON t.EMPLOYEEID = a.EMPLOYEEID
WHERE   t.APPLYDATE BETWEEN CONVERT(DATETIME, '2010-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-12-31 00:00:00', 102)
        AND t.PAYCODEID IN (101, 102, 103, 104, 105, 106, 107, 111, 115, 116, 117, 123, 125, 127, 301, 505, 553, 554, 555, 561, 566, 502, 507, 851)
        AND t.LABORLEVELNAME1 IN (N'006', N'007', N'011', N'091', N'095', N'097', N'126', N'371')
        AND e.SESSIONID = 117343
        AND a.EXPIRATIONDTM = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)
        AND a.PAYRULEID <> 451
ORDER BY t.LABORLEVELNAME1,
        t.LABORLEVELNAME2,
        t.PERSONFULLNAME,
        t.APPLYDATE

Open in new window

0
 

Author Closing Comment

by:intimenordic
ID: 35067269
Thanks for all tips. The query was more effcient after your input. Didn't make it all the way, but I got a few more months out of it.

Thanks,
Bjorn
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question