SQL Server - vertical partitioning - Asked By Snehalata Maheshwari on 04-Jan-07 06:34 AM

would vertical partitioning improve performance.

if i break up the table(vertical partitioning) in two tables one is having the most used fields and the other on rarely used fields and then constructing view from these two tables would improve the performance ?

Response - F Cali replied to Snehalata Maheshwari on 04-Jan-07 10:38 AM

If you break up your table vertically and create a view over it, it may become slower if your query is going to use columns from both tables because of the overhead in joining the tables.  Depends on your queries but one way of improving it is by creating indexes on those columns that you usually use in your WHERE clause.

SQL Server Helper
http://www.sql-server-helper.com

vertical partitioning - Snehalata Maheshwari replied to F Cali on 05-Jan-07 04:38 AM

if i break up the table vertically anmd create a view then at the time of data fetching i fetch data for the field which reside only in one table at that point of time would the view use both of the tables or just simply fetch data from the require table only?

Response - F Cali replied to Snehalata Maheshwari on 05-Jan-07 09:46 AM

If you look at the execution plan of your SELECT statement when you only select from the columns of 1 table and compare it against a select from columns from 2 tables within your view, you will see that SQL Server is smart enough to only read from the table where the columns are being referenced.  Meaning, if your SELECT statement only includes columns from one table in your view, then it will only read from that table and not from the other tables that you may have in your view.

SQL Server Helper - Free Test Assessment
http://www.sql-server-helper.com/free-test/default.aspx

vertical partitioning - Snehalata Maheshwari replied to F Cali on 08-Jan-07 03:23 AM
if i select columns of one table only or the columns from two tables the cost of table scan(for table2) is same in execution plan. why so?
Response - F Cali replied to Snehalata Maheshwari on 08-Jan-07 09:46 AM

Depends on how you set-up your view.  It will only be looking at one table if you select only columns from that table if you are using an INNER JOIN to join your 2 tables.  As for the execution plan, if you see a full table scan, it means that you don't have the necessary indexes that SQL Server can use to execute your query fast.  Check your WHERE clause and see if the columns you specified there have indexes.

SQL Server Helper - Frequently Asked Questions
http://www.sql-server-helper.com/faq/index.aspx

Index creation - Snehalata Maheshwari replied to F Cali on 09-Jan-07 05:02 AM

I have created view as follows

CREATE VIEW Data
WITH SCHEMABINDING
AS
SELECT A.PartitionID,FundID,ReportDate,ForeignTaxWithheld,DomDividendIncome,RGainShortTerm,RGainLongTerm,NewIssueRGainShortTerm,
 NewIssueRGainLongTerm,ChgUnrealizedGain,ReplaceTax,TotIncomeBefFee,TotalIncome,EndingNetCapital,EndingRedemptionUnits,
 BeginRedemptionAmount,EndingRedemptionAmount,EndingUnits,InterestOverseas,ExpenseOverseas,OrdIncome,
 ReallocationExpense,BeginRedemptionFee,EndingRedFee,BeginGrossCapital,EndingGrossCapital,GPFees,FixedExpense,MergerCost,
 SellingCommission,GrossRoR,NAV,GAV,GPMgmtFee,IMMgmtFee,GPIncentivefee,IMIncentivefee,NetRoR,MonthCounter,
 BegUnits,BegAddUnits,BegAddAmount,EndAddAmount,GrossRealizedGain,BrokerCommission,
 NetRealizedGain,OperatingExpense,OffsellExp,OrgExp,USObligationIncome,FixIncomeIntrIncome,
 CapitalGain,SellingFee,SellingMgmtFeeMidQtr,SyndicateCost,BeginNetCapital,DomesticDividendExp,FixedIncomeIntrExp
FROM dbo.vPart1  A
LEFT JOIN dbo.vPart2 B
ON A.PartitionID = B.PartitionID

when i create index on this view as follows

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT  OFF

create unique clustered index ind1 on Data(FundID,ReportDate)

it gives me following error

Cannot index the view 'MonthliesTest2.4.dbo.Data'. It contains one or more disallowed constructs.

tell me how to create indexed view?

Response - F Cali replied to Snehalata Maheshwari on 09-Jan-07 10:17 AM

One of the requirements when creating an index view is that "The view must not reference any other views, only base tables."  Based on the name of the tables your view is accessing, namely vPart1 and vPart2, it looks like these two objects are views and not tables.  Make sure you only use base tables so that you can create an indexed view.

SQL Server Helper - Frequently Asked Questions
http://www.sql-server-helper.com/faq/index.aspx

Index Creation - Snehalata Maheshwari replied to F Cali on 10-Jan-07 01:28 AM
these vPart1 and vPart2 are tables only. but still the view can not be created?
Response - F Cali replied to Snehalata Maheshwari on 10-Jan-07 10:10 AM

One of the other limitation of creating indexed views is that you are not allowed to use OUTER joins.  In your view, you are using a LEFT OUTER JOIN, and SQL Server does not allow you to create an indexed view because of this.  If possible, use an INNER JOIN.

SQL Server Helper Forums
http://www.sql-server-helper.com/forums/default.asp