[IQ] Which table design has the best performance for IQ ?

[Former Member]

Hi experts,

Assuming there is  over 1 billion for this table (table 1):

Table Design 1

Day               Time Range     No. of visitors

20150101     0000-0100           100

20150101     0100-0200          200

20150101     0200-0300          400

etc...

Table Design 2

Day               0000-0100     0100-0200     0200-0300...etc

20150101     100                    200               400

etc...

Which table should have the optimal performance for IQ if we perform a query for simple listing / aggregation ?

[markmumy]

The answer will really depend on your overall needs.  Option #1 will give you the narrowest table, and likely the best for most aggregations.  Option #2, though, will be the smallest in terms of space and rows.  It will perform aggregates, for a single range, as fast as option 1, if not faster.

The main difference really is do you want your data pivoted?  Will the users want to aggregate multiple ranges in the same output?  Is space a concern?  How simple do you want the SQL to be for users?

I've seen both designs in use.  Neither is bad, in general.  But one model over another can give you fits if your users and reports need data in another format.

Mark

[c_baker]

The first table design would be more optimal for IQ.  It is narrower and indexing the first table will also be much simpler.  As will query SQL and aggregation.

Queries against the second, especially aggregation might force IQ into a row-based retrieval which will be slower.

Chris

[Former Member]

Since you have a primary key in common , you can go ahead with Cluster table. you can also use inner join for common entries and outer join for all the entries.