CODEWITHSUNDEEP
c#sqlsql-server-2008
user1056466
user1056466

Reputation: 667

How to make the Sql query efficent

I am running a sql query on about 2.5 million data but it computes the answer extremely slow.Is there any way to make a query compute the particular answer fast.

My query is

SELECT COUNT(FarmerName) AS Total_Number_Of_Farmers 
FROM try  
WHERE FarmerName <> '-'"

Upvotes: 0

Views: 84

Answers (3)

uvais
uvais

Reputation: 416

create INDEX FarmerNameIndex
ON try (Farmername)

then execute the same query as you have written above.

 select COUNT(FarmerName) Total_Number_Of_Farmers 
 FROM try  
 where FarmerName <> '-'"

Note: try to set the datatype as small as require for father name.

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

In general, Make use of Query Execution Plan option in SSMS. This will suggest you with missed index. Although the suggested missed index is not always trustworthy, by looking at the execution plan you can easily figure out on which column you need to create an index. Try to reduce Index Scans and Table Scans as much as possible. Convert them to Index seek wherever possible. This will improve the performance a lot.

For your query, as @ElectricLlama suggested, applying '<>' would have extra burden. It is always better to use '=', so that it can make use of index on that column.

Upvotes: 1

Tilak
Tilak

Reputation: 30698

Try index on 'FarmerName'

CREATE INDEX FarmerNameIndex
ON try (Farmername)

Also, If this field is NVarchar either change it to varchar, or use WHERE FarmerName <> N'-'"

Upvotes: 3

Related Questions