Technology Corner

Home » Database » Common tips to increase performance of Sql Queries

Common tips to increase performance of Sql Queries

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 80 other followers

Twitter updates


RSS InfoQ Feeds

  • First Pedestrian Killed by Self-Driving Car
    A pedestrian was killed on Sunday evening in Tempe, Arizona by a self-driving car operated by Uber, the BBC reports. The firm confirmed that the vehicle was traveling in autonomous mode with a safety driver, the only vehicle occupant, behind the wheel during the crash. By Roland Meertens
  • Microsoft Embeds Artificial Intelligence in Windows 10 Update
    The next Windows 10 update opens the way for the integration of artificial intelligence functionalities within Windows applications. Developers will be able to integrate pre-trained deep-learning models converted to the ONNX framework in their Windows applications. By Alexis Perrier
  • Ankyra Presents “Escape”, a Release Automation Tool that Manages Platforms as Logical Components
    Over the last ten years there has been increased focus on infrastructure as code (IaC) tooling, primarily driven by the rise of Infrastructure as a Service (IaaS) and API-driven infrastructure. InfoQ discussed the challenges of homogenising this tooling with Bart Spaans, founder of Ankyra, who is an expert in the domain of infrastructure and release engineer […]
  • Article: Servlet and Reactive Stacks in Spring Framework 5
    Spring Framework 5 supports both traditional servlet-based and reactive web stacks, in the same server application, reflecting a major shift towards asynchronous, non-blocking concurrency in applications. In this article Spring committer Rossen Stoyanchev explores and contrasts both stacks, and explains the range of available choices, and provides guidance f […]
  • Presentation: Elm and Game Development, a Perfect Fit
    Paulo Diniz discusses the Elm architecture, how to use it as functional reactive programming for web game development. By Paulo Diniz
  • Google Releases “Skaffold”, a Tool That Facilitates Continuous Development with Kubernetes
    Google has released Skaffold, an open source command line tool that facilitates continuous development for Kubernetes applications. Skaffold is entering an increasingly crowded space of Kubernetes development automation tooling, including Azure’s Draft, Datawire’s Forge and Weavework’s Flux. By Daniel Bryant
  • Q&A with Marisa Fagen on Security Championship
    Security lead Marisa Fagen recently spoke at QConLondon 2018 about upskilling and elevating engineering team members into the role of Security Champions. We catch up with Fagen and report on her efforts to address contention caused by a scarcity of security professionals. By Rafiq Gemmail
  • GitHub Licensed Aims to Make it Easier to Comply with OSS Licenses
    GitHub Licensed is an open-source tool that aims to simplify the chore of ensuring license soundness and documentation for all dependencies of a GitHub project. By Sergio De Simone
  • Sauce Labs Adds Analytics and Extended Debugging to Continuous Testing Cloud
    At their recent user conference SauceCon, Sauce Labs introduced new capabilities for its continuous testing cloud including test analytics, featuring a dashboard that analyses test results and exposes common failures by browser and operating system, including Android and iOS. By Helen Beal
  • JavaFX and the Future of Java Client Technologies
    Oracle will remove JavaFX, Applets and Java Web Start from the JDK after Java SE 8. Swing and AWT will remain. By Tim Hodkinson


Below is some very common tips to increase sql queries performance:

  • Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not too high. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
  • Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
  • Try to create indexes on columns that have integer values rather than character values.
  • If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
  • If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
  • Create surrogate integer primary key (identity for example) if your table will not have many insert operations.
  • Clustered indexes are more preferable than non-clustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.
  • If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
  • You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.
  • Avoid Indexing small tables.
  • Index the order by / group by/ distinct columns for better response time.
  • Try to restrict use of Outer join
  • Use Parameterized query because it only compile once.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blogs I Follow

%d bloggers like this: