How To Make Faster SQL Server Search - Part One

Do a small change on database and on your query to make it faster.

In 2007, I worked as an advisor for a Sofware Maker that targeted telecom companies.

The databases to search by an Id or by the customer were very slow.

So, I took a look at the SQL query logs that their tools offered and I visualized the bottleneck. The database with 5 million records was searched using equal “=” in a string field, for example:

select id from Customers where customerName='Jefferson Motta';

What this does in SQLServer Engine i,  it looks for every record until it finds the one equal, but, worse; the table field, ‘customerName’ wasn’t indexed.

 

By default, all searchable fields should be indexed but they weren't in this case.

 

So my advice was:

Create an index for that field.

CREATE INDEX [search1Name] ON [Customers] ([customerName] ASC);

Use “like” instead of equal, “=”.

select id from Customers where customerName like 'Jefferson Motta';

The database used to be accessed by more 400 concurrent users and the hardware was not so good. This small diference in the database/code made the query take fewer than two seconds instead of 90 seconds.

CONCLUSION

The SQL Server engine uses the field's index to search and the "like" command is more optimized for big databases.