Application Performance Tuneup

[enoon]

In matters of performance is it bad to have functions in the WHERE clause? If yes, can someone explain why, and how could that functionality be replaced by anything less resource hungry?

 

[Casper]

In matters of performance is it bad to have functions in the WHERE clause? If yes, can someone explain why, and how could that functionality be replaced by anything less resource hungry?

Depends on where the function is.

If you have for instance:

for each customer where customer.name  =  substring('somestring',1,8) no-lock:
end.

then it is no problem.

But if you have

for each customer where substring(customer.name,1,8) = 'abcdefgh' no-lock:
end.

It is a problem, since the index on customer.name cannot be used.

Casper.

 

[enoon]

Sorry for not finishing the sentence

Of course you're right, what I mean was situations where

for each db.table where db.table.field = myFunc().

Is the bad for performance? Why?

 

[TomBascom]

Because the db engine must fetch each record in the table and apply the function to it. No index can be used to bracket that portion of the query.

 

[enoon]

Because the db engine must fetch each record in the table and apply the function to it. No index can be used to bracket that portion of the query.

Solution for this would be to do a for each let progress choose the right index and with an if branch manage the desired data?

 

[TomBascom]

It depends entirely on what you need to do.

But for best db performance you want to take advantage of index bracketting as much as possible. Functions in WHERE clauses break that so they generally ought to be avoided. Usually if you need such a structure you probably have an issue with the design of your database -- you probably have a composite field or other relational travesty.

 

[tamhas]

A lot depends here on what MyFunc() does. If it doesn't depend on the values in the record, then execute it before the for each and assign the value to a variable to turn the for each into something simple. If it does depend on the values in the record, then I would do what you could to bracket the for each to the minimum number of records and then do you test on that result set within the for each.

Oh, and could we move back to child of the main post or start a new thread? The deep nesting on this thread is getting tedious to read.

 

[enoon]

A lot depends here on what MyFunc() does.

Of course most of the cases we have the situation where the function depends on the values from the record buffer. Bu does it make a difference
a:

FOR EACH db.table
   WHERE db.table.record = MyFunc(db.table.record2):
END.

or a:

FOR EACH db.table:
   IF db.table.record = MyFunct(db.table.record2) THEN DO END.
END.

?

About moving the thread: I'm not sure how to do that

 

[tamhas]

As for fixing the deep indentation, either start a new thread or reply to the original post in order to get the indentation back to the left.

Re the difference between the two, the hope is that you can provide some qualification on the second case so that you aren't reading the entire table.