Re: How many fields in a table are too many

On Thursday 26 June 2003 1:03 pm, btober(at)seaworthysys(dot)com wrote:
<snip>
> > As long as we are playing "who's is biggest", I have one with
> > 900+ attributes (normalized) but there is a big warning - if you
> > have a query that returns hundreds of columns it will be very,
> > very slow. Slow as in tens of seconds to do a "select * from
> > fattable" when fattable has <1000 records.
>
> Is the SELECT * the only circumstance? That is, if you specify a
> small number of columns, does the response improve even though the
> table actually has that large number of columns but is only be
> asked to supply a column-limited result set? What about when you
> limit the rows but not the columns with a WHERE clause? And of
> course the last case when you limit both rows and columns?

It's the number of columns in the result, not the width of the table.
Speed is also substantially influenced by whether the returned
columns are fixed or variable width (variable is slower) and where
the variable columns appear (earlier is worse).

Selecting a single column from a wide table or even array element 1000
from an array is fast. For example on my slow machine selecting array
element 600 from an array with a limit of 100 rows takes 0.02 seconds
but selecting elements 1-600 takes 20 seconds as does selecting
element 1 600 times (select a[1],a[1],a[1]...). Whether you select
the first, middle or last field/array element does not impact the
query speed much.

You can see the effect with a 2 column table:
create table foo (i int, x text)
and add some data.

On my test (1000 rows):

Single field:
select i from foo: 10ms
select t from foo: 10ms

Int field 600 times:
select i,i,i,i...(600 times) from foo: 2400ms

Text field 600 times:
select t,t,t,t...(600 times) from foo: 6500ms

599 ints and a text:
select i,i,i,i...(599 times), t from foo: 2500ms

Text and then 599 ints:
select t,i,i,i...(599 times) from foo: 6400ms

Cheers,
Steve