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

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Archives

RSS InfoQ Feeds

  • How Google Develops New Managers
    Alex Langshur, host of Google Partners Podcasts, has organized the podcast Google HR secrets: identifying & developing great managers, interviewing Sarah Calderon, People Development at Google, on how Google selects, trains, and develops their managers. By Abel Avram
  • Presentation: Cognitive Services, Next Step in Creating Our Robot Overlords
    Harold Pulcher discusses Cognitive Services, how to get started using them, and how to incorporate speech, image, and facial recognition into an application. By Harold Pulcher
  • Presentation: Control Flow Integrity Using Hardware Counters
    Jamie Butler and Cody Pierce discuss a new system for early detection and prevention of unknown exploits. Their system uses Performance Monitoring Unit hardware to enforce coarse-grained Control Flow Integrity (CFI). They intend to prove that their approach is effective and suitable for practical use, while staying resistant to bypass. By Jamie Butler
  • JetBrains Launches GoLand Go IDE
    JetBrains has moved its Go IDE from its early access programme to market. Now branded as GoLand, the IDE extends the IntelliJ platform making its core functionality specific to Go. This follows suit with their other language-specific tools such as PyCharm for Python and RubyMine for Ruby. By Andrew Morgan
  • Panel on the Future of AI
    An SF QCon panel on the future of AI explored some issues facing machine learning today. The areas explored: critical issues facing AI right now, how has technology changed the way people are hired, how non-leading edge companies make the best use of current technologies, what the role of humans in relation to AI is, and exciting new breakthroughs on the imm […]
  • Microsoft Updates Cosmos DB with Cassandra Support and Provides Better Availability Guarantees
    Last month at Microsoft Connect 2017, Azure Cosmos DB received several new updates, including support for using the Cassandra NoSQL database API and increased guarantees for availability. With the Cassandra NoSQL database API, customers can run operations inside Cosmos DB on a data model. The availability guarantee moves from 99.99 percent to 99.999 percent. […]
  • Article: Approximate Computing on WSO2: Explaining Approximation Algorithms in an Applied Setting
    In this article, we describe an example real world application of API monitoring which gets benefit by using approximate stream processing. We developed the application on top of WSO2 Stream Processor as Siddhi extension. Siddhi is the complex event processing library which acts as the event processing engine of WSO2 Stream Processor. By Chamod Samarajeewa
  • Rust in Visual Studio and VS Code
    Daniel Griffen has released a preview version of a Rust language service for Visual Studio. This plugin requires Visual Studio 2017 Preview, an experimental release stream for testing new VS features. By Jonathan Allen
  • Article: Key Takeaway Points and Lessons Learned from QCon San Francisco 2017
    The eleventh annual QCon San Francisco was the biggest yet, bringing together over 1,800 team leads, architects, project managers, and engineering directors. By Abel Avram
  • Article: Q&A With Eberhard Wolff On the Book “A Practical Guide to Continuous Delivery”
    Eberhard Wolff speaks with InfoQ about his work "Continuous Delivery: A Practical Guide", where we detail some of the major concepts behind successful CD adoption and the ripple-effect it can have on developer productivity and quality of service. By Dylan Raithel

 

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: