An indexed view appears to be a solution to managing huge tables, but my
efforts for this have missed the mark and there may be better solutions
anyhow. In a table of roughly 500 million records, 50 million may belong to
a given month of a year. Without creating 12 tables of each month, can this
be done somehow without using an indexed view? For that matter, it may not
hurt to try the indexed view again. What is the best solution for handling
queries against enormous amounts of data (Windows 2000 Advanced Server with 4
Gig RAM - not /3GB switching, no /PAE).
Regards,
Jamie
If your main concern is performance, index tuning is paramount. Indexed
views can greatly improve performance of certain types of queries,
especially aggregations. You may need different indexes to support
different types of queries.
However, you still need to consider manageability since it will take a while
to rebuild indexes on large tables/views such as this. If possible,
consider SQL 2005 since it provides partitioning to better facilitate
managing large tables.
-
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
> An indexed view appears to be a solution to managing huge tables, but my
> efforts for this have missed the mark and there may be better solutions
> anyhow. In a table of roughly 500 million records, 50 million may belong
> to
> a given month of a year. Without creating 12 tables of each month, can
> this
> be done somehow without using an indexed view? For that matter, it may
> not
> hurt to try the indexed view again. What is the best solution for
> handling
> queries against enormous amounts of data (Windows 2000 Advanced Server
> with 4
> Gig RAM - not /3GB switching, no /PAE).
> --
> Regards,
> Jamie
|||A truly great help at this point would be an example of an indexed view using
an aggregate.
Regards,
Jamie
"Dan Guzman" wrote:
> If your main concern is performance, index tuning is paramount. Indexed
> views can greatly improve performance of certain types of queries,
> especially aggregations. You may need different indexes to support
> different types of queries.
> However, you still need to consider manageability since it will take a while
> to rebuild indexes on large tables/views such as this. If possible,
> consider SQL 2005 since it provides partitioning to better facilitate
> managing large tables.
>
> -
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
>
|||>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
Below is an example using the Northwind [Order Details] table:
CREATE VIEW dbo.Product_Order_Summary
WITH SCHEMABINDING
AS
SELECT
ProductID,
SUM(UnitPrice * Quantity) AS GrossTotal,
SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
SUM(Quantity) AS OrderQuantity,
COUNT_BIG(*) AS Orders
FROM dbo.[Order Details]
GROUP BY
ProductID
GO
CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
ON Product_Order_Summary(ProductID)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...[vbcol=seagreen]
>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
|||Thank you.
Regards,
Jamie
"Dan Guzman" wrote:
> Below is an example using the Northwind [Order Details] table:
> CREATE VIEW dbo.Product_Order_Summary
> WITH SCHEMABINDING
> AS
> SELECT
> ProductID,
> SUM(UnitPrice * Quantity) AS GrossTotal,
> SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
> SUM(Quantity) AS OrderQuantity,
> COUNT_BIG(*) AS Orders
> FROM dbo.[Order Details]
> GROUP BY
> ProductID
> GO
> CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
> ON Product_Order_Summary(ProductID)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment