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 |
|
snrani
Starting Member
7 Posts |
 Posted - 2005-07-27 : 07:21:03
|
| Hi,A Table on my database is partitioned into multiple tables and indexes on each of the tables is proper. I also created a view on all of the partitioned tables. When ever I query on this View it takes lot of time as there is very huge data. Is it necessary that I create index on the view also for querying on the view so that I could get the results quickly?Thanks in advance. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-27 : 07:37:10
|
| Does your VIEW, and the underlying tables, conform to the requirements for a horizontal partition (constraint on PK column etc)? If so querries should be very quickSee "Partitioned View" in BoLKristen |
 |
|
|
snrani
Starting Member
7 Posts |
Posted - 2005-07-27 : 07:59:32
|
| Yes all the underlying tables are properly indexed. Every table contains "4562090" rows of data. Is it the reason that querying on such data is difficult or have to create an index on the view also? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-27 : 10:05:12
|
| When you say "properly indexed" does that include the Constraints necessary for Horizontal Partition?4 million rows is a fast query, shouldn't be slow even if SQL has to table scan.Can you post an example of the SQL for a Query that is running slowly? Some DDL for the table & view would help too. Maybe the query itself is the problem.Kristen |
|
|
|
snrani
Starting Member
7 Posts |
Posted - 2005-07-28 : 04:15:15
|
| The table structure is as CREATE TABLE [dbo].[CreditJuly2005] ( [TxnID] [bigint] NOT NULL , [IDCol] [bigint] NOT NULL , [TxnStatus] [utTxnStatus] NOT NULL , [UtilCode] [utUtilCode] NOT NULL , [UtilAmount] [float] NOT NULL , [UtilAmountCEF] [float] NULL , [LineID] [bigint] NOT NULL , [StartDate] [datetime] NOT NULL , [EndDate] [datetime] NOT NULL , [LinkID] [int] NOT NULL , [RecChgBy] [utMaker] NOT NULL , [RecChgAt] [datetime] NOT NULL , [MonthN] [int] NOT NULL , [RecInsertedAt] [datetime] NULL ) Primary key exists on [TxnID],[TxnStatus],[UtilCode],[LineID],[IDCol],[MonthN] CREATE INDEX [IXCreditHistoryDate] ON [dbo].[CreditJuly2005]([StartDate], [EndDate]) CREATE INDEX [IXCreditHistoryLine] ON [dbo].[CreditJuly2005]([LineID]) ON [CCSTxnDataIdx] CREATE INDEX [IXCreditHistory] ON [dbo].[CreditJuly2005]([TxnID], [TxnStatus], [UtilCode]) CREATE INDEX [IXCreditHistoryLineDateR] ON [dbo].[CreditJuly2005]([LineID], [StartDate], [EndDate]) and the view is union of 12 tables for the year. View is queried directly instead of the tables.Query is done on the columns which are indexed |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-28 : 06:52:21
|
| From the script that you've posted the table does NOT have Constraints on the Primary Key, so SQL won't treat the View as a Horizontal Partition, and therefore the performance will be slow.As I said earlier see "Partitioned View" in Books-on-Line for details of how to add Constraints to your tables to create a Horizontal PartitionKristen |
|
|
|
|
|
|
|
|
Query on Indexing of Views