SQL View/Table question

[WertmanTheMad]

MVP's and the like

I am looking for suggestions , confirmation

Let me start by saying bar none, performance is paramount with the
queries to be retured off this view/table query.

To that end I am completley donormailzing the data into a view (or a
table) with all possible legal combinations ( I have seen this reffered
to as Croation Method ?) Its ends up at 3 total rows per account on
average.

THis query is to be executed only from the web, so once again the
performance is paramount.

Ok here is what I have so far I have a set of related tables 5 or so
that are for the most part 1 to 1 but in the case of one particular
table its an average raqtion of 20 to 1.

Broken all out with 150k customer rows it ends up at 500k total rows in
the view

About 7 cols are dynamic aggregates (Sum(tran_AMT)) , min(tran_amt) ,
etc

I had it in a view and with 1/5 million rows (In the view) it performed
adequatley, I will most likeley get to somewhere around 10 million
total rows (In the view)

I looked into indexing it but thats not going to happen because I am
doing several subquery's , outer joins etc.

The View with a Select * returns in 40 Seconds

Now i ran into an issue with recursing this query into several seperate
subqueries and hit the 256 table limit, after only about 15 recursions.

Now the data only gets updated once , perhaps twice a day, in a batch
transaction.

So I Put all the data from the view into a table, and indexed that, I
can return all rows in 12 seconds and any refining conditions I throw
at it, like Date > 12/1/2003 (then it executes in under 6 second_ only
slices the query time down big time (A very good thing)

Since its only updates 2x a day I drop and recreate the table in my DTS
, and I have triggers on the other tables for Online updates (usually
no more that 100 a day) but to ensure the table is ALWAYS current it
gets recreated on import of additional rows.

Is there a name to this maddness ? My co-workers aree leary of it but
they come from a dbase background, denomailzing it into a table and
then indexing the table I couldnt be happeier with ther performance.

Are there any lurking gremlins in this design ?

I cannot see any pitfalls with doing this , or are there some ?,
basically I am denomailizing for read-only query performance.

They are concerned about the denorilization for performance, even
though its only on a read only table (was and could be a view I just
cant index this particualr view)

So I guess Im looking for a , "Sounds OK" or "Sounds Great" from the
SQL Gods ....

Chris

[Erland Sommarskog]

WertmanTheMad (cw******@webchamps.com) writes:

Since its only updates 2x a day I drop and recreate the table in my DTS
, and I have triggers on the other tables for Online updates (usually
no more that 100 a day) but to ensure the table is ALWAYS current it
gets recreated on import of additional rows.

Is there a name to this maddness ? My co-workers aree leary of it but
they come from a dbase background, denomailzing it into a table and
then indexing the table I couldnt be happeier with ther performance.

Are there any lurking gremlins in this design ?

I cannot see any pitfalls with doing this , or are there some ?,
basically I am denomailizing for read-only query performance.

They are concerned about the denorilization for performance, even
though its only on a read only table (was and could be a view I just
cant index this particualr view)

There is too little information in your post, to give any absolute blessing
to this. But with the outline you give - source data updated twice,
aggregating the data into a read-only table could very well be the winner.

The alternative would be to look into the underlying query, and see if
indexes etc can be improved. Without knowledge about the table and the
queries, I cannot say whether this is workable or not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[WertmanTheMad]

> The alternative would be to look into the underlying query, and see
if

indexes etc can be improved. Without knowledge about the table and the queries, I cannot say whether this is workable or not.
Yeah, unfortunatley there are about 10 subqueries in the view and 5 or
so outer joins, Indicies on the View are a No-Go hence I ended up here.

Erland Sommarskog wrote: WertmanTheMad (cw******@webchamps.com) writes:

Since its only updates 2x a day I drop and recreate the table in my DTS , and I have triggers on the other tables for Online updates (usually no more that 100 a day) but to ensure the table is ALWAYS current it gets recreated on import of additional rows.

Is there a name to this maddness ? My co-workers aree leary of it but they come from a dbase background, denomailzing it into a table and
then indexing the table I couldnt be happeier with ther performance.
Are there any lurking gremlins in this design ?

I cannot see any pitfalls with doing this , or are there some ?,
basically I am denomailizing for read-only query performance.

They are concerned about the denorilization for performance, even
though its only on a read only table (was and could be a view I just cant index this particualr view)
There is too little information in your post, to give any absolute

blessing to this. But with the outline you give - source data updated twice,
aggregating the data into a read-only table could very well be the winner.
The alternative would be to look into the underlying query, and see if indexes etc can be improved. Without knowledge about the table and the queries, I cannot say whether this is workable or not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp