Take 4 hours to generate 1 report ??

[catbebe]

Hi,

I am new to Crystal Report, using ver9. Connectivity via ODBC to AS400/DB2. Sometimes, it take me few hours to generate a Sales Report. However, the result is correct all the way. The "Perform grouping on server" tab is turned on.

Question: Is it a normal nature or I miss out something to enable the data to be generated and grouped faster?

Please help me.

Million thanks in advanced.

 

[dgillz]

There could be many other things making your report take so long, but I will address the one you brought up. If "perform groupong on server" is turned on, make sure none of the following is true about your report:

It is not a drill down report
The details section is not displayed
You have no database fields in your report, page or group headers or footers.

If you are ok on the above points post back and we will explore other areas affecting report performance.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

The key here is to check the Database->Show SQL Query to see that the selection criteria is getting passed to the database.

Keep in mind that with CR 9 you can use real SQL as your data source, so just create the SQL yourself.

-k

 

[catbebe]

The report isn't drill down, hidden details and only show groups in specify orders.

The SQL statement listed below:
SELECT "SAH05"."ZMCOMP", "SAH05"."BTLTYP", "SAH05"."DMITCL","SAH05"."BTITNO", "SAH05"."BTSLDQ", "SAH05"."BTCONV", "SAH05"."BTSLDA", "SAH05"."BTSLDD", "SAH05"."JSYEAR", "SAH05"."JSPERD", "ARM01"."GMCTYP", "INM01"."DMITDS"
FROM ("S65445BB"."ECLFLIB"."SAH05" "SAH05" INNER JOIN "S65445BB"."ECLFLIB"."ARM01" "ARM01" ON ("SAH05"."GMCUST"="ARM01"."GMCUST&quot AND ("SAH05"."ZMCOMP"="ARM01"."ZMCOMP&quot) INNER JOIN "S65445BB"."ECLFLIB"."INM01" "INM01" ON "SAH05"."BTITNO"="INM01"."DMITNO"
WHERE (&quot;SAH05&quot;.&quot;JSPERD&quot;>=1 AND &quot;SAH05&quot;.&quot;JSPERD&quot;<=12) AND &quot;SAH05&quot;.&quot;JSYEAR&quot;=2003 AND (&quot;SAH05&quot;.&quot;DMITCL&quot;='F2' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='G3' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='HD1' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='M1' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='M2' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='M3' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='M6' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='M7' OR &quot;SAH05&quot;.&quot;DMITCL&quot;='M8') AND &quot;SAH05&quot;.&quot;BTLTYP&quot;='R' AND &quot;SAH05&quot;.&quot;ZMCOMP&quot;='50'

I know I am poor in writting SQL statement. Hope you can give me some idea on how to edit it, if possible.

Thank you.

 

[synapsevampire]

This SQL does not have a group by in it, so no aggregate functions are being performed on the server.

As with real SQL, for CR to perform grouping on the server, you must have all non-aggregate fields in groups.

-k

 

[lbass]

I believe that your use of &quot;specified order&quot; for your grouping prevents the &quot;perform grouping on server.&quot; Just as a test, you might try using &quot;ascending order&quot; to see what difference it makes.

I'm guessing this isn't the entire problem, however. Are you using multiple running totals as summaries within your report? These are what slow my reports the most. Depending on the running total, they can force client-side processing.

-LB

 

[catbebe]

Yes, this report is group by &quot;specified orders&quot;. I was just tried group by &quot;Ascending Order&quot;, resulted the same. There is no running total.

I referred to the samples from &quot;general business&quot; folder in Crytal Report program file. Seldom have SQL statement including &quot;group by&quot; procedure.

Sir Synapsevampire, I would like to try but I am not clear on how to define &quot;aggregate&quot; and &quot;non-aggregate&quot;. Can you help me (like: send me a regardless link for the defination)?

Thank you.

 

[pelajhia]

Several things I noticed:
You may wish to rewrite this and try it outside of Crystal to get the query itself working properly.
Something more like this would be much more readable, at least:
SELECT &quot;SAH05&quot;.&quot;ZMCOMP&quot;, &quot;SAH05&quot;.&quot;BTLTYP&quot;, &quot;SAH05&quot;.&quot;DMITCL&quot;,&quot;SAH05&quot;.&quot;BTITNO&quot;,
&quot;SAH05&quot;.&quot;BTSLDQ&quot;, &quot;SAH05&quot;.&quot;BTCONV&quot;, &quot;SAH05&quot;.&quot;BTSLDA&quot;, &quot;SAH05&quot;.&quot;BTSLDD&quot;,
&quot;SAH05&quot;.&quot;JSYEAR&quot;, &quot;SAH05&quot;.&quot;JSPERD&quot;,
&quot;ARM01&quot;.&quot;GMCTYP&quot;,
&quot;INM01&quot;.&quot;DMITDS&quot;
FROM (&quot;S65445BB&quot;.&quot;ECLFLIB&quot;.&quot;SAH05&quot; &quot;SAH05&quot; INNER JOIN
&quot;S65445BB&quot;.&quot;ECLFLIB&quot;.&quot;ARM01&quot; &quot;ARM01&quot;
ON (&quot;SAH05&quot;.&quot;GMCUST&quot;=&quot;ARM01&quot;.&quot;GMCUST&quot
AND (&quot;SAH05&quot;.&quot;ZMCOMP&quot;=&quot;ARM01&quot;.&quot;ZMCOMP&quot)
INNER JOIN &quot;S65445BB&quot;.&quot;ECLFLIB&quot;.&quot;INM01&quot; &quot;INM01&quot;
ON &quot;SAH05&quot;.&quot;BTITNO&quot;=&quot;INM01&quot;.&quot;DMITNO&quot;
WHERE
(&quot;SAH05&quot;.&quot;JSPERD&quot; between 1 AND 12)
AND &quot;SAH05&quot;.&quot;JSYEAR&quot;=2003
AND (&quot;SAH05&quot;.&quot;DMITCL&quot; in ('F2', 'G3','HD1', 'M1','M2','M3','M6','M7','M8')
AND &quot;SAH05&quot;.&quot;BTLTYP&quot;='R' AND &quot;SAH05&quot;.&quot;ZMCOMP&quot;='50'


(outside of crystal you'll have to loose all the double quotes).

Also, for some dbs, the order of the where clause conditions could have impact on speed. For example, you might try putting the most inclusive condition at the bottom of your list of conditions.
Eg, if &quot;SAH05&quot;.&quot;ZMCOMP&quot;='50' will get a 'yes' on only a few records, put this first. If AND &quot;SAH05&quot;.&quot;JSYEAR&quot;=2003 will return many thousands of records, put this last. If your not sure, mix it up a few different ways.

Finally, the biggest factor for poor speed is usually the joins. Yours don't look too complicated, but I'm not too familiar with that inner join - on syntax. Can you get with a dba to help you check the joins?

 

[pelajhia]

PS - think I skipped a parens in this part; try it this way:

(&quot;SAH05&quot;.&quot;JSPERD&quot; between 1 AND 12)
AND (&quot;SAH05&quot;.&quot;DMITCL&quot; in ('F2', 'G3','HD1', 'M1','M2','M3','M6','M7','M8'))
AND &quot;SAH05&quot;.&quot;BTLTYP&quot;='R' AND &quot;SAH05&quot;.&quot;ZMCOMP&quot;='50'
AND &quot;SAH05&quot;.&quot;JSYEAR&quot;=2003

 

[catbebe]

I am trying to edir the Statement but in Crystal Report version 9 Professional, I can only find the SQL statement by clicking Database->Show SQL. I can not see any key the enable me to edit the statement.
Any Idea?

 

[pelajhia]

I was thinking that you have this statement in a 'command', which is sort of like a stored procedure stored in the crystal report instead of in the database.
This is something that is new to 9, so you should take advantage of it, since you are having processing problems.

You will have to create a new connection to the datasource and just above the list of tables you should see an 'add command' icon. Highlight and click the > button to use.
You can copy and paste your statement above right into the command edit screen.

One caveat, you will have to replace all your fields in the report with the new command fields.

 

[synapsevampire]

lbass is correct, I skipped over the specified order statement.

So you're forcing Crystal to group the data, and if there's a lot of it, it'll take a long time.

Go ahead and build the SQL with the grouping in it as pelajhia referenced (I'd consider using a View or Stored Procedure), and then force the specified order within the report.

It should be fast and hit the requirements.

-k