slow report when parameter/formula is added 1

[smeyer56]

Version:Crystal 8.5 Pro
OS:Windows 2000 Pro
DBervasive 2000

I have a report that if I hard code the parameter "customer number", it takes less than a second to run it. But I need the users to enter a customer number so I added a parameter.
The users only know the customers by their phone number and the database uses a string of length 12 for customer number so I added the following code.

BeforeReadingRecords:
Dim x As String
x = {?CustNum}
while len(x) <12
x = "0" + x
wend
formula = x

Problem is that when I use this as a parameter in the report it now takes 30 seconds to gather data.

Any ideas?

 

[Naith]

Smeyer,

Two things: Firstly, can you stipulate that the parameter is passing to the SQL, and that you aren't qualifying it with a LIKE operand.

Secondly, do you notice the same performance overhead if you replace the initial formula with something like the following:

StringVar zero := "00000000000";
If Length({?CustNum}) < 12
Then Left(zero,12-Length({?CustNum})) + {?CustNum}

Naith

 

[smeyer56]

Takes maybe a second or two of the time and i don't understand your first question.

I am using CR select expert:
{SA_LIN_ITEM.Z_KEY_2_COMP_0} = {@num} and
{SA_LIN_ITEM.Z_KEY_2_COMP_1} startswith "PALL"

where the first line is the customer number and the second is the item I am looking for.

 

[vidru]

I'm not familiar with Pervasive, but with any db, trying to use a formula like the above would never get passed to the database for processing.

Is the {?CustNum} parameter entered as a number (8005551234), or is it entered like a phone number string (800-555-1234)?

If it's entered as a number, try changing the parameter type to Number instead of String, and amend you record selection like this:

{SA_LIN_ITEM.Z_KEY_2_COMP_0} = ToText({?CustNum}, "000000000000") and
{SA_LIN_ITEM.Z_KEY_2_COMP_1} startswith "PALL"


-dave

 

[Naith]

The printed formula isn't supposed to pass to the database. It's just a regular formula, not a record selection clause.

StartsWith is interpreted by the database as a LIKE operand, which will slow your results down.

You can check what is passing to the SQL by going to the Database menu, and selecting Show SQL.

Naith

 

[smeyer56]

That was it. It now runs in less than 1 second.
Thanks!