Subject Re: SELECT statement runs at different speeds
Author red_october2009
Hi Philip,

Putting some of the "smarts" into a COMPUTED BY field was a good idea. What I didn't tell you in the original posting was that I actually had two tables with a very similar structure, with the same speed problem. The first one, without any BLOB fields ran much faster using the COMPUTED BY field method.

The one *with* the BLOB fields (One SUB_TYPE 1, and the other SUB_TYPE 0) did not give a detectable speed improvement with the COMPUTED BY field. I reasoned that the BLOB fields must be causing some kind of interference with performance. I further reasoned that,since the fields I was referencing in my IF condition were on the "far side" of the BLOB fields (ie: The BLOB fields were field indexed at about 7 and 8, and the fields I referenced are "down" at 12 and 13), that some how the FB engine had to "walk" thru the BLOB fields, to find the "End of BLOB Field" marker, then, continue with getting the value of the field I wanted. So I simply ALTERed the field POSITION of the BLOBs, moving them to the very bottom of the field index, so that the referenced fields come first.

I'm not sure if the explanation makes sense, because I think I read some where that the content of a BLOB is not stored in the same location on disk as the rest of the non-BLOB fields in a record, however, my performance experiments are showing a 98% speed improvement.

Now, running the SQL on the table containing the BLOB fields is as fast as running it on the table without the BLOB fields.

Thanks for your help and for getting me on the right track.

- Red October