>Hi guys,
>I am having severe performance problems with sql server. I Have a sales table(sales) with 11 million rows. I have another table that accumulates the sales data into months for the last 2 years(month_sales). In the sales table I have a field to indicate that the record has been added to the calculated table(month_sales). When I start updating the month_sales and the field in sales table the whole sql server comes to a stand still. Is this the best way to do this or is there any other way to create calculated fields.
>
>TIA
I don't know about SQL Server, but in VFP I would consider the option to do update the other table
as soon as a record is added, deleted or modified. This can be done through triggers. Probably there are similar options in SQL Server.
The idea is that, for example, if you add a new record with a certain sales amount, this is then updated immediately to the monthly sales table.
Perhaps, if you do this one record at a time (as soon as changes are made), the performance will not drop so noticeably.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
