As an T-SQL Developer, we always tend to wonder why my query takes considerately more time when everything is set on the server including indexes, fragmentation, fill_factor, etc. Well, these things are important for the performance of your query but another important thing that we usually miss is the structuring of the query. Yes, the where clause is equally important and the way we structure it determines our query performance in the long run.
Following are the steps that needs to be followed in sequencing the where clause to best optimize a query:
- Primary key (Bigint, Int, Numeric, etc)
- Foreign key (Bigint, Int, Numeric, etc)
- Primary key (strings and GUIDs)
- Foreign key (strings and GUIDs)
- Other bigint, int and numeric columns
- Date columns
- String columns
- Parameters received by the query
The priority for sequencing the query is that we have to set joins on the numerals first followed by GUIDs then come the dates and finally the strings.
EG: when we join 2 tables on a particular column and specify the where clause we need to specify the primary key columns should be conditioned first followed by foreign key columns then come the integers and other numerals followed by date columns and the string columns finally the parameter variable value that is received from the sources of the query has to be passed to the where clause.
This is a tried and tested best way a query can be optimized for performance after setting up various indexes, pages, fill factors and reducing fragments.
Post a comment if this helps
