Zain Shaikh
Zain Shaikh

Reputation: 6043

How do I optimize the following SQL query for performance?

How do I optimize the following SQL query for performance?

select * from Employee where CNIC = 'some-CNIC-number'

Will using alias help making it a little faster?

I am using Microsoft SQL Server.

Upvotes: 2

Views: 356

Answers (3)

Singleton
Singleton

Reputation: 3679

First thing, as I see this column will be used for storing Id card nos, then you can make your coulmn of type int rather than varchar or nvarchar as searching will faster on an integer type as compared to varchar or nvarchar.

Second, use with (no lock), like

select * from Employee with (nolock) where CNIC = 'some-CNIC-number'

This is to minimize the chances of a deadlock.

Upvotes: 1

JNK
JNK

Reputation: 65157

This is better if you tell us what RDBMS you are using, but...

1 - Don't do SELECT *. Specify which columns you need. Less data = faster query

2 - For indexing, make sure you have an index on CNIC. You also want a good clustered index on a primary key (preferably something like an ID number)

3 - You put the number in single quotes ' ' which indicates you may have it as a varchar column. If it will always be NUMERIC, it should be an int/bigint data type. This takes up less space and will be faster to retrieve and index by.

Upvotes: 5

Quassnoi
Quassnoi

Reputation: 425371

Create an index on CNIC:

CREATE INDEX ix_employee_cnic ON employee (cnic)

Upvotes: 2

Related Questions