📒
Notes
Cloud ComputingData Science/AIGame Development
  • Home
  • Big O
  • Data Structures & Algorithms
    • Data Structures
      • Array
      • Stack
      • Queue
      • Linked List
      • Binary Tree
    • Algorithms
      • Searching
      • Sorting
      • Graphs
        • Searching
        • Minimum Spanning Tree
        • Shortest Path Algorithms
      • String Algorithms
  • Object Oriented Programming
  • Languages
    • HTML/CSS
      • CSS
    • C++
    • C#
      • Types
      • Keywords
        • Modifiers
          • Access Modifiers
        • Method Parameters
      • Operators and Expressions
      • Collections
      • Constructors
      • Delegates
      • Indexers
      • Concepts
      • Features
        • LINQ
          • Operators
          • Working with Data
          • Methods
          • Resources
        • Asynchronous Programming
        • Reflection
    • Dart
    • GraphQL
    • JavaScript
      • Variable and Parameter
      • Built-in objects
        • Array
        • Built-in Functions
      • Functions
      • Classes
      • Prototype
      • Libraries
        • jQuery
        • React
          • Components
          • State and Lifecycle
          • Hooks
            • useState
            • useEffect
          • Resources
      • Testing Framework
      • Web APIs
    • Kotlin
      • Basics
    • Python
      • Basics
      • Data Structures
      • Functions
      • Resources
        • Flask
    • SQL
      • Basics
      • Operators
      • JOINs
      • Aggregations
      • Subqueries
      • Views
      • Functions
        • Window Functions
      • Stored Procedures
      • Performance Tuning
      • Extras
    • Resources
  • 🌐Web Frameworks
    • Angular
      • Templates
      • Directives
        • Attribute Directives
        • Structural Directives
    • ASP.NET
      • Fundamentals
        • Dependency Injection
        • Middleware
        • Session & State Management
      • Web apps
        • MVC
          • Controllers
            • Filters
          • Models
            • Model Binding
            • Model Validation
          • Views
            • Tag Helpers
            • View Components
          • Features
        • Client-side development
      • Web APIs
        • Controller-based APIs
        • Minimal APIs
        • OpenAPI
        • Content Negotiation
      • SignalR
      • Host and Deploy
        • IIS
      • Security
    • Django
      • The Request/Response Cycle
    • Terminologies
      • Web Server
        • Internet Information Services
    • Resources
  • 📱App Frameworks
    • Introduction
      • Resources
    • Xamarin
      • Lifecycle
      • Custom Renderers & Effects
      • Behaviors
      • Triggers
      • Gestures
      • Commands
      • Dependency Service in XF
      • Libraries
      • Showcase
    • .NET MAUI
      • Controls
      • Navigation
      • Storage Options
  • Multi-Platform Frameworks
    • .NET
      • .NET Framework
        • ADO.NET
        • WCF
      • Fundamentals
        • Logging
        • Testing
      • Advanced
        • Asynchronous Programming
        • Parallel Programming
        • Threading
        • Memory Management
          • Garbage Collection
    • Flutter
  • Object-Relational Mappers
    • Entity Framework
      • Application Models
      • Configuration
      • Setting Up
      • Advanced
  • Databases
    • Introduction
      • DBMS Architecture
      • Normalization
      • Database Transaction Models
    • Relational Databases
      • Microsoft SQL Server
        • Basics
        • Functions
        • Stored Procedures
        • Error Handling
        • Log Shipping
        • Querying and Manipulating JSON data
        • Statements
        • Topics
        • Extras
    • Non-Relational Databases
      • MongoDB
      • Redis
        • Data Structures
        • Introduction
        • Managing Database
  • Tools
    • Version Control
      • Git
        • Setup and Config
        • Basics
          • Sharing and Updating Projects
        • Resources
      • Perforce Helix
    • GitHub
    • Powershell
  • Software Development
    • Software Development Life Cycle
    • Software Design Patterns
      • GoF Design Patterns
      • Architectural Patterns
        • MVC
        • MVVM
        • N-tier Architecture
        • Onion Architecture
        • Data Transfer Objects
      • CQRS
    • Software Design Principles
      • S.O.L.I.D. Priniciple
  • System Design
    • Topics
      • Load Balancing
  • Topics
    • JWT
    • Caching
      • Static vs Dynamic Caching
    • OSI model
      • HTTP
    • Glossary
    • API
      • SOAP
      • REST
    • Microservices
    • WebHooks
    • Practice
    • Operating Systems
      • Windows
    • Architecture
  • 🔖Bookmarks
  • 🔗Resources
Powered by GitBook
On this page

Was this helpful?

  1. Languages
  2. SQL

Performance Tuning

One way to make a query run faster is to reduce the number of calculations that need to be performed. Some of the high-level things that will affect the number of calculations a given query will make include:

  • Table size

  • Joins

  • Aggregations

Query runtime is also dependent on some things that you can’t really control related to the database itself:

  • Other users running queries concurrently on the database

  • Database software and optimization (e.g., Postgres is optimized differently than Redshift)

The second thing you can do is to make joins less complicated, that is, reduce the number of rows that need to be evaluated. It is better to reduce table sizes before joining them. This can be done by creating subqueries and joining them to an outer query. Aggregating before joining will improve query speed; however, be sure that what you are doing is logically consistent. Accuracy is more important than run speed.

Adding the command EXPLAIN at the beginning of any query allows you to get a sense of how long it will take your query to run. This will output a Query Plan which outlines the execution order of the query. The query plan will attach a cost to the query and the higher the cost, the longer the runtime. EXPLAIN is most useful to identify and modify those steps that are expensive. Do this then run EXPLAIN again to see if the speed/cost has improved.

PreviousStored ProceduresNextExtras

Last updated 2 years ago

Was this helpful?