ASP.NET MVC 3 indexing varchar columns

asp.net-mvc-3 dapper optimization sql-server

Question

I am fairly new to performance testing on SQL Server but I would like to speed up my search queries. I have a table called locations and it is only used for "GET". I have 2 columns called city and state. Users inputs some search criteria into a textbox and then I bring back the information from that location using the following code

var city = textbox.city;
var state = textbox.state;
var search = sqlConnection.Query<location>("Select * from locations where city = @Ucity AND state = @Ustate", new { Ucity = city, Ustate = state }).FirstOrDefault();

Both columns are Varchar(50), would indexing them make the queries faster? Or somehow optimizing my query. I been hearing a lot of mixed answers since these are strings.

Accepted Answer

would indexing them make the queries faster?

Um.... Yes? Since this is an equality match search, non-clustered indexing (presumably a single index spanning state and city in that order) would help hugely if there are a non-trivial number if rows in that table.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why