Paritioned views in SQL 7 Developer edition

[dbleyl]

Hi,

I have a table that has been partitioned into a series of smaller tables for size & speed. The tables are partitioned by zip code into 100 tables. From what I understand, I should be able to generate a view that can present these tables as one, and thus eliminate the client-side 'use this table' logic. Accordingly, I should create check constraints on each table, build a view using UNION ALL for each table, and let the query optimizer remove all unnecessary select statements. I build the check constraints, write the sql, but when I access the view, the query times out. When I check the SQL with the Query Analyzer, it indicates that the other SELECT statements aren't being optimized away. Here's what I did specifically:
I created a check constraint for each table
using sql-dmo:
Dim svr As SQLDMO.sqlserver
Dim MyDb As SQLDMO.Database
Dim tbl As SQLDMO.Table
Dim Chk As SQLDMO.Check
Dim i As Integer

Set svr = New SQLDMO.sqlserver
svr.Connect "MYSERVER"

Set MyDb = svr.Databases.Item("DatabaseName&quot

For i = FIRST_TABLE To LAST_TABLE

Set tbl = MyDB.Tables.Item("tblZip" &
Format$(i, "00&quot & "000&quot
Set Chk = New Check
With Chk
.Name = "Ck_" & tbl.Name
.Text = &quot;([ZIP] >= &quot; & i & &quot;000 and [ZIP] < &quot;& _
i+ 1 & &quot;000)&quot;
.ExcludeReplication = False
End With
tbl.Checks.Add Chk
Next

--This generated the check constraints, for example:
([ZIP] >= 12000 and [ZIP] < 13000) for tblZip12000

Then I created the view, which looked like:

CREATE VIEW VehiclesByZip AS
SELECT * FROM tblZIP00000
UNION ALL
SELECT * FROM tblZIP01000
UNION ALL
...
SELECT * FROM tblZIP99000

I then try to access the view with a stored procedure along
the lines of:
CREATE PROCEDURE [sp_VehiclesbyZip]
@zipcode int
AS
SELECT ZIP, MM FROM VehiclesByZip
WHERE ZIP = @zipcode

I've tried various modifications. Does the Developer Edition allow partitioned views? Any help would be helpful.
Thanks,
Don

 

[bitbrain]

Unless I'm missing something check constraints are used for data storage (add/update), not retrieval.

The bigger question is, why were the tables &quot;partitioned&quot; to begin with? Doing so defeats the purpose of a database system. My guess is that the performance hit for union'ing 100 tables together far outweighs the hit of having lots of rows in a single table.

Performance should not be a problem if the table is indexed properly. Is the server powerful enough for your database?

 

[dbleyl]

Hi,

Thanks for your input bitbrain. Partitioning is actually a technique used for performance gains. This type is called 'horizontal partitioning'. It's advocated at several SQL Server sites and even explained in the SQL Server Books online(see 'Partitioning' in the glossary). I think it fits into the 'denormalization' category, whereby data that has been sliced & diced to the highest level of normalization is then reorganized to address 'practical' concerns such as performance. The combined row count of these tables exceeds 100 mil, and we do have a wimpy server.
The idea is that the query engine filters out unnecessary unions by examining the check constraints first. In this case, given zip code 77056, it's supposed to 'know' by looking at the constraints that the only table that fits the criteria is tblZip77000 and avoid the unions all together. Does that make sense?