Related to break-by clause .........!

Posted by Admin on 26-Nov-2007 09:34

All Replies

Posted by Tim Kuehn on 26-Nov-2007 09:41

Break-by incurs overhead no matter if you do it against a db table or a temp table.

The decision on populating a TT and then using that for output would be decided by their relative performance. If the code's selecting from a large number of db records, and producing a small output, and the output order isn't matched by a db table index, then a TT can be more performant despite the additional overhead of building it.

The best way to find out is to code it, do some performance testing, and see what happens.

Posted by Thomas Mercer-Hursh on 26-Nov-2007 11:41

To reinforce a point which Tim made, to me the real advantage of building a temp-table is in those cases where one has highly variable or complex selection and sorting criteria, including computed values, which are difficult to do in a performant way directly from the database. Certainly, the biggest bottleneck is usually the speed of getting things out of the database, so that should be defined in a way which optimizes the selection process. Building to a temp-table is a handy way to select out limited datasets, to combine multiple records into one record in the fashion of SQL queries, to compute values, and to do sorting and breaks on very flexible user criteria, including on computed values.

Posted by Admin on 27-Nov-2007 09:00

This thread is closed