Efficient SOUNDEXing?bold

[butkus]

I have the following record selection formula:

{DA_DAMAST.DM_CASE} = {?Case Number} or
{DA_DAPDEF.DU_NAM} = {?Defendant Name} or
soundex({DA_DAPDEF.DU_NAM_SUR1})=soundex({?Last Name})

and am trying to make thing run faster.

Without the soundex functions, the report is very quick, however with it, the darn thing runs for nearly a minute.

What I'm trying to do, is give my staff a summary of case information (based on their input of either the case number / the defendants name (as it appears in the data) / or just a soundex of the last name (mis-spells included).

Can the record selection formula be made to ignore the soundex if cause number or defendant name are populated?

I've tried the following, but it has the same result
If {?Last Name}="" then
{DA_DAMAST.DM_CASE} = {?Cause Number} or
{DA_DAPDEF.DU_NAM} = {?Defendant Name} or
else
soundex({DA_DAPDEF.DU_NAM_SUR1})=soundex({?Last Name})

As always, your thoughts are welcome.

James

 

[synapsevampire]

The performance hit is the result of soundex being processed by Crystal, rather than on the database (check the database->show sql query).

Crystal is very picky about what it passes to the database, and apparently soundex does not get passed to the database (I tried several methods), and if it's involved with a record selection formula, it either breaks ALL of the the pass through, or in some tests, provided erroneous pass through....not good...

If you have a database that supports the soundex function, I'd approach this with a Stored Procedure (make the SQL check for soundex only when required as it's a performance hit). SQL Server also has a Difference function which might prove useful.

Another approach, which will still net slow results when the ?last name parameter = "" would be to use a subreport to return the soundex portion, that way the main report will pass sql for the first two parameters, and you can suppress the subreport if the ?last name parameter = "".

When the subreport does fire, it will have to retrieve the rows into the report and then perform the Soundex for each condition, hence slow results.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[MVisconte]

Use of Soundex this way can only be done on retrieved values, meaning all "possible" values must be returned in order to be evaluated. I think that the local evaluation, coupled w/ a maximal recordset is what is causing your pain. If you can otherwise limit the # of records returned, you'll be better off, as a smaller record set will have to be evaluated.

The best alternative is to find a way to export the soundex function to the server... if you can evaluated it there, it should be much faster.
Maybe there's a soundex in the db functions, or if not, maybe an SP.

 

[synapsevampire]

Read the previous post, MV.

Soundex is available in Oracle and SQL Server, and likely in most db's.

The question of the poster was whether the record selection can be designed so that when the parm is empty, to ignore the soundex function, unfortunately CR is weak at constructing pass through SQL, and if it detects that soundex is part of the criteria (even if it isn't going to be used because an IF disqualifies it's use), CR won't pass the rest of the SQL, resulting in having CR do all of the processing, resulting in poor performance.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[butkus]

Thanks to all. SOUNDEXing as a part of the primary record selection criteria is horrible. I was able to move this portion out to a subreport (across one table as opposed to many) and performance improved - although still not great.

Luckily, it should not come into play to often.

I may investigate the stored procedure option to further improve performance, but for now, its acceptable.

Thanks again, regards.

James

 

[synapsevampire]

Yeah, I figured that would improve it, changing to a Stored Procedure will really speed it up.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com