SQL-Server

Quick Guide: Query Optimization Tips

Raviraj Patel
By Raviraj Patel
Oct 11, 2019
Quick Guide: Query Optimization Tips

The most underrated but most important topic, which is a must while implementing the SQL Query, Stored Procedures, or Functions. While implementing any SQL operations knowing the syntax and structures is a good thing, but one must know optimization. Without knowledge of optimization, any developer can create DDL and DML statements, but they are not well-designed procedures. You know why? Because while executing those statements there are chances that it will take time or may create a deadlock situation. Proper joining is also considered to be part of optimization.

This above is an actual execution plan flowchart.

The most simple query execution flow is mentioned below:

  1. From

  2. Joins

  3. Where

  4. Group by

  5. Having clause

  6. Column list

  7. Distinct

  8. Order by

  9. Top

  1. From: First it will fetch all the records from the table mentioned after the ‘From’ keyword.

  2. Join: joins are an essential part of any SQL statements. A developer must have proper knowledge of tables; otherwise, it can cause the wrong data population of data or extended execution time.

  3. Where: It another filter applied to any query after applying joins. It is used to decrease no. of records provided filter wise.

  4. Group by: It is used for grouping the records with aggregate functions or grouping the records particular provided column-wise.

  5. Having: When we need to provide an aggregate function with a filter then we should use it in the ‘Having’ clause.

  6. Column list: While putting ‘*’, we are calling all the columns and all records from a particular table. If not necessary then we must provide only those columns which are actually useful.

  7. Distinct: It is used to remove duplicate records while fetching the details through a select statement.

  8. Order by: This is useful to sort the data ascending or descending column-wise.

  9. Top: It is used to limit the no. of records to be displayed on the screen.

Above mentioned query execution flow is 1st step of the optimization ever keyword should be placed as per the above plan. The last 3 steps (7, 8, and 9) are most crucial because it will process all the records and then do operations accordingly.

‘Group by’ and ‘Having’ clauses are also taken time while execution because it uses aggregate functions in it. Along with that, it is necessary for you to know that if not necessary then don’t do for inbuilt functions. ‘Convert’ and ‘Cast’ is the most frequent built-in functions that can extend the execution because of conversion.

As mentioned above Joins are the most important part of any SQL statement because a good join increases the performance where wrong can mislead you.

First of all, while implementing a join please check whether tables are properly indexed or not. Indexing is very important while the creation of a table, a table must have at least one clustered index.
The lessor or no use of temp tables. Temp tables tend to increase the complexities of the query because it increases the continuous use of ‘tempdb’ database. If necessary then create a clustered index on that temp table which increases the performance and doesn’t wait for the temp table to be dropped automatically, drop it when it is of no use.

Go for the execution plan if the query is taking too much time, by seeing the plan we can easily fetch which query or portion of the query is taking the time. The execution plan shows which table used maximum process time from the overall time.

Make your indexes unique using integer or unique identifier which increases the performances. A table must have one clustered key and can have one or more nonclustered keys. Use small data types for indexing.
For the existence of any record don’t dependent on the count statement in the query.

For example, Always use ‘with (nolock)’ keyword to avoid locks while fetching records from the table. Avoid the use of ‘NOT IN’ statement in where condition, instead of that you can go for let join. In the same way no need to go for the ‘IN’ statement, you can simply use inner join.

Please avoid loops and cursors while creating any store procedure, because looping also causes CPU process usage and calling the same statement again and again. So avoid it is not needed. Use ‘UNION ALL’ instead of ‘UNION’ for combining two or more ‘select’ statements.


 

TAGS SQL
Easy Git Branching Tips & Tricks
GIT
Easy Git Branching Tips & Tricks
Raviraj Patel
Oct 22, 2019

Best practice for the Git Branching and what is the key usage and difference between each. For the Git workflow, branches can be differentiated: Feature: All features/new functions/major refactoring should be done in feature branches, which branch off of and are merged back into the develop branch (usually after some kind of peer review). Release: When enough features have accumulated or the next release time frame comes near, a new release branch is branched off of develop, which is solely dedicated to testing/bug fixing and any cleanup necessary (For instance, altering some path names, instrumentation default values, etc.). Master: Once the QA is satisfied with the quality, the release branch is merged into master (and also back to develop). This is then what is shipped/used by the customers. Hotfix: If a major problem is found after release, the fix is developed in a hotfix branch, that is branched off of the master. Those are the only branches that will ever branch off of master.   Note: Any commit in the master is a merge commit (either from a release or a hotfix branch) and represents a new release that will be shipped to the customer. master and develop branches should be protected branches which will prevent direct commit. All the changes must be incorporate via pull requests only. Please be aware that this model is mainly meant for: big software projects that follow classic release versioning and have a separate QA team Many popular repositories on GitHub follow a simpler model.

Read More
Unlocking Windows 10's New File Explorer
Windows
Unlocking Windows 10's New File Explorer
Raviraj Patel
Oct 14, 2019

It's been ages since we have the same look and feel for the Windows Explorer. There are some updates but the look and feel remained the same for long. However, the Redmond giant is working on a new File Explorer which is based on UWP (Universal Windows Platform). This is hidden but it's been discovered by a Redditor. So, if you want to try out the new File Explorer on Windows 10 then follow the steps below. Open File Explorer. Paste this into the address bar shell:AppsFolder\c5e2524a-ea46-4f67-841f-6a9465d9d515_cw5n1h2txyewy!App Hit Enter. At this point, the new File Explorer should startup. (Optional): Right-click the program and "Pin to Taskbar" for quick access.

Read More
Backup Scheduling in SQL Server Express
SQL-Server
Backup Scheduling in SQL Server Express
Raviraj Patel
Sep 20, 2019

Have you ever attempted to set up an automated backup of your SQL Server Express Edition and found that there’s no SQL Server Agent where you can schedule the job which will took a backup of your database. Alas, the world does not end there and you don't need to pay extra bucks just to have the back up via an SQL Agent which is available only to Standard and Enterprise editions. There are many options to automate the backup job which runs on a specific time and does not require manual intervention. Here, we will learn how to do it via SQL Command using batch file and Windows in-build Task Scheduler. Hope, you may find this useful. Create a BAT(batch) file to execute the command to take a backup of Database and save it. echo off :: -------------------------------------------------- :: clear console cls :: -------------------------------------------------- :: Define variables set SERVERNAME=YOUR_SERVER_NAME set DATABASENAME=DATABASE_NAME set MyTime=%TIME: =0% set MyDate=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2%.%MyTime:~0,2%.%MyTime:~3,2%.%MyTime:~6,2% set FileName=%DATABASENAME%_%MyDate%.bak set BAK_PATH=DIRECTORY_PATH set DEST_FILE=%BAK_PATH%%FileName% :: -------------------------------------------------- :: BACKUP Database sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%DEST_FILE%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT" :: -------------------------------------------------- :: Optional Part :: -------------------------------------------------- :: Zip file 7z a -tzip "%DEST_FILE%.zip" "%DEST_FILE%" :: -------------------------------------------------- :: Delete unziped file DEL "%DEST_FILE%"   “SERVERNAME” is the name of SQL Server physical machine. “DATABASENAME” is the database which will be backup. “FileName” sets as a database name and append date which has .bak extension  “BAK_PATH” is the path in which a database backup file will be saved. “DEST_FILE” is use backup path and file name. After defining all the variables database backup will be generated and save as zip file in “DEST_FILE” path and at the end, the unzipped file will be deleted from “DEST_FILE” Now, it's time to schedule this created batch file in #1 Start Menu -> Task Scheduler -> Run as administrator Click on Create Task... from the right bar and configure it with Triggers and Actions  

Read More
Raviraj Patel

About the Author

Raviraj Patel

A results-driven, customer-focused, articulate and analytical Senior Software Engineer who can think “out of the box”. Strong in design and integration of problem-solving skills. Expert in C#, .Net Core, Web API, ASP.NET, jQuery, SQL Server, SSIS, SSRS.

Skilled in developing business plans, requirements specifications, user documentation and architectural systems research.

RECENT POSTS

CATEGORIES

TAGS CLOUD