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 2 1678
WertmanTheMad (cw******@webch amps.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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
> 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******@webch amps.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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Richard Holliingsworth |
last post by:
Hello:
I have an Access 2K form I built from a SQL Server 7.0 view. I want to
lock certain fields in the database from users so they can see them on
the views and forms, but NOT be able to edit them.
I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books and
could not find how to do this.
Any advise will be greatly appreciated.
|
by: Ryan |
last post by:
I have a very complex view which we've been working with for some
time. As we have progressed with this view, we have reached the stage
where the data is generated into a table which is then used in our
reporting application. This has improved reporting performance
considerably.
There is a stored procedure which copies the data from the view into a
table. Very simple select into statement.
However, I was wondering if I actually held...
|
by: Fran?ois Bourdages |
last post by:
Hi is there a way to know if object (view, function, etc) are invalid
?
let say a have a table t1 (field col1, col2)
and a view v1 (field t1.col1, t1.col2)
if I drop t1.col2, the view v1 is not working anymore. I want to know
that information.
In Oracle (8.1.7), i can query the all_objects, user_object table,
where status = 'INVALID'. So i can recompile invalid objects (or
|
by: brendan_gallagher_2001 |
last post by:
Hi,
I have a view(A) and I am trying to do a join on another table (B) to
include only rows where date values in view A is greater than in table
B. I also want the view to pick up rows in viewA based on date values.
Here is what I have so far:
SELECT *
FROM viewA vw
left JOIN tableB tb ON
|
by: xeqister |
last post by:
Greetings,
I would like to know whether there is a way to estimate/calculate
the view table size in DB2. As what I understand, view is just a
logical table and we cannot simply calculate the size based on the
physical table(s) that made up the view. Any ideas?
| |
by: Justin |
last post by:
I am creating a web app for a client in VS.NET using ASP.NET with C#. I need
to query three tables in a database using one parameter and display the
results on the page. my question is should I use a Stored Procedure or view
in SQL Server to get the data from multiple tables? What is the difference?
and where can I find a some articles on write code to display the results of
the SP or view?
Thanks, Justin.
|
by: Beowulf |
last post by:
I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.
Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.
CREATE VIEW...
|
by: Matik |
last post by:
Hey,
First, sorry if this post appear twice, because, I can not find my post
recently send, trying to post it once again.
I'm out of ideas, so, I thought, will search help here again :(
I'm trying to prepare a view for ext. app. This is in normal cases very
easy, but what if the view structure should be dynamic?!
Here is my point (I will siplify the examples).
|
by: randy.buchholz |
last post by:
Been fighting this one for a while, looking for help.
I have a simple page with a grid view and details view (just managing a
single table). The details view is primarily used for creating new records,
and the grid for display. Everything works fine unless there are no records
in the table. In this case the details view does not show, so there is no
way (for a user) to enter the first record unless I set the default mode to
Insert. I...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |