SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Peformance tuning using view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 03/08/2007 :  09:52:53  Show Profile  Reply with Quote
Guys,

I have a select query which joins mutilple tables, each time when I try search for a record the select query is executed.
Is it advantageous to build a view on top of the select query and do a simple select from the view.

All I can think of is the view is going to save the sql parsing time. Am I right ? any suggestions/inputs on select from multiple tables Vs view would help.

Thanks

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/08/2007 :  10:05:57  Show Profile  Reply with Quote
"is the view is going to save the sql parsing time?"

I don't think so - but someone more knowledgeable than me may have a concrete answer.

If you say:

SELECT Col1, Col2 FROM MyView WHERE Col3 = '123'

then I doubt that the query plan is going to be reused for

SELECT Col1, Col2 FROM MyView WHERE Col3 = '456'

however, if you use a stored procedure, or a parameterized query, along the lines of:

SELECT Col1, Col2 FROM MyView WHERE Col3 = @MyParameter

then there is a good chance that it will be cached (and in this regard it doesn;t make any difference wither you are querying MyView or MyTable1 JOIN MyTable2 ... etc)

Kristen
Go to Top of Page

rlaubert
Yak Posting Veteran

USA
96 Posts

Posted - 03/08/2007 :  10:30:21  Show Profile  Visit rlaubert's Homepage  Send rlaubert a Yahoo! Message  Reply with Quote
A view per say will not reduce the processing time as it must be optimized just like an adhoc query. However, a view does reduce the complexity of the query for the user and because views have a tendency to be used more that adhoc querys the execution plan may already be cached which would reduce processing time.
To get the best performance from a view make sure you have the proper indexes for the joins and the where clauses. That way the optimizer will have the best chance of reducing the execution time and cost.

Raymond Laubert
MCDBA, MCITP:Administration, MCT
Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 03/08/2007 :  11:06:55  Show Profile  Reply with Quote
point taken
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000