Execution Plan on a View looking at Partitioned Tables

Giganews Newsgroups
Subject: Execution Plan on a View looking at Partitioned Tables
Posted by:  Matthew Nguyen (khangtannguy…@gmail.com)
Date: Fri, 8 May 2015

I currently have tables that are partitioned out by year & month for our sa=
les transactions. This was set in place at our company before I even starte=
d. For example, we have sales tables that would look something like this:

factdailysales_201503 etc ...

Generally, I've always performed dynamic SQL to capture a Start Date, End D=
ate, find out what partitions those are, and then loop through each of thos=
e partitions ... but its starting to become such a hassle and I've learned =
that this is probably not the best way to do it in terms of just maintenanc=
e, trouble shooting, and performance.

I decided to build a view that would UNION ALL of my sales partitions toget=
her. However, I don't want selecting from the view to have to scan all of t=
he partitions on execution, it would take away the whole purpose of partiti=
oning tables out. Because of this, I added check constraints on date to eac=
h of my sales tables. This way when I selected from the view, it would know=
which tables to access from instead of scanning every table.

Here are the following examples below:

    SELECT SUM([retail]) =20
    FROM Sales_Orig=20
    WHERE [Date] >=3D '2015-03-01'=20

This query has the execution plan of only pulling from the partitions that =
I needed.=20

My problem that i'm facing right now is that most of the time when my team =
will be writing stored procedures, they would more than likely write their =
queries where a date variable is passed into the where statement.

DECLARE @SD DATE =3D '2015-03-01'=20

SELECT SUM([retail]) =20
FROM Sales_Orig=20
WHERE [Date] >=3D @SD=20

However, when a variable is being passed in, the execution plan now scans A=
LL of the partitions in the view, causing the performance to take wayyy lon=
ger than when I hard coded in the date

I suppose I could do dynamic SQL again and insert the date string into the =
SELECT statement, but it would bring me back to the beginning of trying to =
get rid of dynamic SQL in the first place for this simple sales query.

So my question is, am I setting this up wrong? Am I on the right track? It =
seems that the view can't take in a variable for the check constraint and e=
nds up scanning every table. Is there another approach anyone would recomme=
nd? Maybe my original solution of just looping through partitions via dynam=
ic SQL is the best way to do it?