August 25, 2003 at 9:29 am
Hi guys,
a little problem for you.
I'm using a partitioned view to split 17.000.000 rows into about 17 tables (one per month).
Well, I followed all Microsoft documentation building the views, I have clustered index on data (and something else) column, but when I make a query (involving data, of course) the execution plan shows me that anyway every table is scanned, even if there is the right partition column (which is data). I saw in Microsoft docs that SQL engine should skip tables not involved, and perform the query on the right table.
In this way the query is faster on the original table, instead on the view...
Does anybody know what's happening in my DB!??
Thanks a lot!
tb
August 26, 2003 at 8:15 am
What is the type of your SQL Server? Only the Enerprise version allows the use of partinioned views.
Also did you implement the check constraint on the partitioning column on each and every tables?
Within the view did you put UNION ALL (not just UNION)
The easiest way would be if you could post the DDL of your tables and your partitioned view to see where the problem could be.
Bye
Gabor
Bye
Gabor
August 27, 2003 at 3:04 am
Well, I'm using SQL Server 2K Enterprise Edition.
Yes, I created every single table with the following script, and as you can see there's the right (I hope) check constraint (my only doubt is the way I wrote the check, using data in string format instead of datetime).
--Partition table 1
CREATE TABLE DBO.Prezzi_Storico_2002_04
(
[CodTitAS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DataPrezzo] [datetime] NOT NULL CHECK (DataPrezzo >= '20020401' AND DataPrezzo < '20020501' ) ,
[Cod_Fonte] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Prezzo_Titolo] [float] NULL ,
[F_USato] [bit] NULL ,
[Ordine] [int] NULL ,
[Commento] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tipo_Prezzo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Cod_Div_AS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[F_Usato_GI] [bit] NULL ,
[DataProvider] [datetime] NULL ,
[Ordine_GI] [int] NULL
) ON [PRIMARY]
ALTER TABLE DBO.Prezzi_Storico_2002_04
ADD CONSTRAINT PK_Prezzi_Storico_2002_04
PRIMARY KEY CLUSTERED
(
[DataPrezzo],
[CodTitAS],
[Cod_Fonte],
[Tipo_Prezzo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
ALTER TABLE DBO.Prezzi_Storico_2002_04
ADD CONSTRAINT FK_Prezzi_Storico_2002_04_FnPrezzi
FOREIGN KEY ( [Cod_Fonte] ) REFERENCES ...
ALTER TABLE DBO.Prezzi_Storico_2002_04
ADD CONSTRAINT FK_Prezzi_Storico_2002_04_Titoli_Anagrafica
FOREIGN KEY ( [CodTitAS] ) REFERENCES ...
ALTER TABLE DBO.Prezzi_Storico_2002_04
ADD CONSTRAINT DF_Prezzi_Storico_2002_04_TipoPrezzo DEFAULT (N'LAST') FOR [Tipo_Prezzo]
CREATE INDEX IX_Prezzi_Storico_2002_04_1
ON Prezzi_Storico_2002_04
(
[DataPrezzo],
[Ordine],
[CodTitAS]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX IX_Prezzi_Storico_2002_04_2
ON Prezzi_Storico_2002_04
(
[DataPrezzo],
[Ordine_GI],
[CodTitAS]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX IX_Prezzi_Storico_2002_04_3
ON Prezzi_Storico_2002_04
(
[DataPrezzo],
[CodTitAS],
[F_USato]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX IX_Prezzi_Storico_2002_04_4
ON Prezzi_Storico_2002_04 ([CodTitAS]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX IX_Prezzi_Storico_2002_04_5
ON Prezzi_Storico_2002_04 ([DataPrezzo]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX IX_Prezzi_Storico_2002_04_6
ON Prezzi_Storico_2002_04 ([Cod_Fonte]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX IX_Prezzi_Storico_2002_04_7
ON Prezzi_Storico_2002_04 ([DataProvider]) WITH FILLFACTOR = 90 ON [PRIMARY]
------
--Partition table 2
CREATE TABLE DBO.Prezzi_Storico_2002_05
(
[CodTitAS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DataPrezzo] [datetime] NOT NULL CHECK (DataPrezzo >= '20020501' AND DataPrezzo < '20020601' ) ,
[Cod_Fonte] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Prezzo_Titolo] [float] NULL ,
[F_USato] [bit] NULL ,
[Ordine] [int] NULL ,
[Commento] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tipo_Prezzo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Cod_Div_AS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[F_Usato_GI] [bit] NULL ,
[DataProvider] [datetime] NULL ,
[Ordine_GI] [int] NULL
) ON [PRIMARY]
ALTER TABLE DBO.Prezzi_Storico_2002_05
ADD CONSTRAINT PK_Prezzi_Storico_2002_05
PRIMARY KEY CLUSTERED
(
[DataPrezzo],
[CodTitAS],
[Cod_Fonte],
[Tipo_Prezzo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
...
--Partition table N
CREATE TABLE DBO.Prezzi_Storico_2003_03
(
[CodTitAS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DataPrezzo] [datetime] NOT NULL CHECK (DataPrezzo >= '20030301' AND DataPrezzo < '20030401' ) ,
[Cod_Fonte] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Prezzo_Titolo] [float] NULL ,
[F_USato] [bit] NULL ,
[Ordine] [int] NULL ,
[Commento] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tipo_Prezzo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Cod_Div_AS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[F_Usato_GI] [bit] NULL ,
[DataProvider] [datetime] NULL ,
[Ordine_GI] [int] NULL
) ON [PRIMARY]
ALTER TABLE DBO.Prezzi_Storico_2003_03
ADD CONSTRAINT PK_Prezzi_Storico_2003_03
PRIMARY KEY CLUSTERED
(
[DataPrezzo],
[CodTitAS],
[Cod_Fonte],
[Tipo_Prezzo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
Talking about the view, the script is:
CREATE VIEW DBO.vw_Prezzi_Storico AS
SELECT * FROM Prezzi_Storico_2003_03 UNION ALL
...
SELECT * FROM Prezzi_Storico_2002_05 UNION ALL
SELECT * FROM Prezzi_Storico_2002_04
Using simple query I have an improvement in performance, but using complex queries they are faster if I have a unique table (with the same primary, foreign key and indexes as the partition tables) intead of the view.
Thanks a lot!
tb
quote:
What is the type of your SQL Server? Only the Enerprise version allows the use of partinioned views.Also did you implement the check constraint on the partitioning column on each and every tables?
Within the view did you put UNION ALL (not just UNION)
The easiest way would be if you could post the DDL of your tables and your partitioned view to see where the problem could be.
Bye
Gabor
August 27, 2003 at 9:41 am
Well, I solved my problem. The execution plan in Query Analyzer showed me that were involved tables even if actually they weren't. Using SET STATISTICS IO I could see that phisical reads were done toward only the appropriate table, and not the others.
If only Microsoft diagnostic tools gave the same results to equal inputs!! :-)))
Anyway, thanks a lot for the help!
Bye,
tb
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply