I have a Query that uses an InitCap custom function to capitalize a name properly.  When under a lot of records (test is about 124,000, the performance is very slow with the query taking on average about 30 seconds. I've used the execution plan and it isn't giving me any suggestions for further optimizations.

Select MBDINITCAP("Persons".LastName + ',' + "Persons".FirstName + ' ' + "Persons".MiddleName) as BowlerName, "Persons".PersonID, MBDInitCap("Persons".LastName) AS "LastName"
from Persons
Where "Persons".IsActive = TRUE
Order by LastName COLLATE "ENU_CI", Firstname COLLATE "ENU_CI", MiddleName COLLATE "ENU_CI"

Test DB is at
www.cdesoftware.net/elevate/testdb.zip

Suggestions Welcome!
Lance

Note: I just took off the InitCaps function and the performance changed from 26.469 seconds to 3.781 seconds.

So then question is there something to help the performance of the function or a better way to handle?

Lance

On my system the select without the MBDInitCap function takes 7+
seconds.  Damn you kids with your hotrod computers!

I can save a second by replacing your last line with
Order by BowlerName COLLATE "ENU_CI"
but check whether this gives you identical results.

If you post your MBDInitCap function someone may be able to suggest
improvements.

If MDBInitCap does its job perfectly, you could apply it in an
update/insert trigger to Persons.  Then you'd know that the names were
correctly formed when entered and wouldn't have to fiddle with them when
doing queries.  (But what if a bowler calls himself "Octavio de la
Roche"--is "De La Roche" acceptable?)


On 7/23/2010 4:38 PM, Lance Rasmussen wrote:
> Note: I just took off the InitCaps function and the performance changed from 26.469 seconds to 3.781 seconds.
>
> So then question is there something to help the performance of the function or a better way to handle?
>
> Lance
>

Lance


You've answered the first question I was going to ask, and I think you've isolated the bottleneck

Next question - what do you do with the result after its been generated - is there an option to do the transform as part of the presentation layer?

Roy Lambert

Lance


As written the query won't work at all. You're supplying a comma separator between last & first name and your function relies on a space so You end up with names like

Aaron,abby M

Roy Lambert [Team Elevate]

Lance


I've been playing and I think there's a problem Tim needs to have a look at (he'll probably spot something I haven't). With the ORDER BY clause in the query there's no way I could get a sensitive result set so EDBManager is having to write out 120k+ rows. I first thought it was the collation you were using (ENU_CI when the columns are defined as ANSI and the index I thought the ORDER BY clause would use had ANSI_CI definitions, but it had an extra field in it which would probably stop it being used) so I altered the ORDER BY clause to use use ANSI_CI and created an index with the three fields as ANSI_CI and that didn't work. I also tried creating three separate indices and that didn't work.

The other thing I did was to add an index for IsActive even though I thought an index on a boolean wouldn't achieve much - but it did. Without the ORDER BY clause I was obtaining sub-second results.

A couple of comments. First the function you've written is never going to be massively effective. Looping is the kiss of death for any interpreted (OK Tim I know its tokenized) language. I stopped playing when I looked at the exution plan and started to figure out I was getting an insensitive result set but I was trying

CREATE FUNCTION "iCaps" (IN "UCaps" VARCHAR COLLATE ANSI)
RETURNS VARCHAR COLLATE ANSI
BEGIN
DECLARE WithCaps VARCHAR;
SET WithCaps = COALESCE(UCaps,'');
IF LENGTH(WithCaps) > 1 THEN
SET WithCaps = UPPER(SUBSTR(WithCaps,1,1))+SUBSTR(WithCaps,2,LENGTH(WithCaps)-1);
ELSE
SET WithCaps = UPPER(WithCaps);
END IF;
RETURN WithCaps;
END

Not as full as yours but select time dropped from c24 secs to c9 secs. If I was to do something like this I'd use a Delphi external function. I would also set it so that each bit separately and have a separate flag to say if it was a surname or something else. You only need to apply the Mc tests to surnames.

Finally I wouldn't do it at all. I've been down this route before and given up several times. As it stands your function copes with things like McDonald and O'Neil but not Yan le Derf or MacDonnald or Oneil or Macdonald. As Charles suggests do it on input. If you can't then write the function in Delphi its a lot easier adding all the ifs ands buts and maybes into a Delphi function.

Roy Lambert [Team Elevate]

Lance,

<< I have a Query that uses an InitCap custom function to capitalize a name
properly.  When under a lot of records (test is about 124,000, the
performance is very slow with the query taking on average about 30 seconds.
I've used the execution plan and it isn't giving me any suggestions for
further optimizations. >>

Okay, there's a few issues here:

1) Your correlation name for the second expression
(MBDInitCap("Persons".LastName) AS "CapLastName") is causing the ORDER BY to
use it instead of the actual LastName column in the table.
2) Your collation names are different from the actual table/indexes.
3) There isn't an index on the IsActive column.
4) There isn't an index that the ORDER BY can use, even with 1) corrected.
The idxPerson index is incorrect also, because you've got a "name" index
that begins with a unique person ID, thus negating any further sorting or
searching by name.

1, 2, and 4 prevent a sensitive result set, so the entire 124,000+ rows need
to be written out to a temporary table, and 3 just adds an entire table scan
on to the whole bunch.

I would correct 1, 2, and 4, and that should help:

ALTER INDEX "idxPerson" ON "Persons"
("LastName" COLLATE "ANSI_CI" ASC,"FirstName" COLLATE "ANSI_CI"
ASC,"MiddleName" COLLATE "ANSI_CI" ASC,"Birthday" ASC)

but 3 will make it instant:

CREATE INDEX "IsActive" ON "Persons" ("isActive" ASC)

Final SQL:

Select MBDINITCAP("Persons".LastName + ',' + "Persons".FirstName + ' ' +
"Persons".MiddleName) as BowlerName,
"Persons".PersonID,
MBDInitCap("Persons".LastName) AS "CapLastName"
from Persons
Where "Persons".IsActive = TRUE
Order by LastName COLLATE "ANSI_CI", Firstname COLLATE "ANSI_CI", MiddleName
COLLATE "ANSI_CI"

Notice that I changed the LastName correlation name to CapLastName
(important).

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Not as full as yours but select time dropped from c24 secs to c9 secs. If
I was to do something like this I'd use a Delphi external function. I would
also set it so that each bit separately and have a separate flag to say if
it was a surname or something else. You only need to apply the Mc tests to
surnames. >>

Although that will help (he could also just code it as a native external
module and get the same effect), the first rule of thumb with database
optimization is disk, disk, disk.   Eliminate the I/O to/from the disk and
you won't have to worry about the performance of functions that don't do any
I/O, since they're peanuts compare to any disk accesses.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>1) Your correlation name for the second expression
>(MBDInitCap("Persons".LastName) AS "CapLastName") is causing the ORDER BY to
>use it instead of the actual LastName column in the table.

I never even noticed that one

Certainly explains why I couldn't get a sensitive result set.

>3) There isn't an index on the IsActive column.

I always thought that booleans weren't good candidates for indices or is that a hangover from my DBISAM memories?

Roy Lambert

Tim

><< Not as full as yours but select time dropped from c24 secs to c9 secs. If
>I was to do something like this I'd use a Delphi external function. I would
>also set it so that each bit separately and have a separate flag to say if
>it was a surname or something else. You only need to apply the Mc tests to
>surnames. >>
>
>Although that will help (he could also just code it as a native external
>module and get the same effect), the first rule of thumb with database
>optimization is disk, disk, disk. Eliminate the I/O to/from the disk and
>you won't have to worry about the performance of functions that don't do any
>I/O, since they're peanuts compare to any disk accesses.

Whist I agree that disk I/O is going to bear the greatest cost looping through 124k * upto 20+20+20 characters is not good practice and should never be encouraged

Roy Lambert