At 04:56 AM 19/08/2011, red_october2009 wrote:

>Just to close off this case, the final SQL that SET was looking at is "slow-ish" because it has to make at least 2 hits to the DB (maybe even 4?) because at least 2 of the fields are BLOB SUB_TYPE 1 fields, which I have learned require multiple hits to the DB. Without those fields included in the field list, the query is < 1s.
>
>I need to include those fields and the slower response time is tolerable if I explain it to my customer.

If you have such large BLOBs being fetched by that query and the "blobless" query is sub-second then it sounds as though you have the solution in your hands. If you are using a program interface that doesn't support fetching BLOBs on demand from a multi-row set, either store the BLOB in a separate table or construct a view, and redesign your app to fetch one blob on request.

>-- Side note --
>
>During my 22 years of experience, I have had excellent response times ( < 1s) on all properly made queries, using multiple tables and JOINs of various kinds, on both/either VARCHAR() AND INTEGER fields, in any/all the major RDBMSs.
>
>To stuff everything in 1 table defeats the purpose of using an RDBMS. It's not "wrong", but, just not what RDBMS was intended for.

True and fair; but it's not just about writes, is it? Don't write apps that fetch volumes of stuff over the wire that the user won't touch.

./hb