performance difference between Query and View.

Question

Post reply

performance difference between Query and View.

AJ-700139

SSC Journeyman

Points: 90
More actions
July 10, 2008 at 4:22 am

#209676

Can anybody help to find the performance difference between Query and View.
What is the performance difference when "where " clause is used with Query and the View which is using the same query.
Which one will be faster.
Thanx in advance.
AJ

Viewing 5 posts - 1 through 5 (of 5 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

A VIEW is just a stored query.

If you create a view and run the same query, the performance will be the same.

WayneS SSC Guru Points: 95461 More actions · Answer 2

A view would be pre-compiled; therefore it could potentially be faster than a query which would need to be compiled. In practice, you may not be able to perceive a difference.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

AJ-700139 SSC Journeyman Points: 90 More actions · Answer 3

Thank you Michael and WayneS for the replies.

Actually i've written a large query in a view which fetches almost 1million records.

and if I apply filters to that query then that fetches 10,000 records.

my question is that if i apply the same filters to that view

then what will be the scenario

whether after filtering the view

i.e.

select * from vwProducts

where producttypeid = 10

will fetch 1million records first and then it will filter down the to 10,000 records.

if this is the case then the performance of the view should not be good.

So what will be the case.

Thanks in advance.

AJ

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

It will not get the huge data set and then filter it down. The database engine will optimize that in the same way it would optimize using a sub-query.

My suggestion is that you try it and look at the execution plans.