Storing Massive Results 1

[Jesus4u]

This is a sample of some data that is a result of a SELECT that calculates the total number of sites that referred traffic to our websites.

What I would like to do is store these results in another table so that my boss can simply SELECT that results table and get the report.

Is this good practice? I need your opinions.

Thanks


coralridge.org 52 4549 4549 0
- 36 3200 3199 1
216.247.64.85 5 499 499 0
65.240.226.68 1 97 97 0
crministries.org 0 84 84 0
google.com 0 43 43 0





Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[tlbroadbent]

I would recommend that you create a view from the query and have your boss seelct from the View. Advantages, data is always up-to-date, no extra storeage is required, and no table maintenance is needed. Disadvantage the View will not return the result as fast as the table. There are always trade-offs. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Jesus4u]

you mean write out the query in html or some other format? Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[SQLSister]

Often sums are stored in other tables for ease/speed of extraction, especially if the base table(s) are large. The key is to make sure they stay current; you can do this with triggers if they need to be up-to-the minute or with stored procedures run on a schedule if the user can accept a lag time in the sum. You also need to consider the timeframe of the sum and how you will handle it if records from the base table(s) are deleted.

For instance - suppose you want to store monthly sums but people also want to look at past months or fiscal years. Store the monthly sun in a table with one record and update the sums as records are changed or inserted or deleted. At the end of the month, transfer the data to a historical reocrd table add the data bout the month and fiscal year the data is from. Now you can start recording the new month's information in the original table, but can still easily query historical data.

Now that I think on it, I could probably do this with one table if I use the current month and year as fields in the table, then when doing the update from the trigger, it searches for the record for the current month and year and that is the one it updates. HMMM, this will solve a problem for me that I was about to start working on. Thanks for the question.

 

[tlbroadbent]

"you mean write out the query in html or some other format?"

No. I mean create a SQL View. Look in SQL BOL for information about views.

SQL Views
A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Jesus4u]

oohhhhh ok thanks I never knew that. Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[tlbroadbent]

We have SQL BOL, technical manuals, books, training, etc. so we can read about these things and learn our profession. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Jesus4u]

What kinda remark is that? Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[SQLSister]

Terry, not often that I disagree with you (usually you are right when you disagree with me), but I got the impression that we were dealing with a very large base table and that querying it or just using a view would be too slow. Maybe because of the type of data he was querying which are hits to websites. This data can get very big, very fast resulting in slow performance and maybe timing out when you need to do calculations on it. That's why I agreed that using a separate table or tables might be a good idea if you take the time to think through the process and set it up correclty. Yeah a view will help the guy doing queries to not have to deal with the complexity of the underlying query the view is based on, but as you pointed out, it won't improve performance.

 

[tlbroadbent]

SQLSister,

No argument. I noted the speed issue in my reply. Questions to ask.

1) How often will the Boss run the query?
2) How long does the query run?
3) How current must the data be? Should the summary table be created daily, hourly, every 15 minutes? Or does the boss want real time data when he runs the query?
4) If periodic updates are sufficient, would it be better to create a webpage with the result of the query and let anyone who needs to see the data have access? No new table is needed, no table maintenance, etc.

Using a summary table may be the best way to go. It all depends on requirements which we know so little about. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[edwinjs]

if you are using SQL 2000. How about using Indexed Views?

 

[sguslan]

I agree with EDWINJS ..
I think indexed views are the answer ... though not sure they are available in SQL7, but SQL2000 does ..

Hop Terry and SQLSister have comments on the viability of indexed views from their experirnce ..

Here are the MS documentation on indexed views:

http://msdn.microsoft.com/library/d...ry/en-us/vdtsql/dvtskcreatingindexedviews.asp

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7/html/dvconIndexedViews.asp

 

[sguslan]

One more link on indexed views with more informative iformation:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp

 

[tlbroadbent]

Indexed views may help. However, there are several restrictions on Indexed Views. You'll have to carefully there restrictions in SQL BOL.

Topic: Creating an Indexed View

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_9jnb.asp

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm