How efficient is ti to use join views in a database?
I'm developing an e-commerce system and using join views to join the
product, product category and product review tables and wondering if this
would have any adverse effect on performance.
Thanks in advance 3 3871
Andrew Banks (ba****@nojunkb lueyonder.co.uk ) writes: How efficient is ti to use join views in a database?
I'm developing an e-commerce system and using join views to join the product, product category and product review tables and wondering if this would have any adverse effect on performance.
Views are essentially macros. That is, there are no query plans
associated with views, so views or direct SELECT statements does
not really make much difference.
There is one exception, and that is you create a clustered index
on the view. In this case the view is materialized, and SELECT:s
against the view are likely to be faster than SELECTs against the
table. The price you pay is that updates takes longer time.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thanks Erland
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1... Andrew Banks (ba****@nojunkb lueyonder.co.uk ) writes: How efficient is ti to use join views in a database?
I'm developing an e-commerce system and using join views to join the product, product category and product review tables and wondering if
this would have any adverse effect on performance.
Views are essentially macros. That is, there are no query plans associated with views, so views or direct SELECT statements does not really make much difference.
There is one exception, and that is you create a clustered index on the view. In this case the view is materialized, and SELECT:s against the view are likely to be faster than SELECTs against the table. The price you pay is that updates takes longer time. -- Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
FYI
Sometimes when a view is used more then once in a query,
a better plan is generated for the query then when no view is used.
Seems to me, the query optimizer is a bit limited/lazy about realizing
various parts of query are the same. But at least one can use a view
to help the query optimizer come to that realization; upon which,
the query optimizer will consider taking advantage of that fact.
Bye,
Delbert Glass
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1... Andrew Banks (ba****@nojunkb lueyonder.co.uk ) writes: How efficient is ti to use join views in a database?
I'm developing an e-commerce system and using join views to join the product, product category and product review tables and wondering if
this would have any adverse effect on performance.
Views are essentially macros. That is, there are no query plans associated with views, so views or direct SELECT statements does not really make much difference.
There is one exception, and that is you create a clustered index on the view. In this case the view is materialized, and SELECT:s against the view are likely to be faster than SELECTs against the table. The price you pay is that updates takes longer time. -- Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: asj |
last post by:
awhile back, eBay decided to switch from a Microsoft/.NET/Windows
architecture on the backend to a J2EE one, which might explain why their
java backend will handle up to 1 BILLION page views a day! the funny
thing was eBay was one of the major case studies for .NET at the
beginning, when there was still some hype about it.
interesting post...
|
by: Mike N. |
last post by:
Hello:
I am new to T-SQL programing, and relativly new to SQL statements in
general, although I have a good understanding of database theory. I'm a
little confused as to the fundamental differences between a view and a
query, or rather, when it is more appropriate to use one vs. the other.
It seems to me that most select queries can be...
|
by: dbtoo_dbtoo |
last post by:
One of the databases has 50 views and when I do a db2look I only get
schema for 40 of them. If I select from the sysviews, I can see all 50
(the text column contains schema for all 10 (missing) views). What's
going on here? Why can't or how can I get the schema for these 10
views.
Aix 5.1 V7.2 EE FP9
Thanks.
|
by: KemperR |
last post by:
Hello Experts outhere,
may be someone can tell me whats going wrong with my ADOX trial.
I have an Access 2002 database with some tables and queries (views)
The code listed below works well up to the point where I want to add
the new view to the views collection. I get Runtime error 3001 which
is telling me
"Arguments are of wrong...
|
by: dbuchanan52 |
last post by:
Hello,
I am building an application for Windows Forms using. I am new to SQL
Server 'Views'. Are the following correct understanding of their use?
1.) I believe a view can be referenced in a stored procedure something
like this;
Select * from View1
Is that true?
| |
by: rod.weir |
last post by:
Fellow database developers,
I would like to draw on your experience with views. I have a database
that includes many views. Sometimes, views contains other views, and
those views in turn may contain views. In fact, I have some views in
my database that are a product of nested views of up to 6 levels deep!
The reason we did this was.
...
|
by: Peter |
last post by:
People are telling me it is bad to put
select * from <atable>
in a view. I better should list all fields of the table inside the
definition of the view.
I dont know exactly why but some say:
A select * from makes sql server does a table scan.
|
by: extrudedaluminiu |
last post by:
Hi,
Is there any group in the manner of the C++ Boost group that works on
the evolution of the C language? Or is there any group that performs an
equivalent function?
Thanks,
-vs
|
by: Gary |
last post by:
Hello guys!
Bear with me, I am a newbie.
She is the Data Warehouse manager. She has about 50 users to use the
Oracle database from M$ Access via ODBC connection. All those users
have only SELECT privileges on certain tables. I built all the roles
and users for them and they work fine.
Then she asked "Why do YOU let them see all those...
|
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, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
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.
| |