View performance

Question

View performance

newbieuser

SSCrazy Eights

Points: 9423
More actions
August 10, 2010 at 10:06 am

#238823

Hi friends,
Is there a way to tune this view by anyway?
CREATE VIEW [dbo].[servew] AS
select TOP 100 percent a.create_to,a.to_code,
b.prof_code sh_prof_code,
c.prof_code sl_prof_code,
x.fname sh_fname,
x.mint sh_mint,
x.lname sh_lname,
b.eventid sh_eventid,
y.fname sl_fname,
y.mint sl_mint,
y.lname sl_lname,
c.eventid sl_eventid
fromserv a, shdept b, sldept c, inst x, inst y
where a.to_code = b.to_code and
a.create_to = c.create_to and
b.prof_code = x.prof_code and
c.prof_code = y.prof_code;
When we run a select on the view which returns no records, the cost is very high at 81% for index pk_inst(primary key on inst.prof_code) in inst table.
Please give me your suggestions. Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Hoogendoorn SSC Eights! Points: 843 More actions · Answer 2

Hi There,

Changing the query to explicit INNER JOINs with ON statements might prevent improper execution plan.

In your current query, their might be a possibility that SQL first joins all tables and then applies a filter.

By explicitely using INNER JOIn with ON, you will be sure that the tables are pre-filtered before the are joined. As a result less I/O will be needed.

Hope this will help you out.

Kind regards,

Dave

Dave Hoogendoorn SSC Eights! Points: 843 More actions · Answer 3

By the way,

Also I should get rid of the "TOP 100 percent" if you don't use it.

Kind regards,

Dave

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Dave Hoogendoorn (8/15/2010)
Hi There,
Changing the query to explicit INNER JOINs with ON statements might prevent improper execution plan.
In your current query, their might be a possibility that SQL first joins all tables and then applies a filter.
By explicitely using INNER JOIn with ON, you will be sure that the tables are pre-filtered before the are joined. As a result less I/O will be needed.
Hope this will help you out.
Kind regards,
Dave

Do you have a coded example that shows such a thing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Richard Williams-416852 Old Hand Points: 374 More actions · Answer 5

All depends on how many rows you have in each table, which columns are indexed etc - i agree with previous post - not using INNER JOIN won't be helping

Sometimes SQL Server doesn't go the right way on a join i.e. pick the smallest dataset to start with. Using sub-selects in the SELECT and FROM sections can help it to make the correct decision, but the answer lies in knowing the data, indexes etc.

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 6

Please follow Gail's recommendations and then we dont have to guess...

with the above requested info we can give you an answer very quickly