Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 16 total |
Query Speed |
Fri, Jul 23 2010 7:17 PM | Permanent Link |
Lance Rasmussen CDE Software Team Elevate | 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 |
Fri, Jul 23 2010 7:38 PM | Permanent Link |
Lance Rasmussen CDE Software Team Elevate | 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 |
Fri, Jul 23 2010 8:56 PM | Permanent Link |
Charles Tyson | 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 > |
Sat, Jul 24 2010 4:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Jul 24 2010 8:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sat, Jul 24 2010 9:51 AM | Permanent Link |
Roy Lambert NLH Associates 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] |
Sat, Jul 24 2010 10:48 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Jul 24 2010 10:51 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Sat, Jul 24 2010 11:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Jul 24 2010 11:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |