Roy,

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

It's what you hear in a lot of SQL/database text books, and it's a case of
"depends".  The issue is selectivity and whether there are other conditions
involved.  If the WHERE clauses commonly refer to just the Boolean column,
without any other conditions, then you're going to want to index it.  If the
Boolean column is regularly included *in addition* to other, more selective,
columns, then you won't need to index it.   Boolean columns aren't
particularly selective, but you still can't beat an index with a table scan.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< 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 >>

Yes, but that's the key - the SELECT expressions only get evaluated *after*
the rows are selected, so as long as the result set is smallish, the
evaluation speed will never be an issue.

Now, if the UDFs are in the WHERE clause, that's a different story.....

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim, Roy and all who chimed in.

Tim - I made those changes and under the EDB Manager, the performance jumped to about a 3 second query, which was a heck of a lot better.  Thank you.

What I did do, for testing, was to use Charles note and made a trigger for insert/update to apply the InitCap routine on the F, M and L Names.

I then took out the call to the function in the query.

The thing now that has me stumped is that I am using DevExpress's CXGrid that access the datasource which has the EDBQuery in question.  It loads the query on form create.  I disable the controls before the query.

What I'm now trying to hunt down why it takes 3'sh seconds on the query, but adds another 17 seconds when it hits the enable controls. after the query.




"Tim Young [Elevate Software]" wrote:

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"

Lance


Take out the update trigger. If someone enters a surname of ffish (and yes it does exist) and its "corrected" to Ffish they can alter it back to ffish with a bit of moaning. If they can't alter it back there will be much stronger moaning.

Roy Lambert

Lance,

<< The thing now that has me stumped is that I am using DevExpress's CXGrid
that access the datasource which has the EDBQuery in question.  It loads the
query on form create.  I disable the controls before the query.

What I'm now trying to hunt down why it takes 3'sh seconds on the query, but
adds another 17 seconds when it hits the enable controls. after the query.
>>

I'm guessing that the grid is trying to load all of the rows into the grid
in one shot.  I can never remember the property name, but there's a property
that controls whether the grid behaves like a normal TDBGrid with virtual
row loading/navigation, or loads all of the rows into memory for the grid.

--
Tim Young
Elevate Software
www.elevatesoft.com

Spot on.  They did have that and it greatly improved performance.

"Tim Young [Elevate Software]" wrote:

I'm guessing that the grid is trying to load all of the rows into the grid
in one shot.  I can never remember the property name, but there's a property
that controls whether the grid behaves like a normal TDBGrid with virtual
row loading/navigation, or loads all of the rows into memory for the grid.

--
Tim Young
Elevate Software
www.elevatesoft.com