Filters 1

[dh42891]

I've applying as many as five or six selection formulas using the select expert in CR 8.5. I have two linked databases pulling three fields and summing two of them. Before the selection criteria is applied I think there are about 7400 records. The number of selected records changes but is usually around 25. But, it takes about 4 or 5 minutes for Crystal to return those few records. I'm new at this so I'm not sure if that's a long time. It seems like it is to me, but maybe not for narrowing the record selection down so much. I don't know the server specifications but the machine I use is P4 2.66ghz with 256 M RAM. Is there a proper method to narrowing record selections down? Is the selection expert the best way or should I try to use SQL somewhere else? Any advice would be great. Thanks,

dylan

 

[fredp1]

Hi Dylan,
Which db are you using?

One appoach is to try and eliminate the client pc as a bottleneck. To minismise data send to the cleint, the SQL needs to be performed on the server.
Compare the SQL genereated vs the selection formulae. If the SQL WHERE clause is missing fields compared to the selection formulae, then CR is not pushing the processing to the server. This happens is you are using CR functions that the DB doesn't know about, so it will process part of the selection locally.

Make sure "use indexes Or Sever for speed' is also enabled in the report options box.

try posting the SQL clause and the selection logic for possible optimisation.

Fred

 

[dh42891]

The database, as I understand, is Pervasive SQL. Where do I look at the SQL clause? Thanks,

dylan

 

[fredp1]

Have a look at Database.. Show SQL Query.

Just cut and paste that code as well as your record selection formulae. Report.. Edit Selection Formulae... Record. If you have a Group selection formulae paste that as well.
To check is you are using the Indexs on the server, File... Report Options. make sure that "Use Indexes or Server for Speed" is ticked.

Fred

 

[dh42891]

From SQL Query:

SELECT
Members."OBJ_ObjectCode", Members."MBR_Code", Members."CategoryCode",
Reservations."PickUpDate", Reservations."NumPersons", Reservations."Ammount"
FROM
"DataWarehouse"."Members" Members INNER JOIN "DataWarehouse"."Reservations" Reservations ON
Members."MBR_MemberId" = Reservations."MBR_MemberId"
WHERE
Members."MBR_Code" <> '' AND
Reservations."NumPersons" > 0 AND
Members."OBJ_ObjectCode" = 'P' AND
(Members."CategoryCode" <> 'THO' AND
Members."CategoryCode" <> 'ACC') AND
Reservations."PickUpDate" >= {d '2004-07-13'}
ORDER BY
Reservations."PickUpDate" ASC

From Report Selection:

{Members.MBR_Code} <> "" and
{Reservations.NumPersons} > 0 and
{Members.OBJ_ObjectCode} = "P" and
not ({Members.CategoryCode} in ["ACC", "THO"]) and
{Reservations.PickUpDate} >= CurrentDate



CR seems to be pushing the selection clauses to the server, and "Use Indexes or Server for Speed" is checked. For one of the slow reports the perform Grouping on server wasn't checked. Checking that seemed to speed it up some. I'm thinking that mayber our server was updating at the time I was trying to access it. Thanks for the help, I'm learning loads every day.

dylan

 

[fredp1]

Good stuff,
as you said, the selection is pushed to the server. So the bottle neck in this case seems to be the server.
I've had some consultants creating reports that they wrote with selection statements that got executed locally.
Its all to easy to do.
Its good to be aware of it, because there are many techniques you'll need to use to help push the selection to the server.

Fred