UrbanPro
true
default_background

Learn MS SQL from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors
✕

Search in

10 Cool SQL Optimizations That Do Not Depend On The Cost Model

Profile Photo Hasnain
12/10/2017 0 View Comments0

Today, we don’t want to talk about cost based optimisation, i.e. optimisations that depend on a database’s cost model. We’ll look into much simpler optimisations that can be implemented purely based on meta data (e.g. constraints) and the query itself. They’re usually no-brainers for a database to optimise, because the optimisation will always lead to a better execution plan, independently of whether there are any indexes, or how much data you have, or how skewed your data distribution is.

So, they’re not no-brainers in the sense whether they’re easy for the optimiser teams to implement, but they’re no-brainers in the sense whether they should be done.

These optimisations remove needless, optional work (as opposed to needless, mandatorywork, which I’ve blogged about before).

Where do these optimisations apply?

Most of these optimisations are applied to:

  • Fix mistakes in queries.
  • Allow for reusing complex views without actually executing the entire logic from the view.

In the first case, you could claim: “Well, then fix the stupid SQL already”, but then again, who never makes any mistakes, right?

Specifically, the second case is really cool, as these optimisations allow us to build complex libraries of views and table valued functions, which we can reuse in several layers.

Databases being used:

This post will evaluate 10 SQL optimisations on the 5 most popular RDBMS:

  • Oracle 12.2.
  • MySQL 8.0.2.
  • SQL Server 2014.
  • PostgreSQL 9.6.
  • DB2 LUW 10.5.

These will be the 10 optimisation types:

i. Transitive Closure

ii. Impossible Predicates and Unneeded Table Accesses.

iii. Join EliminationRemoving “Silly” Predicates.

iv. Projections in Exists Subqueries.

v. Predicate Merging.

vi. Check Constraints.

vii. Unneeded Self Join

viii. Predicate Pushdown.

0 Dislike
Follow 3 Loading Spinner
Default Profile Photo

Please Enter a comment

Submit

Other Lessons for You

Rename The Logical Name Of SQL Server Database Files Using T-SQL
Rename the logical name of SQL Server database files using T-SQL: 1. Problem: SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical...
Profile Photo

Java Training Syllabus
Learn JAVA-J2EE Syllabus Core Java - Syllabus aligned to OCA Exam - JDK 8 Object-Oriented Programming (OOPS) concepts: Programming Languages Object Oriented Programming Classes & Objects Pillars...
Profile Photo

SQL Tips (4 to 6)
SQL tips 4:Avoid INDEX, unless you need to retrieve information quickly. Index will slower insert and update data query.The another way is using sub querySelect MAX(salary)FROM employeeWHERE salary IN(Select...
Profile Photo

PowerPivot For Excel
PowerPivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous...
Profile Photo

Migration from other languages to Salesforce
Anyone can easily migrate from other language to Salesforce. People must have skills to understand business logic.
Profile Photo
X

Looking for MS SQL Classes?

The best tutors for MS SQL Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn MS SQL with the Best Tutors

The best Tutors for MS SQL Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more