Solved

create index on view fails, not schema bound

Posted on 2011-09-13
14
791 Views
Last Modified: 2012-05-12
I'm trying to create an index on a view as:

CREATE UNIQUE CLUSTERED INDEX IX_Members ON members (memberId)

but I get the error: "Cannot create index on view 'members' because the view is not schema bound."

I'm not sure what "view is not schema bound" means, but in any case, is there a way around this? Queries using this view take a really long time.
0
Comment
Question by:jmarkfoley
  • 8
  • 6
14 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36532182
Hi.

- First check to see that the underlying tables are indexed properly.
- Also ensure that the view is not employing any JOIN or WHERE clause conditions that are inefficient, i.e., render your indexes useless.
- Check the EXECUTION PLAN on the query generating the view to help.

See this as a reference:
http://www.sqlservernation.com/home/tag/schemabinding
Note the WITH schemabinding and caveats
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 36532679
the view does have multiple joins.

I've checked your link. There are no null values in the column I want to index. I tried the "with schemabinding" and got the message:

Cannot schema bind view 'dbo.members' because name 'osh2011.dbo.tblPaEmpGenInfo' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Not sure what "two part" format is. The view references two tables in a different database. The table mentioned does reference itself (I suppose, not sure what that means) in  a left join. Am I screwed?

How do I check the execution plan? Sadly, I've never done that before.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36532852
Two-part format would be schema_name.table_name or in your case dbo.tblPaEmpGenInfo. If you are querying across multiple databases and so need the three-part naming which includes database_name.schema_name.table_name then you cannot schema bind the view and as such cannot index it, unfortunately. If you post the query behind the view, maybe we can spot some way to optimize it so that hopefully you are not "screwed." *smile*

You can right-click and select "show actual execution plan or click graphical icon is probably easiest in SQL Management Studio. http://msdn.microsoft.com/en-us/library/ms178071.aspx

Kevin
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 36544308
Kevin - I'd like to check out that execution plan. We had an issue like this once before, and my predecessor did an execution plan and helped fix the problem. So, how exactly do I do that? The MS help tells you why/when you should run this, and what the results can tell you, but it doesn't actually tell you how to run it.

My included image show what I get when I right click on the view as you suggested. I see nothing related to "execution plan." The "SQL Profiler" tool gives me a blank screen with no wizard to tell me what to do next. Please advise. Thanks.
view.jpg
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36544433
My apologies for not being clear. Unfortunately, I am logged into a Linux system at the moment so using a different tool other than SQL Management Studio; therefore, my screen shots will not match up. But I will try to explain more clearly.

1. In the query window where the SQL code is that you are executing, right-click and you should see "Include Actual Execution Plan" ...
2. Query > Include Actual Execution Plan ...
3. On my SQL 2005 SSMS, there is a graphical icon 7 spots to right of the Execute button on toolbar ...
4. Ctrl+M should work.

If one of those doesn't get it, we can break out SET statements. :)
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 36545737
OK, found the icons. I have:

a) Display Estimated Execution Plan

That gives me the output:

Query 1: Query cost (relative to the batch): 50%
Query 2: Query cost (relative to the batch): 50%

Not sure what to do with that information. Nor does it tell me what Query 1 and Query 2 are. I have a union in this view, so perhaps these are each part of the union.


b) Analyze Query in Database Engine Tuning Advisor

Perhaps I'm a bit obtuse about these tools ... this one appears to give me the size and number of rows in my current database (not the database my view is looking at). In any case I don't really see any "advise".

c) Include Actual Execution Plan
d) Include Client Statistics

These two seem to turn the respective buttons on and off, but don't "do" anything that I can see. Perhaps something else needs to be run which uses these settings?

Now that I've found the buttons, perhaps you could help explain how I can use them.

More info: We are converting from a 3rd party accounting program modified to be a pension benefits program. The "old" system has two main member tables, one for active members and one for pension benefit recipients and their families. The old system creates a new database for each year. This view joins these two tables into one view. It is in a new database because that is where all the migrated tables, views, and procedures will end up and we'll stop using the by-year databases.

I'm going to check the two main tables and see if indexing can be improved.

ALSO ... you say you are using Linux and the screen shots are different. Intriguing. I am also using Linux with jdbc to a Windows SQL Server. Is that what you are doing? If so, do you have a Linux GUI for the SQL Server DB? Or, are you running SQL Server directly on Linux. Please let me know as I didn't know any of these possibilities existed. Thanks.

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36545921
Okay, to be clear:

If you post the query behind the view, maybe we can spot some way to optimize it so that hopefully you are not "screwed." *smile*

You can right-click and select "show actual execution plan or click graphical icon is probably easiest in SQL Management Studio. http://msdn.microsoft.com/en-us/library/ms178071.aspx

That all went together. I wanted you to grab the SELECT statement that makes up the view. If you right-click the view, you can use Script View AS > CREATE TO > New Query Window ...

-- paste that query here in a code snippet
-- execute the query with execution plan turned on
-- right-click on the execution plan and save as XML
-- attach that XML file here
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36545930
"I'm going to check the two main tables and see if indexing can be improved." < That is a good idea. If indexing is fine on the tables, seeing the query behind the view may reveal issues in query that are preventing the indexes from being utilized correctly. When you get the graphical execution plan, it will often tell you if there are missing indices.
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 36546094
OK, I'll post the view first, then post the execution results later. It's a big query! I am attempting to merge 2 tables in this view. Some members exist in table a, but not in b and some exit in b, but not in a, hence the (select a union b ...) "main" table. The query is of the form:

select x.id, a.col, b.col, ....
from (select id from a union id from b) x
left join a on a.id = x.id
left join b on b.id = x.id

USE [HPRS]
GO
/****** Object:  View [dbo].[members]    Script Date: 09/15/2011 16:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 02-JUN-2011
ALTER view [dbo].[members] /*with schemabinding*/ as
select case when e.employeeid is not null then e.employeeId else a.employeeId end as memberId,
  left(case when e.employeeId is not null then e.socialSecurityNo else a.socialSecurityNo end,11) as socialSecurityNo,
-- GENERAL
  case when e.employeeId is not null then e.memberName else a.memberName end as memberName,
  case when e.employeeId is not null then e.firstName else a.firstName end as firstName,
  case when e.employeeId is not null then e.middleInit else a.middleInit end as middleInit,
  case when e.employeeId is not null then e.lastName else a.lastName end as lastName,
  case when e.employeeid is not null then e.employeeId else a.employeeId end as employeeId,
  case when e.employeeId is not null then e.laborClass else 'ACT' end as memberType,
  e.userDefinedField1 as retireeId,
  e.contactRelation as relationToRetiree,
  case when e.employeeId is not null then e.sex else a.sex end as sex,
  case when e.employeeId is not null then e.jobTitle else a.rank end as positionTitle,
  a.jobCode,
  case when e.employeeId is not null then convert(int,e.userDefinedField2) else a.district end as district,
  a.post,
  a.unit,
  case when e.employeeId is not null then e.class else convert(varchar,a.class) end as class,
--
-- attribute settings
--
  case when e.employeeId is null or e.corporateOfficer is null then 0 else e.corporateOfficer end as estateSurvivor,
  case when a.disabled is not null and a.disabled = 1 then 1 else 0 end as activeDisabled,
  case when e.employeeId is not null then e.exemptFromOvertime when a.disabled = 2 then 1 end as onDutyDisability,
  a.diedOnDuty,
  a.terminateReason,
  --e.seasonalEmployee as optionBeneficiary,	-- indicator on DEP/BEN record, no longer needed
  case when e.employeeId is not null then e.boardMember else a.boardMember end as boardMember,
  e.freeDues,
  e.smoker,
  e.spouseOK,
  e.no1099,
  e.DROPforfeitOverride,
  e.newsLetterDelivery,
  e.checkstubDelivery,
--
-- KEY DATES
--
  a.MilitaryServiceStart,
  a.militaryServiceEnd,
  case when e.employeeId is not null then e.birthDate else a.birthDate end as birthDate,
  (datediff(m,case when e.employeeId is not null then e.birthDate else a.birthDate end,getdate()) / 12) age,
  case when a.socialSecurityNo is not null then a.hireDate else e.adjustedHireDate end as hireDate,
  a.DROPdate,
  e.userDefinedDate6 as DROPwithdrawEligDate,
  e.userDefinedDate4 as DROPtermDate,
  a.retireDate as lastDayOfService,
  e.userDefinedDate3 as DeferRetirementDate,
  e.startDate as pensionStartDate,
  e.userDefinedDate1 as COLdate,
  e.userDefinedDate2 as ExtBenfitsEligDate,
  -- e.userDefinedDate5 as firstPensionPayment,
  e.terminationDate,
  a.deceasedDate,
  e.exclusionStartDate,
--
-- CONTACT INFORMATION
--
  case when e.employeeId is not null then e.addressLine1 else a.addressLine1 end as addressLine1,
  case when e.employeeId is not null then e.addressLine2 else a.addressLine2 end as addressLine2,
  case when e.employeeId is not null then e.residentCity else a.residentCity end as residentCity,
  case when e.employeeId is not null then e.residentState else a.residentState end as residentState,
  case when e.employeeId is not null then e.zipCode else a.zipCode end as zipCode,
--
  cast(null as varchar) as paytoAddressLine1, cast(null as varchar) as paytoAddressLine2, 
    cast(null as varchar) as paytoCity, cast(null as varchar) as paytoState, cast(null as varchar) as paytoZip,  
  cast(null as varchar) as mailAddressLine1, cast(null as varchar) as mailAddressLine2, cast(null as varchar) as mailCity, 
    cast(null as varchar) as mailState, cast(null as varchar) as mailZip,  
--
  case when e.employeeId is not null then e.phoneNumber else a.phoneNumber end as phoneNumber,
  e.workPhoneNo as workPhone,
  case when e.employeeId is not null then e.homeEmail else a.emailAddress end as emailAddress,
  case when e.employeeId is not null then e.workEmail else a.workEmail end as workEmail,
  e.cellPhone,
  a.invalidMailingAddress,
  e.noPublishAddr,
  e.noPublishPhone,
  e.noPublishEmail,
  e.noGroupEmail,
  e.emergrncyContact as emergencyContact,
  e.ContactHomePhone as emergencyContactPhone1,
  e.ContactWorkPhone as emergencyContactPhone2,
--
-- WAGE AND PENSION
--
  a.taxedContrib,
  e.originalPension,
  e.salary as pensionAmount,
  e.payPeriodsPerYear,
  convert(money,e.userdefinedfield3) as COLbase,
  e.exclusionAmount,
  e.exclusionMonths,
  o.employeeId as optionMemberId,
  o.optionElected,
  o.optionPercent,
  o.optionYears,
  o.optionAmount,
  e.partialLumpSumAmount,
--
-- TAXES
--
case when f.exemptions is not null and f.exemptions = 99 then 1 else 0 end as doNotWithholdFederal,
case when f.maritalStatus in ('S','M') then f.maritalStatus else null end as federalMaritalStatus,
case when f.exemptions is null or f.exemptions = 99 then 0 else f.exemptions end as federalExemptions,
case when f.extraWithholding is null then 0 else f.extraWithholding end as federalExtraWithholding,
case when f.fixedWithholding is null then 0 else f.fixedWithholding end as federalFixedWithholding,
--
case when s.exemptions is not null and s.exemptions = 99 then 1 else 0 end as doNotWithholdState,
case when s.maritalStat in ('S','M') then s.maritalStat else null end as stateMaritalStatus,
case when s.exemptions is null or s.exemptions = 99 then 0 else s.exemptions end as stateExemptions,
case when s.extraWithholding is null then 0 else s.extraWithholding end as stateExtraWithholding,
case when s.fixedWithholding is null then 0 else s.fixedWithholding end as stateFixedWithholding,
--
case when e.noLocalTax is null then 0 else e.noLocalTax end as doNotWithholdLocal,
--tempGroup2      0
a.comments
from (select case when socialSecurityNo is null then '' else socialSecurityNo end + 
    case when laborClass in ('DEP','ALT','SUR') then employeeId else 'm' end as ssno 
  from osh2011.dbo.tblPaEmpGenInfo
union 
  select socialSecurityNo + 'm' from osh2011.dbo.tblPaActiveMembers) x
left join osh2011.dbo.tblPaEmpGenInfo e on case when e.socialSecurityNo is null then '' else e.socialSecurityNo end + 
    case when e.laborClass in ('DEP','ALT','SUR') then e.employeeId else 'm' end = x.ssno
left join osh2011.dbo.tblPaActiveMembers a on a.socialSecurityNo + 'm' = x.ssno
left join osh2011.dbo.tblPaEmpFedWithhold f on f.employeeId = e.employeeId
left join osh2011.dbo.tblPaEmpStateWithhold s on s.employeeId = e.employeeId
left join (select userDefinedField1 retireeId, employeeId, optionElected,optionPercent,optionYears,optionAmount 
  from osh2011.dbo.tblPaEmpGenInfo where optionElected is not null and terminationDate is null) o on o.retireeId = e.employeeId

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36546145
As weird as this may sound, on my first glance I am wondering if a UNION as the outer query is not best here. I was originally thinking you were doing a FULL OUTER JOIN, but based on your CASE statements, it appears you are just pulling together two row sets to form a larger one and not really needing a JOIN of osh2011.dbo.tblPaEmpGenInfo and osh2011.dbo.tblPaActiveMembers ... but that is just an initial guess without full analysis of code in SELECT.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36546214
And to clarify, for example:
a.socialSecurityNo + 'm' = x.ssno

This may not utilize the index on a.socialSecurityNo because it is having to concatenate it to 'm' first. In the case of osh2011.dbo.tblPaActiveMembers where all you did in UNION was "select socialSecurityNo + 'm' from osh2011.dbo.tblPaActiveMembers" it makes it seem like you could simply do a join of "a" to "x" directly where a.socialSecurityNo = x.socialSecurityNo thus making the columns sargable; however, then I looked and saw it was the same exact table; therefore, you are doing a full table scan in the derived table "x" to get a list of the socialSecurityNo values with 'm' appended to it, then turn around and self-join the table with a condition that nullifies the index and as a left outer join again with no where clause I could discern, so you have another table scan.

You could just simply do this:

select socialSecurityNo + 'm' as socialSecurityNo
     , {other columns you want}
from osh2011.dbo.tblPaActiveMembers

Maybe I am missing something, but that is just one point that will help if I am correct. Beyond that, the execution plan may reveal other areas to tune.
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 36554900
I'm going to look at that SSNO+'m" more carefully. I think I don't need that anymore. Anyway, I did manage to get the "show actual execution plan" working. I created a unique index on osh2011.dbo.tblPaActiveMembers.employeeId. That seemed to do the trick on this table. Like I said, I'll double check for the other efficiencies you suggested, but this view will soon be replaced by a real table when we've completed the migration, so perhaps it's "fast enough" for the moment.

Thanks, I think I'll consider this issue a wrap.

Before I close out the question, you wrote, "I am logged into a Linux system at the moment so using a different tool other than SQL Management Studio." Could you tell me what tool you are using? I'd love to use a Linux based tool!!!!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36554993
RazorSQL. It is not free, but trying it out. I think TOAD has a package for SQL Server also. RazorSQL is the only one I have tried so far and it works for developing in SQL (haven't tried heavy administration through it). ;)
0
 
LVL 1

Author Closing Comment

by:jmarkfoley
ID: 36710017
Thanks for all the tips!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now