SQL VIEW SCHEMABINDING

Will creating a VIEW with SCHEMABINDING for the following query help improve data access performance?
If so, how would the VIEW be created and which columns in that view are best used as a clustered index?

The columns pr.ParticipantID, pr.RaceID, ir.ParticipantID, ir.RaceID, and p.ParticipantID are clustered indexes in their base tables.

SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St
FROM Participant p
JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID
JOIN PartRace pr ON pr.RaceID = ir.RaceID AND pr.ParticipantID = p.ParticipantID
WHERE ir.RaceID = " & lRaceID & "  AND ir.FnlTime IS NOT NULL AND ir.FnlTime > '00:00:00.000' AND p.Gender = '" & sGender & "' ORDER BY " & sOrderBy
SolveigKCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
re:> that view are best used as a clustered index?

This depends what columns  users most likely search data with,

What fields are PKs?
pcelbaCommented:
You should test the standard view first. Before you do it create additional indexes on pr.RaceID and ir.RaceID beause existing multicolumn clustered indexes cannot be used to fully optimize the second JOIN and also the WHERE clause.

You may also remove   AND ir.FnlTime IS NOT NULL  because  ir.FnlTime > '00:00:00.000'   covers just not null values.

If the result will still be slow test the view with schema binding and new index.

The clustered index on the view should be based on RaceID, ParticipantID as it seems to be the unique key and it also helps to optimize the WHERE clause for the queries.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SolveigKCAuthor Commented:
eghtebas,
The PK's are on p.ParticipantID, pr.PartRaceID and ir.IndRsltsID.
 ir.IndRsltsID is also a FK

pcelba,
I need to test the view with schema binding and new index. Should I still add non-clustered(?) indexes to pr.RaceID and ir.RaceID in the base tables?
Would I want the index in the schema bound view on whatever column is being queried? if so, how would that query look?

Also, I'm not using SQL Server Enterprise. I'm using SQL Express, Will the query optimizer consider the VIEW when executing the original query or do I have to force it by using a NOEXPAND an a separate query of the VIEW?

Thanks
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

pcelbaCommented:
If you need to test the view with schema binding then you don't need the indexes on RaceID columns as they are not directly used. BUT you should look at the query plan to verify above sentence. I am not sure.

You don't need to create indexes on queried view columns (columns after the SELECT keyword). You should consider indexes on columns used in the WHERE clause, i.e. FnlTime. You don't need index on the Gender column as its filtration capability is low obviously.

The EXPRESS version does not support automatic NOEXPAND hint implementation so you may use it whenever you decide it would give better results. Query plan should tell more.

BTW, how many records are in your tables used for the view definition? A few thousands of records do not need optimization by schema binding.
slightwv (䄆 Netminder) Commented:
You asked this in an Oracle Zone and Oracle doesn't have a SCHEMABINDING option with views.

Please verify you have asked this in the correct Zone.

If this is an Oracle question, then what exactly are you asking since you cannot use SCHEMABINDING.
pcelbaCommented:
This author's comment states: "I'm not using SQL Server Enterprise. I'm using SQL Express".

I would agree to change the zone to SQL Server.
Scott PletcherSenior DBACommented:
First, make sure the clustering indexes are:
pr ( RaceID, ParticipantID ) --in that order
ir ( RaceID, ParticipantID ) --in that order

You may want to switch to a table-value-function rather than a view so that the conditions can be considered before processing the query.  That is, if you create a view, it will have to be just:

SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St
FROM Participant p
JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID
JOIN PartRace pr ON pr.RaceID = ir.RaceID AND pr.ParticipantID = p.ParticipantID

SQL would process the WHERE conditions only after all the rows had been read from the underlying tables.

Instead, you need SQL to first limit the rows by RaceID, then process the rest of the query.
Vitor MontalvãoMSSQL Senior EngineerCommented:
SolveigKC, do you still need help with this question?
pcelbaCommented:
My closing recommendation: Split points
https:#a40868977
https:#a40869261
https:#a40870455
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.