Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mpaul31
Starting Member
3 Posts |
 Posted - 2010-08-06 : 16:37:18
|
| my site has a daily deal and I show the user the total number of sales for that deal for the day. I fetch this with a simple query using a count() aggregate.i also have a deal history page, where i also show the count using the same aggregate. now to my question, is it an expensive call to keep callling the aggregate in my queries or should i de-normalize my table to store the calculate count?it just seems i need an additional "moving part" that calculates and updates the denormalized table and keeps it in sync. I also read something about materialized views??Thoughts? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-06 : 16:45:18
|
| How expensive it is depends on the query, database design, hardware, data. How many rows are in the table? How fast/slow is your current query?Materialized views are an Oracle concept, but in SQL Server they are called indexed views. We used to store the aggregate data like you are proposing when we were on SQL 2000, but since we've been on 2005/2008 we haven't had the need (performance-wise).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
mpaul31
Starting Member
3 Posts |
Posted - 2010-08-06 : 16:53:39
|
Right now the transaction table has only a few thousand records, but the site has only been up for a few months, so it should be growing much larger in the near future (expanding to multiple cities). It just seems a little wasteful to calculate the total sales for deals in the past where those totals will not change. With the following command, is there ever a chance the transaction row count and total sales count would not be in sync (not including deleting rows)? Does the default isolation level work for this?Isolation levels always trip me up...INSERT INTO [transaction] ... UPDATE dealSET TotalSales = (select count(*) from [transaction] where dealid = @dealId)WHERE dealId = @dealId |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mpaul31
Starting Member
3 Posts |
Posted - 2010-08-06 : 17:05:17
|
| Great, thank you! and just for my clarification, the reason this will work with the default isolation level is because the initial insert into the transaction table will add a lock and no other transaction (say another purchase of the deal) can insert a row, so the count(*) query will always return the correct #? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Should I De-normalize?