How To Make SQL Server Search Faster - Part Two

In this article, we will discuss how to make faster SQL Server Search - Part Two.

This will be based on one table with 500k records that are filtered to build the weekly schedule. In this post, I'll show one of the things that I did in practice  to load more than 400 records for each day, from Monday to Monday, in less than 2 seconds, in my case already with the HTML embedded. 

 

Consider this table and then select from it,

CREATE TABLE [Accounts](
   [accArea] [int] NULL,
   [accSubArea] [int] NULL,
   [accName] [nvarchar](50) NOT NULL,
   [accCodeID] [int] IDENTITY(1,1) NOT NULL,
   [accDateAccess] [datetime] NULL
);

"Select * from Accounts where accArea=1 AND accSubArea=4 and accName like 'Maria%' ORDER BY accDateAccess DESC;"

What is wrong and what must be done

 

Do not use "*", name the fields that you want to work.

"Select accName, accCodeID from Accounts where accArea=1 AND accSubArea=4 and accName like 'Maria%' ORDER BY accDateAccess DESC;"

Indexes

Your table must be indexed in the same order of the WHERE clause.

CREATE INDEX [IX_Accounts] ON [Accounts]
(
   [accArea] ASC,
   [accSubArea] ASC,
   [accName] ASC,
   [accDateAccess] DESC*
)

*Observe that this field is indexed in descending mode.

 

Ordinate fields by numbers or booleans first in the WHERE clause is correct.

 

CONCLUSION

 

Create indexes as much you need if you have big tables.

 

This applies to relational databases. 

 

Have a nice coding experience.