server sorting and grouping

[CoSpringsGuy]

In report options i have changed the option for grouping to be performed on the database server. When I run the report and look at report statistics it tells me the grouping and sorting was not done on the server. Is that something that needs to be setup on the server? Im going to look through it see if I can find an option somewhere but thought I would ask someone who already may know..

Crystal 2008
Intersystems Cache database

Thanks in advance

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

I'm guessing you might be grouping or sorting on a formula field that wouldn't be available at the server level.

-LB

 

[CoSpringsGuy]

No it is a DB field ... date field straight out of database

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

Grouping per day? Or some other option?

-LB

 

[CoSpringsGuy]

I am currently reading through a thread you started years ago about report performance.. I read it a while back but going through it again. You and synapsevampire (and others) covered server side grouping in good detail. At the time, performance wasnt a huge issue for me but database size has increased dramatically along with increase requirements for reporting and BI. So I am revisiting. Plan on lots of questions from me about SQL vs formulas lol.
I promise to do my research from other posts you have done in that regards first...(any links to those references would be appreciated)

ok back to this topic..
I set up a VERY basic report to benchmark the difference with server side grouping.
there are two groups both on the same field
it is a date field first grouped by month then grouped again on week. There is a formula in the detail section but I dont believe it would effect performance but maybe it would. very basic formula

if {Call_Logging_Audit.Call_Type} in ["IN"] then {Call_Logging_Audit.Line_For_Call}

then summarized with a DISTINCT COUNT

I just got off the server to see if there was a setting to allow server side grouping and didnt see anything.

Any thoughts are appreciated....



_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

Try suppressing the detail section and then go to report->performance info->processing and see if the grouping is then performed on the server. I think the detail section has to be suppressed for the grouping to occur on the server.

If you are trying to speed up reports, then grouping on the server isn't a requirement to do this--what you do want to achieve is that the entire record selection is done on the server (it passes to the SQL query).

I work with a very large database, and I have found that using a command as the sole datasource and adding criteria to the From clauses (not to the Where clause) can dramatically speed up reports. You might want to try that, like this:

select table1.field1, table2.field1
from table1
left outer join table2 on
table1.key=table2.key and
table2.field1='ABC'

This technique also allows you to make selections on a left-joined table (table2 above) without losing records from the first table (table1).

-LB

 

[CoSpringsGuy]

details have been suprressed .. the server side grouping has me a bit perplexed but you are the second person that has told me that may not have a huge effect on performance so I am gong to leave that alone for now and focus more on creating commands and passing info to sql.

I use that technique (command instead of table) quite frequently but normally for a different reason than performance.

I do have the ability to create views on the database as well but sometimes my problem is they way our software developer created the original structure.

Thanks for your help! I may have more specific questions either here or a new thread, later if you have some time.

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection