Slow SQL Query 

Hi, I experienced a problem about the slow SQL query with string operations in Conditions. 

For example, there is a table called A which has 7 integer fields. I used the following Condition:

A.field1 + A.field2 + A.field3 + A.field4 + A.field5 + A.field6 + A.field7 > 0 

Will that cause performance issue when the size of the table A becomes large?

Hi Pat,

Well the time of that condition should be proportinal to the size of the table, since no index will help there.
But you mentioned string operations and then did a example with only integers? Got me a bit lost there.

João Rosado
Hi Pat,

You may want to create a "Sum" field and just assign the calculated value whenever you Create Or Update the record. Then just query against that field whenever you need to filter against the total row value.


Thank you for your answer.

The string/agrithmic operation (A.field1+..+A.field7) is dealing with decimals. I am not sure if this operation caused the performance problem when used in 'Condition' in a standard query action although I guess so. For the size of the table (now it's 0.2 million entries), we maintain the tables by re-indexing it regularly and it seems to work (i.e., no siginificant query speed issue after doing so).

Since the fields (i.e., A.field1..7) are dynamic corresponding to the value of every weewday, so their sum is dynamic too. That's why I didn't add the 'sum' field in the table at the beginning.
In my opinion there's a better way to solve this.  Create indexes on all seven fields and change the where part of the query to WHERE A.FIELD1 <> 0 AND A.FIELD2 <> 0 AND A.FIELD3 <> 0 AND A.FIELD4 <> 0 AND A.FIELD5 <> 0 AND A.FIELD6 <> 0 AND A.FIELD7 <> 0.

I know this looks a little cumbersome but you only have to write it once but then there's never any maintenance required as the field indexes are current all the time and you are taking advantage of those indexes by testing each field.  As the number of records grows, this solution will work better and better.

Hope this helps,
Thanks, Curt.

Would creating those indexes affect the performance of INSERT operations (as there will be new values to be inserted to the fields as well.) ?
There should be no issues.  Both Oracle and SQL Server work extremely well even with many indexes.