• Latest
  • All
  • News
  • Business

SQL Server Performance – How to Create Fast Views

Dec 1, 2018
5 benefits of SEO to the online gambling industry

Why SEO Service Experts Are Essential for Your Business

May 31, 2023
Why Do College Students Love Gaming?

12 Addictive Free Online Games That Will Keep You Hooked

May 27, 2023
5 Key Steps to Growing Your Online Business

5 Key Steps to Growing Your Online Business

May 25, 2023

Marijuana legalization law in california

May 22, 2023

How to Optimize Your Printing Workflow With a Desktop Extruder

May 13, 2023
Fundamentals Of Quality Website Design

How UX Design Agencies Can Help You Build a Customer-Centric Website

May 12, 2023
Can a VPN Service Help Your Business?

Securing Your Digital Footprint

May 8, 2023
How to Use Glass in Your Home Renovation Project

How to Use Glass in Your Home Renovation Project

May 2, 2023
How Photobook Software Can Help You Preserve Your Memories Forever

How Photobook Software Can Help You Preserve Your Memories Forever

Apr 26, 2023
Dock Maintenance 101 – Tips and Tricks for Keeping Your Dock in Top Shape

Dock Maintenance 101 – Tips and Tricks for Keeping Your Dock in Top Shape

Apr 24, 2023
The Benefits of Investing in Data Catalog and Metadata Management Tools

The Benefits of Investing in Data Catalog and Metadata Management Tools

Apr 24, 2023
5 Ways You Can Protect Your Personal Data Online

Securing Student Data in an Age of Increasing Digital Education Assessments

Apr 21, 2023
  • Guest Posts
  • Advertise
  • Contact Us
  • Privacy Policy
Thursday, June 1, 2023
Tech Featured
  • Home
  • Latest Tech
  • Apple
  • How to’s
  • Apps
  • Automobiles
  • Gadgets
  • Gaming
  • Reviews
  • Tech Tips
No Result
View All Result
No Result
View All Result
Tech Featured
No Result
View All Result
Home News

SQL Server Performance – How to Create Fast Views

by techfeatured
Dec 1, 2018
in News
0
0
SHARES
14
VIEWS
Share on FacebookShare on Twitter

When views were first explained to me, they were explained incorrectly. I have, since then, heard others regurgitate the same falsehood countless times.

I operated under this false knowledge for years until recently, working with Query Analyzer and actually breaking down the query plans, I saw "the light."

Most of us were taught that Views are slower because the database has to calculate them before they are used to join to other tables and before the where clauses are applied.

If there are a lot of tables in the view, then this process slows everything down. This explanation seems to make sense on the surface, and is there ever easily accepted. However, nothing is further from the truth on SQL Server!

The fact of the matter is that when a query is being broken down by the SQL Server's optimizer, it looks at the fields within the select to see which of them are needed by the consuming query.

If it needs a given field, then it extracts it from the query definition, along with it's table from the FROM clause, and any restrictions it needs from the WHERE clause or other clauses (GROUP BY, HAVING, etc.)

These extracted elements are then merged into the consuming cases and are generally treated as a sub-query. The optimizer then joins the data together along indexes as best it can, just as it does with non-view elements, and then the entire query is run. The view is NOT pre-calculated just because the query chunk came from a view definition.

So, why do views often run slower than the same code typed directly into a query? Three reasons:

Reason 1 – Sort Order: Sub-queries often suffer from not being sequenced in an order that can easily be merged into the main query. This causes the server to do extra work to so the data returned by the sub-query before merging it. In this circumstance, the data is pre-calculated so it can be sorted.

Reason 2 – Inner Joins: When the view is broken down to see what fields on the select are needed, and then the corresponding table from the case, it has to go one step further. It must consider anything in the where clause that may throw out data. As well, Inner Joins from the table in the case cause can also throw out data if the joined in table does not have a matching row. Since the optimizer does not know whether or not the Inner Join was used as a filtering device, it has to include it. Very often, tables are joined in to show data that the consuming query does not need, not just as a filter. In these cases, the Inner Join only causes SQL Server to do more work for no good reason.

Side note: Left Joins are not used as filters. If a view left joins in a table, but there are no fields used in that table, it will be eliminated when the view is dropped in.

Reason 3 – Redundant Tables Calls: When you create a view, you can actually use another view as a source of data. This practice can be nested practically limitlessly. Since each of these views will be have their query definitions dropped in as a Sub-Query, then it's very possible that the same base table will participate in the query multiple times. This is, generally, just a waste. Why go to the same place multiple times?

There you have it. The truth about SQL Servers and 3 reasons why your views may be slowing down the speed of your queries. You can find solutions to these 3 problems at the SQL Coach blog. Simply point your web browser to http://www.sqlcoach.blogspot.com to get the information you need!

Tags: androidimaciphonemacbooktechnology news
ShareTweetShareSendShare

For any queries, you can reach us at [email protected]ail.com

Latest Posts

  • Why SEO Service Experts Are Essential for Your Business
  • 12 Addictive Free Online Games That Will Keep You Hooked
  • 5 Key Steps to Growing Your Online Business
  • Marijuana legalization law in california
  • How to Optimize Your Printing Workflow With a Desktop Extruder
Tech Featured

© 2022. All Rights Reserved.

Navigate Site

  • Guest Posts
  • Advertise
  • Contact Us
  • Privacy Policy

Follow Us

No Result
View All Result
  • Home
  • Latest Tech
  • Apple
  • How to’s
  • Apps
  • Automobiles
  • Gadgets
  • Gaming
  • Reviews
  • Tech Tips

© 2022. All Rights Reserved.