Hello Everyone,
Regarding stored procedures and views, I know that stored procedures
cause SQL Server to create a cached execution plan. Is the same thing
done for views? Also, how bad is the performance hit for a stored
procedure that use 1 or a few views as opposed to re-creating the same
select statement with the proper joins to the required tables?
I know that there are a bunch of variables that affect this stuff, I
just trying to get a ball park idea of how this stuff works.
Thanks,
Frank
4  1294 
There is no performance benefit to using stored procedures as opposed
to views; as of SQL Server 7, the optimizer caches all SQL plans the
same way. However, if your queries are highly paramaterizable (as most
stored procedures are), then the likelihood increases that a plan will
get re-used. The optimizer should also process a view in the smae
method as your SELECT statement, so there should be no performance
difference there.
Of course, if you use nested views, you may get some benefit by using
parameterized SELECT statements to filter your data at the lowest level
of the nesting structure, rather than using the views themselves.
HTH,
Stu
Frank Villasenor (ja*****@gmail. com) writes: Regarding stored procedures and views, I know that stored procedures
cause SQL Server to create a cached execution plan. Is the same thing
done for views? Also, how bad is the performance hit for a stored
procedure that use 1 or a few views as opposed to re-creating the same
select statement with the proper joins to the required tables?
Views are essentially macros that are expanded, and the plan is built for
the expanded query.
As Stu says, SQL server caches plans for stored procedures as well as
ad hoc-statements.
The performance hit there could be for using a view, rather than a direct
SELECT is that the view refers to tables that the query at had does
not need.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
stored procedures can be significantly faster then views.
teh execution plan is relatviely trivial. having an EFFICIENT execution
plan given the known variables can alllow the stored procedure to be
much faster then the view, where the known variables are not known
until execution.
IMO, views suck.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Lorax |
last post by:
I'm on the IS team of a medium-sized non-profit with international
reach. We're trying to make some decisions regarding our Web server
and database server as we expand our web site to have more dynamic
content. Currently the database server houses all data pertinent to
the organization (membership data, events, products, etc) in one
database (~2.2 GB) as well as the web site content in a separate
database (~40 MB). The web site pulls from...
|
by: Varkey |
last post by:
Dear friends,
I am new to .NET based app development and have a pretty elementary
query, I suppose... I have caught up with the basics of .NET pretty
well, thanks to some Microsoft VB/ASP background and helpful
colleagues...
The query goes...
In a DOTNET based desktop application environment, does it make sense
to have each copy of the installed application from the different
|
by: Marc Melancon |
last post by:
Will the next release of SQL Server 2000 64bit sp provide performance
counter?
MarcM
|
by: David W. Fenton |
last post by:
A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can't conceive of them adding more than than
10s of thousands of records, which won't change the current
performance profile at all).
If there is a SQL...
|
by: diskoduro |
last post by:
Hi!!
Years ago I built a database to control the production of a little
factory.
The users wanted to work in a Windows Net workgroup so I created an
mdb with all the tables and data an after that every user had his own
mde linked to the main mdb.
Year by year, progressively the number of records has been growing and
the speed of the application has decreased strongly, because of the
grrrreat number of records stored.
| | |
by: Daniel Walzenbach |
last post by:
Hi,
I have a web application which sometimes throws an “out of memory”
exception. To get an idea what happens I traced some values using performance
monitor and got the following values (for one day):
\\FFDS24\ASP.NET Applications(_LM_W3SVC_1_Root_ATV2004)\Errors During
Execution: 7
\\FFDS24\ASP.NET Apps v1.1.4322(_LM_W3SVC_1_Root_ATV2004)\Compilations
|
by: Jan |
last post by:
Hi:
I have an Access database that's been running (in one form or another)
for a couple of different clients for a few years. Now a new client has
requested that it be implemented with a SQL server back-end. I'm doing
my best to learn about SQL server, and I plan to leave the front-end
more or less as-is, just linking to the SQL server back end, but here's
a basic question:
The db has a front-end linked to two back-ends. One of the...
|
by: dorpnospam |
last post by:
We have an old but very critical application that was written in VB 6
against Access 95 dbs. We need to ditch this decrepit old unstable db
platform but we are trying to determine the best migration path to SQL
Server.
I am researching options. I started by upgrading a sample db to Access
2003 and update the VB 6 app to use DAO 3.6. That works well.
Next I tried upsizing the application to SQL Server and that works as
well but it...
|
by: Coldfire |
last post by:
Since i cannot show the differences in a two-column like table. I am first putting
MS SQL Server 2005 and then MySQL 5.x.
MS SQL Server 2005
Brief Overview
- SQL Server is a full-fledged database system developed specifically for large enterprise databases. All advanced features of a relational database are fully implemented.
- Once you purchase the product, you are only limited to the Sybase-derived engine.
|
by: kodart |
last post by:
Introduction
Performance is the main concern to most server application developers. Thats why many of them anticipate using .NET platform to develop high performance server application regardless of the security features it provides.
Microsoft Windows provides a high performance model that uses I/O completion port (IOCP) to process network events. IOCP provides best performance, but difficult to use due to lack of good code samples and...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
