What order does crystal read tables.

[MrBillSC]

How does Crystal Reports determine the order that it reads its tables and the order it processes the select statements in the select formula.

Does it matter what order you add the tables to the report? Often when I add lines to the report select formula I place them where it seems most logical (using the formula editor). Crystal then moves them around after I save my changes? Can anybody explain what's going on behind the scenes?

Thanks to all of you for your invaluable help.

MrBill

 

[Turkbear]

Hi,
In general, tables link from left to right ( as seen in the Linking Expert)

As to how the tables are actually read, it is dependent on the Database used and how it handles the Sql it is passed..
Use the Show Sql item under the Database menu to see what is actually being sent to the database..how it is handled there is very database specific and depends on lots of factors( indexes, rows returned, etc)

I've never used the Formula editor to add tables, so could you explain further about the
'Crystal then moves them around after I save my changes' statement.

 

[MrBillSC]

TurkBear,

Thanks for your response. My reference to the select formula is not meant to be about adding tables, but about selecting records.

One would want the select criteria applied as soon as a record is read, which could avoid Crystal reading other unnecessary records from other tables.

For example, suppose you have an Employee table, a Position table and a Benefits table with the Position and Benefits tables linking to the Employee table by EmplId. You want to report employees whose positions are in a certain SalaryGroup. You want to report only Medical, Dental, Vision, and Long Term Care benefits. It would be more efficient if Crystal read the Position table and accepted or rejected the employee before reading the Benefits table with its multiple records per employee.

Is there anyway to be sure Crystal is linking to tables in the most efficient way?

Thanks again to all.

MrBill

 

[IdoMillet]

This is typically controlled by the query optimizer of the DBMS. In most cases, the optimizer does a very good job, but in extereme cases, you can take advantage of including "hints" in the SQL to nudge the optimizer in certain directions.

The order of the tables in the FROM clause may also have an impact.

This is an advanced topic that is better pursued by searching the web for "query optimizer" and "hints".

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Turkbear]

Hi, In your example,
suppose you have an Employee table, a Position table and a Benefits table with the Position and Benefits tables linking to the Employee table by EmplId.

you should use the Employee Table as the 'driving' ( or leftmost ) table [assuming that is where the SalaryGroup is indicated, otherwise use the Position table] and link it to both the other 2 tables..that way, only the EMPLIDs that are in the Salary Group specified in the record selection formula are passed to the other tables.

In other words, use the table that will be 'reduced' the most by the selection critera as the driving table in the query. ( altho', as was pointed out by Ido, the Database should be able ( if properly set up) to determine the most effective execution plan)