Ken - this has me scratching my head - it works but why??

[Ngolem]

I have a fairly complicated report that links about 12 tables together...it is Stock catalogue listing of a large company with vendor/stock no. info.

there is a lot of data in these tables and I am handcuffed since I must use only the ability of Crystal 8.0 professional to draw the data from the linked tables...no procedures or queries allowed... there are about 23,000 records that are returned.

The report is designed to present many looks to the data by means of parameters...part of my mandate was to condense at least 10 mainframe reports into one report with many looks.

At first I was discouraged as I was building the report and adding functionality since the report was taking 3 minutes to generate the report....my target is to start printing the report in 60 secs.

There are 2 datetime values that ARE NOT keys in the data table involved. Eventually I came to a mainframe report that selected data on their basis...so I created a parameter for each (datetime-type) and inserted them into my record select at the bottom of the report

... and
{table.date1} <= {?datetime_param1} and
{table.date2} <= {?datetime_param2}

MAGICALLY....my report accesses the database immediately and processes the data faster...meeting my 60 second objective!!! (it was taking about 90 secs to access the database before)

Of course, I am happy...but why is this happening??

Later I decided to split this particular mainframe report out of the Crystal report and put it with others

I remove the parameters and record select AND I SLOW TO A CRAWL AGAIN :-(

Ok...I put the record select back in..but not the parameters
like this...(Dec 31, 2099 is the end of time)

... and
{table.date1} <= datetime(2099,12,31)and
{table.date2} <= datetime(2099,12,31)

It STILL takes the 3 minutes...but if I add the parameters back it flys...even though I don't need them anymore????

Not only that but I need BOTH PARAMETERS...if I leave one of them out I crawl.

Obviously I am happy with this accident but want to understand what is going on....Any ideas????

confused in Calgary

Jim

 

[kanau]

Hi, Jim

{table.date1} <= {?datetime_param1} and
{table.date2} <= {?datetime_param2}

all selection done in Database Server.

{table.date1} <= datetime(2099,12,31)and
{table.date2} <= datetime(2099,12,31)

download all the data to local machine and make selection.

Also &quot;and&quot; condition is done in database server. &quot;or&quot; condition will download all data to local machine and process.

Hope this can help!

Kan

 

[Ngolem]

Ok...I can see that....but if I remove these items from the Record Select...I'd draw those values anyway...but it takes ages to access the database with those Parameters removed.

Don't understand why?...these fields are not indices or keys of any kind...there is no linkage to other tables with these fields they are just there.

Hmmmm...could it have something to do with my report options somehow as to how the query is composed by Crystal??

Jim [thinking he should reference everything...lol]