making a 46min report run faster

[eoggb]

I have a report that is pulling 10,046,018 records off the server. I am also running the report off the server not as a local copy on my computer. This report is pulling all our last year sales and current year sales by items. YEH, does anyone have a suggestion as to making this report run in lesser time frame. In my report I have so far:

-->a whilereading statment in the select statment for the selected date pram.

-->i placed all the date and sales amount formulas in the report header

--> all my subreports are on demand

but the reports still takes 46mins to run, any suggestions?

G

 

[lbass]

Check SynapseVampire's FAQ on optimizing SQL statements: FAQ767-3825. You need to compare your record selection formula in report->selection formula->record with the SQL statement in database->show SQL query. If your report is optimized, all criteria in your formula should appear in the SQL query. For further help you should paste your record selection formula into this thread, along with a copy of the SQL query. You should also identify your database, connectivity, and CR version.

-LB

 

[dgillz]

What version of crystal, what database and what connectivity are you using?

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

http://www.gainfocus.biz

 

[eoggb]

i am running version 10, odbc connection and a sequal database

 

[Turkbear]

Hi,
By sequal do you mean SqlServer?
If so, what version?
SQL is a language, not a database ( altho' many use it as a shortname for SqlServer)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Turk: Sequal is a databse

Giselleb: I would guess that you are pulling back way to many rows for the requirements. The first thing I ask of any requirements that is going to return more than 10,000 rows is WHO is looking at all of these rows?

Try to create a more intelligent datasource (view or SP)for the report which already performs any aggregates. At any rate, cut down that returning of 10 million rows stuff, the database is MUCH faster at doing whatever functionality is required against those 10 million rows.

If your user's are saying that they need to look at 10 million rows then they are clearly insane, just print a report with the word SNAFU over and over, they'll never know the difference

-k

 

[Turkbear]

Hi,
OH...Odd choice for a name..( lots of folks pronounce SQL that way)..

Could not find any reference to that product with Google..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[eoggb]

i will be using all the row, but the user will not be viewing these rows just the summary of the rows( sales), so the database need to pull these rows. as it is a comparision of sales by item for the this year and last year. the number of stores are 115 stores. i read in a crystal book about placing certin formulas at the top of the report header with will evaluate first (ie. sales date with condition to pull current date and last year date). If you can suggest a better way let me know.

if dateserial(year({?Date})-1, month({?Date}), day({?Date})) =
cdate({Lwi_Periods.RealDate}) and
{DailySales_Summary_vw.YearNum} = {?CurrentFiscalYear}-1
then
if ({MMS_Store_Region_vw.Region}="USA")then
{DailySales_Summary_vw.Sales}* {?Exch Rate}
else
{DailySales_Summary_vw.Sales}
else 0

this is my formula to pull for last year's date. when i runn the report without this to calcuate based on the Date pram it takes no more than 1 min.

So my question here is why is this formula slowing it down.

my select statment is :
cdate({DailySales_Summary_vw.SalesDate}) in dateserial(year({?Date})-1, month({?Date}), day({?Date})) to {?Date};

 

[Storyteller]

Hello giselleb,
If it is only the summary the Users will look at try this:
1. Have the summary field and the group name field in the same section.
2. Hide or Supress the Details section
3. Perform Grouping on the Server.
- Database on the menu select Perform Grouping on Server

Please post your SQL statement (Database on the menu select Show SQL Query) so that we can see if there is any additional tweaking that is required.

Regards,
Michael

 

[synapsevampire]

As Michael pionted out, this "but the user will not be viewing these rows just the summary of the rows( sales), so the database need to pull these rows" is incorrect.

You need to only return the summaries, and should have that in mind.

Either you misunderstood the Crystal book, or the Crystal book would be better served as a paperweight. I think that they were speaking of generic formulas that will be processed only once, but the problem with MOST Crystal books is that someone who knows Crystal well, but doesn't understand the advantages to having the database do all of the heavy lifting wrote them, so they address things in a less efficient manner.

I would attempt to create a more intelligent datasource on the database itself, your dba should be able to assist you. If not, you may be able to use Crystal's grouping to assure that the report is grouped and the aggregates are performed on the server, but it depends on what is needed in the report, which obviously we have no idea of.

Successful posts tend to include:

Crystal version (done)
database/connectivity (done)
Example data (show what's in the recordset being returned)
Expect output (show what you need as a result of the recordset)

-k