CR 2008 - Report hanging, not completing.

[cantademorna]

Hi there

I am using CR 2008 and having performance issues when running a report.
There are 4 tables

tableA - 546,232 rows
tableB - 1,017,073 rows
tableC - 11,311 rows
tableD - 1,489 rows

tableC is the main table and is connected via indexes
to the other 3 tables.
The links are left outer joins since I need to gather all
relevant data.
This report actually hangs and doesn't finish when processing
a large data selection, on maller ones <250k row selections it's fine.
Over 250k rows the report hangs, locks the tables and forces the need
for a server re-boot - very messy!

I've tuned this report quite extensively, introducing stored procedures
and looking at the data sets themselves, can't really pear it down any further,
is there anything on the CMC Server control that could be adjusted?
Could the cache and idle time be of any importance?
By the way this is reading from a SQL Server 2005 database using an ODBC
connection.

Thanks

Canta

 

[lbass]

I understand you are asking a different question, but have you optimized the SQL, making sure all selection criteria are passing? Are you using "or" statements in your record selection? Have you considered using a command using union alls where the data from each table is limited in the from clause?

It might help to see your SQL query if you think there might be room for optimization.

-LB

 

[cantademorna]

SELECT "c"."abc_category", "c"."product", "c"."description",
"c"."warehouse", "c"."analysis_a", "c"."standard_cost",
"c"."purchase_key", "a"."transaction_type", "a"."movement_date",
"b"."quantity", "b"."invoice", "b"."dated", "b"."invoice_customer",
"c"."supplier", "b"."sequential_number", "c"."physical_qty", "c"."alpha",
"d"."date_entered"

FROM (("company_l"."scheme"."c" "c" LEFT OUTER JOIN
"company_l"."scheme"."d" "d" ON ("c"."warehouse"="d"."warehouse") AND
("c"."product"="d"."product")) LEFT OUTER JOIN "company_l"."scheme"."b"
"b" ON ("c"."warehouse"="b"."warehouse") AND
("c"."product"="b"."product")) LEFT OUTER JOIN "company_l"."scheme"."a"
"a" ON ("c"."warehouse"="a"."warehouse") AND ("c"."product"="a"."product")

WHERE "c"."analysis_a"='ARG' AND "c"."warehouse"='01' AND
"a"."transaction_type"='SALE' AND ("b"."dated">={ts '2008-09-01 00:00:00'}
AND "b"."dated"<{ts '2009-09-02 00:00:00'})

ORDER BY "c"."abc_category", "c"."product", "b"."invoice"



These ands have to be present here and as you can see a pretty straightforward statement, but after about 250k row reads it goes into stasis!

any thoughts - CMC server settings? (CR2008 here) this happens both from the CR developement environment and when scheduled.

Many Thanks.

-Canta