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

Archives

RSS InfoQ Feeds

  • Article: Q&A on the Book "Humans vs Computers"
    Author Gojko Adzic has released a book, Humans vs Computers in which he tells stories about the impact of inflexible automation, edge cases and software bugs on the lives of real people. He explains the common mistakes built into the systems and provides advice on how to prevent these mistakes from being built into our systems in the first place. By Shane Ha […]
  • Q&A with Michael Coté on Devops Adoption and his Talk at DevOpsDays NZ
    Raf Gemmail talks to Pivotal’s Michael Coté about obstacles to DevOps adoption and his forthcoming talk at DevOpsDays NZ 2017 By Rafiq Gemmail
  • TensorFlow Serving 1.0 Release Detailed at Google I/O
    Google's Noah Fiedel details new programming model for TensorFlow Serving in a stable 1.0 release. Subject matter addresses common challenges with portability, servablility , and reproducibility improvements. By Dylan Raithel
  • First NetBeans Code Drop Lands at Apache
    Oracle has released the first of three NetBeans code drops to the Apache Incubator. By Matt Raible
  • Article: The Top 10 Adages in Continuous Deployment
    On the basis of discussions at the Continuous Deployment Summit, researchers derived 10 adages about continuous-deployment practices. These adages represent a working set of approaches and beliefs that guide current practice and establish a tangible target for empirical validation. By Chris Parnin
  • Podcast: Joshua Kerievsky and Heidi Helfand on High Performance via Psychological Safety
    In this podcast Shane Hastie, Lead Editor for Culture & Methods, spoke to Joshua Kerievsky, CEO of Industrial Logic, and Heidi Helfand, Director of Engineering Excellence at Procore Technologies and author of the book Dynamic Reteaming, about their talk High Performance via Psychological Safety. By Joshua Kerievsky
  • Spotify and Google Release Forseti GCP Security Tools
    Google has opened up Forseti Security, a set open source tools for GCP security, to all GCP users. The project is the result of a collaborative effort from both Spotify and Google, combining what was originally separate work together into a single toolkit. It aims to automate security processes for developers in order for them to develop more freely. By Andr […]
  • Article: Q&A on the Book SAFe Distilled
    The book SAFe Distilled breaks down the complexity of the framework into easily understood explanations and actionable guidance. It’s a resource for acquiring a deep understanding of the Scaled Agile Framework, and how to implement it successfully. By Ben Linders
  • String Interpolation in Entity Framework Raises Concerns
    One of the new features in Entity Framework Core 2 is the ability to automatically convert interpolated strings into parameterized SQL. Though designed to avoid problems with poorly written SQL, it is feared that it may actually lead to more SQL injection attacks. By Jonathan Allen
  • Podcast: Twitter's Yao Yue on Latency, Performance Monitoring, & Caching at Scale
    Yao Yue spent the majority of her career working on caching systems at Twitter. She created a performance team that deals with edge performance outliers often exposed by the enormous scale of Twitter. In this podcast, she discusses standing up the performance team, thoughts on instrumenting applications, and interesting performance issues (and strategies for […]

 

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.
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: