partitioned views Explain Plan

Hello I created a partitioned view on my database db1 and It comes from the UNION ALL of 2 tables (I split 1 very big table in 2 smaller tables... Horizontal partition!!) that are on the same db1. When I query them I receive in the EXECUTION PLAN an Item called CONCATENATION of both input tables, This seems to be more expensive than using only one big table so partitioning the big one gets none sense.. I would like to know if this is normal and If I'm doing well with partitioning.

Please see the script below:

CREATE TABLE [dbo].[TREP_NOVEDADES_2006] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1),
[CDPRECIO] [varchar] (12),
[CDTIP_HECT_DTO_PAG] [varchar] (2),
[FEGENERACION] [datetime] NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2006] ADD
CONSTRAINT [TREP_NOVEDADES2006_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_FEGENERACION2006_CK] CHECK ([FEGENERACION] < '2007-01-01 00:00:00.000')
GO

CREATE TABLE [dbo].[TREP_NOVEDADES_2007] (

[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,

[CDCONCEPTO] [varchar] (12) NOT NULL ,

[CDTIPO_VALOR] [char] (1),

[CDPRECIO] [varchar] (12),

[CDTIP_HECT_DTO_PAG] [varchar] (2),

[FEGENERACION] [datetime] NOT NULL ,

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2007] ADD

CONSTRAINT [TREP_NOVEDADES2007_PK] PRIMARY KEY CLUSTERED

(

[NMSEC_NOVEDAD]

) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_FEGENERACION2007_CK] CHECK ([FEGENERACION] > '2006-12-31 00:00:00.000'),
GO
CREATE VIEW TREP_NOVEDADES AS
SELECT * FROM TREP_NOVEDADES_2006
UNION ALL
SELECT * FROM TREP_NOVEDADES_2007
GO

-- Here is where I query the view...
SELECT * FROM TREP_NOVEDADES_2006 WHERE FEGENERACION < '2006-05-23 00:00:00.000'

Am I doing anything wrong ?

Initially I'd try creating a NONCLUSTERED Index on the FEGENERACION column in each table.

Chris

|||

I would tend to dissent in this case; it looks to me like this is a case in which you are going to have plan crossover and the optimizer will abandon the index in favor of some scan -- either a clustered index scan or a table scan.

Julian:

When have a great deal of data and you perform the search:

SELECT * FROM TREP_NOVEDADES_2006 WHERE FEGENERACION < '2006-05-23 00:00:00.000'

You are invariably going to get stuck with a long-running execution plan. In this case you are going to get somewhere around half of the records in your 2006 table. You cannot benefit from a simple index because the potential number bookmark lookups would make for a worse execution plan than a table scan. The only index that MIGHT be of some benefit would be a cover index that had its first column be (as Chris suggested) the FEGENERACION column. However, since your SELECT statement is for ALL columns I doubt that I would build such a cover index.

To me, the moral of the story is that if you are truely going to read something on the order of half of the data in a table you need to face the reality that you are going to do a table scan. Avoid such queries as much as you can.

|||

I guess I should have explained my reasoning... ;)

My thinking was that the table containing the more recent data would be eliminated quickly during execution of the query that was provided. As long as the statistics are up to date then surely this would result in an index seek in the more recent table - bookmark lookups would not be required as there would be no rows that met the criteria.

I would imagine that a table scan would be performed on the other table(s) rather than a seek and subsequent lookup, for the reasons that you mentioned, and that similar performance to the non-partitioned table would be seen.

Chris

|||

Please forgive me, Chris, I have messed up a little bit.

I walked over to the next building to fetch a liter of water and realized that although the index would probably not apply in this particular instance that Chris' suggested index was probably still a good suggestion. It will help in many instances in which you are filtering by date/time -- it just is not likely to help when you are selecting something like half the table.

Sorry, I messed up.

Kent

( You beat me; I tried to make amends before you felt compelled to respond; you are right )

|||

You've not messed anything up - you made some valid points in your post!

It's good to have an answer challenged and then to feel compelled to justify it. Anyway, my suggestion was only my opinion - it may or may not help the OP.

Chris

:)

|||Agreed. :-)|||Thank you so much guys for your point of view and your help...|||God Damn't ... I did what Chris said (those NONCLUSTERED indexes) ... and now subtree cost is sooooo low.... I hope It boost my queries...

Thank you so much