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
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
Comment