• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

Several complicated views merging into table with combined data

Hi,

I have seven (could be more in future) complicated views that take a few seconds to run each.  The data that the views are based on is changing constantly.  All the views produce a result with the same column format.  Each row in a view includes an aggregated key (a few columns concatenated) which, together with another column (int), can form a unique key for each row.

I have a main table that my website uses to view the data quickly and I need to feed each views output into that main table on a regular basis to update fresh data.  When updating main table with the views output all previous entries from that view (uniquely identified in main table by aggreagtedKey and int column) can be deleted.

My plan was to set an agent to run each minute for each view and MERGE the view output into the main table .  

While I am pretty sure this will work I want to know if this is the best method or if anyone can suggest something more efficient.  My aims are below

refresh main table as quickly as possible with fresh data from complicated views
ensure that the main table is able to be read by website quickly
prevent any locking of records that could result in timeouts
ensure it is as scalable as possible as the number of complicated views may increase in the future

I am using MSSQL 2008R2 STD

Let me know if you need any more info and thanks in advance ;-)
0
cp30
Asked:
cp30
  • 10
  • 5
  • 2
  • +2
1 Solution
 
slightwv (䄆 Netminder) Commented:
0
 
cp30Author Commented:
Thanks, I will look into indexed views for optimizing the views.  Does my approach to merging the views into the table seems acceptable or can you suggest a better way to do this?

Thanks
0
 
cp30Author Commented:
It seems that views can only be indexed if they reference base tables only. My views contain other views so don't think I can use indexed views to optimize.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
slightwv (䄆 Netminder) Commented:
I'm an Oracle DBA by trade and know enough SQL Server to be dangerous.

In Oracle we have a thing called Materialized Views.

These are views based on tables that are actually physical objects that are not evaluated at run time but kept up to date either on commit of a base table or a schedule.

It is my limited understanding that SQL Server Indexed Views are synonymous with Oracle Materialized Views.
0
 
slightwv (䄆 Netminder) Commented:
>>It seems that ...

Sorry you caught me typing.  My last post was for your previous one.

Hopefully another SQL Server Expert will be by later.
0
 
cp30Author Commented:
Thanks for trying.

To be honest, my question isn't really about how to optimize my views, I know that they're a bit slow and I accept that, they do a lot of calculations.  I just want to get the view data into a place where where my user's can query it very quickly, i.e. the main table I mentioned.  I just need to know the best way of doing this and avoiding deadlocks etc. given that the data that the complicated views are built on is forever changing.

Thanks
0
 
slightwv (䄆 Netminder) Commented:
This is what Materialized Views do in Oracle.  They are 'materialized' into a physical object (real table).

I can only imagine that SQL Server has the same sort of feature.

I'm sure a SQL Server DBA will be along soon.  Maybe they will also know Oracle and can bridge the terminology.

0
 
skaraiCommented:
As far as I am concerned the process sounds convoluted.  It is diffcult for me to make a good recommendation without knowing the business problem at hand. Some sample data might help understand what you are trying to do/what needs to be done.
0
 
cp30Author Commented:
Hi,  unfortunately I cannot provide any sample data, but I will try and explain.

The system collects lots of information from various sources every few minutes and stores in tables within the database.  The 7 views perform calculations on the current data (data is always being updated), each view is looking at a different set of data but the output from each view is in the same format (i,e same columns for each view).

I need the website to be able to view the calculated data, the direct approach would be to select a union of all 7 views and that would show my users what they need but obviously this will take too long and be performing calculations that are not needed (the calculated data users see only needs to be updated every minute).  So rather than every user pulling from all 7 views I want to dump the output of each view into a table so that the users can then just query that table for the combined outputs from all calculated views.

Does that explain things any better?

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>I want to dump the output of each view into a table

That is the definition of a materialized view in Oracle.  I'm just not sure what it is called in SQL Server.

At a given time 'new' information in a view/table are updated the 'view' is physically refreshed.  It is not derived at query time.
0
 
twolCommented:
You might look at triggers a little. As you pull the seven sets of data, the last table updated in the process could have a trigger that updates the data in the main table using the view. Make sense?
0
 
cp30Author Commented:
No, I don't quite get that.  The only thing that would prompt the seven views to be generated would be my scheduled task at the minute, and if I'm running a scheduled task then why not explicitly merge the view into the table, I can't see how a trigger helps the efficiency of merging the data into the main table.  There is no point where the data is "ready" or "generated" automatically, it's forever changing and I just need to grab it at a place in time (i.e. every minute).

Please explain if I'm missing something in your suggestion.

Cheers
0
 
LowfatspreadCommented:
ok

1) an oracle materialised view is a sql server indexed view

2) please clarify the underlying data changes result in "cummulative" changes/refinements to various metrics?
    or you are after some sort of snapshot of your system at a regular frequency....?

3) are the underlying tablles/views all in the same database/server/instance

4) complex means different things to many people... can you provide us with an idea of what you consider to be complex
     ...?

5) the whole point of the "materialised view" /"indexed view" position is that when your underlying data changes
    the effect of the changes will immediately become visible in the view ... because the underlying data change ripples the effect through into the views physical storage...

6)  you could also write your own triggers to achieve the same sort of thing that an indexed view can provide ... which may be of benefit in certain scenarios...

7) another possible strategy is to use a replicated/log shipped database source base your reports off... (although this would be slightly more out of date than your current system) , It could have some advantages ... but without fuller details on what you are actually attempting its very difficult to advise.

0
 
cp30Author Commented:
Hi,

I will try to answer the best I can

1) Because my views have views within them I don't think I can use indexed views (that's what I read)
2) The data is loaded from various different sources and then refreshed every 1-2 minutes, same data being refreshed, some data will drop out of scope over time and some will be updated every refresh with freshest data
3) yes all in same instance
4) by complex I mean carrying out union of different views analyzing data in different way and using functions etc to perform calculations, i.e. complex = slow (1-30 seconds)
5) do you have an example, I'm not sure I get how this would work, would every changing data in base tables not really add to server resources keeping that view up to date? can I use it on a view that contains views, do you have an example of an indexed view in a similar scenario?
6) but what would my triggers actually do, how is the most efficient way to copy the data from the 7 views into an area that is easy/fast to query?
7) Not sure this would be quick enough and I need to get something place fairly quickly so may not be an option for now.

Hope that helps you understand better, thanks for your input so far  ;-)
0
 
LowfatspreadCommented:
my points about the oracle/index views where mainly for slightly...

if your current views themselves use views then either we'f have to not use them... or maybe the place for
an index view is at a lower level in the process ...

2) -- sorry getting confused ... are you talking about your base data table being refreshed/reloaded  
   or your summary VIEW requirements..

5) what is the transaction rate on your system?
     what update stategy do you use...  (data history, delete,Insert or update in place,..?)
     is this an OLTP system,, OLAP,,,,?
   
    are you sure that the overhead for each individual "base" transaction to additionally compile itself into your (7?)
    statistics is more of an overhead than the very long 1-30second overhead of your processing the view every 1-2minutes
    thats approaching a 25-30% loading
    (you do mean the whole process for the 7 views takes 30seconds not 30 seconds each...)


sorry going out shortly will re-visit later...

0
 
cp30Author Commented:
Each view could take up to 30 seconds, in practice most are under 5 seconds ad there are 2 that are a bit slower.   The base tables has data have data loaded into them from different processing threads in the application constantly, each source of new data is queried every 1 or 2 minutes, I have have that part working quite well, it pulls data from XML source and loads into temp table and then uses MERGE to update base table (there are 7 base tables, one for each type of information I pull). So each base table is being refreshed constantly by different application threads from different sources. Several views for each base table perform different types of calculations and then we have the view for each base table that combines all analysis views and produces results for website.

I'm out and about at the minute but hope that explains things a little better. I'm not a SQL expert, sorry I don't understand all that OLTP stuff.

Cheers
0
 
skaraiCommented:
Let me try to understand this better > The data that the views are based on is changing constantly.
Does that mean the same data changes or are you constantly adding new rows to the table? This is a key question - in case you are only adding new data - one possible way is to store the already calculated data in a history table. Every 1-2 min when fresh data were to arrive you can perform the calculations and pump the data into a history table.
If it is however the same data row changing over and over >  aggregated key (a few columns concatenated) which, together with another column (int), can form a unique key for each row it's a different story. I am in this case assuming the integer is the only changing element (based on its change you could also create a history) btw is this a unique constraint you are trying to create per row instead of using a unique_ID column which SQL can automatically populate for you. The aspects of several columns together would stir towards a composite key index - the question were is the data per row highly repetitive? Abstract data architecture is difficult since the data itself eventually reveals how it needs to be dealt with - I can understand that you cant give data examples - on the other hand for us to better help try to maybe paraphrase the data in a manner where patterns can possibly be understood.
0
 
cp30Author Commented:
Hi,

The data in the tables is more often than not updated, occasional inserts/deletes.

The aggregated key forms a unique constraint that can be used when loading the data into the base tables using a merge on that column.  It could probably be a composite key but someone suggested creating the aggregated key a while ago and it seems to do the job.  It basically means that when I get my raw data downloaded I can create the aggKey from that data and use it to get the data into the database, when I run the scheduled agents each one (there's 7) pulls from a view (based in the ever changing data) and merges it with the table that my websites uses for searches.  The aggkey is unique within each of the 7 views, but needs an int added to make it unique within the main table.

With regard to the data, it's decimal figures that change, the rest of the information is describing the content of the row and will not change, the decimal amount/s are updated constantly.

Hope this helps describe what I am trying to do.  

I have the scheduled tasks setup and merging the output of the views into the main table and it seems to be running ok, but my data volume will increase in future when I pull in different types of data so any suggestions on better ways to do it will be welcome.
0
 
cp30Author Commented:
Didn't really get any more suggestions to improve on way that I'm doing it already so will close off question.
0
 
cp30Author Commented:
Didn't really get any more suggestions to improve on way that I'm doing it already so will close off question.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 10
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now