Formula in select clause = slow response??

[kraber]

I'm using Crystal 7, accessing an ODBC (DB2) database. When I use a straightforward select clause (I'm using the select expert, if it matters) response time is very fast.

Running the query with either:

{TCMS_DEVICES.OPERATING_NUMBER} startswith {?Device} or

{TCMS_DEVICES.OPERATING_NUMBER} = {?Device}

returns a result almost instantaneously.

I'm planning to compile and distribute this report. I'd like to give the user an option of using an exact or wildcard match, so I've set up a parameter that can have the values of 'exact' or 'wildcard'. When I run the query using the following formula in my select statement, it slows to a crawl!

if {?Match} = "Wildcard" then
{TCMS_DEVICES.OPERATING_NUMBER} startswith {?Device} else
{TCMS_DEVICES.OPERATING_NUMBER} = {?Device}


Why is the 'if' statement slowing things down so much? Any suggestions to speed this up?

-Karyl

 

[vidru]

First, try setting your record selection formula to this:
[tt]
(
if {?Match} <> "Wildcard" then
{TCMS_DEVICES.OPERATING_NUMBER} = {?Device}
else if {?Match} = "Wildcard" then
true
)

and

(
if {?Match} = "Wildcard" then
{TCMS_DEVICES.OPERATING_NUMBER} startswith {?Device}
else if {?Match} <> "Wildcard" then
true
)
[/tt]
Then, read this: faq767-3825.

-dave