intimenordic
asked on
Query returns System.OutOfMemoryException
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:
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.Val ue
Group 2 = Fields!LABORLEVELNAME1.Val ue & Fields!LABORLEVELNAME2.Val ue
Thanks,
Bjorn
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
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.Val
Group 2 = Fields!LABORLEVELNAME1.Val
Thanks,
Bjorn
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
could you move some of the reporting services logic to a stored procedure, then feed a smaller set of results to reporting services?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks,
Bjorn
ASKER
Query completes in 54 seconds from Enterprise Manager, but report still fails with System.OutOfMemoryExceptio
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