CODEWITHSUNDEEP
sql-server-2005
Thomas
Thomas

Reputation: 34208

how to optimize sql server table for faster response?

i found a in a table there are 50 thousands records and it takes one minute when we fetch data from sql server table just by issuing a sql. there are one primary key that means a already a cluster index is there. i just do not understand why it takes one minute. beside index what are the ways out there to optimize a table to get the data faster. in this situation what i need to do for faster response. also tell me how we can write always a optimize sql. please tell me all the steps in detail for optimization.

thanks.

Upvotes: 2

Views: 25723

Answers (4)

Martin Smith
Martin Smith

Reputation: 453837

Irrespective of the merits of returning the whole table to your application that does sound an unexpectedly long time to retrieve just 50000 rows of employee data.

Does your query have an ORDER BY or is it literally just select * from employee?

What is the definition of the employee table? Does it contain any particularly wide columns? Are you storing binary data such as their CVs or employee photo in it?

How are you issuing the SQL and retrieving the results?

What isolation level are your select statements running at (You can use SQL Profiler to check this)

Are you encountering blocking? Does adding NOLOCK to the query speed things up dramatically?

Upvotes: 0

gbn
gbn

Reputation: 432639

If you are running select * from employee (as per question comment) then no amount of indexing will help you. It's an "Every column for every row" query: there is no magic for this.

Adding a WHERE won't help usually for select * query too.

What you can check is index and statistics maintenance. Do you do any? Here's a Google search

Or change how you use the data...

Edit:

Why a WHERE clause usually won't help...

If you add a WHERE that is not the PK..

  • you'll still need to scan the table unless you add an index on the searched column
  • then you'll need a key/bookmark lookup unless you make it covering
  • with SELECT * you need to add all columns to the index to make it covering
  • for a many hits, the index will probably be ignored to avoid key/bookmark lookups.

Unless there is a network issue or such, the issue is reading all columns not lack of WHERE

If you did SELECT col13 FROM MyTable and had an index on col13, the index will probably be used.

A SELECT * FROM MyTable WHERE DateCol < '20090101' with an index on DateCol but matched 40% of the table, it will probably be ignored or you'd have expensive key/bookmark lookups

Upvotes: 1

Lord Peter
Lord Peter

Reputation: 3501

Select only the columns you need, rather than select *. If your table has some large columns e.g. OLE types or other binary data (maybe used for storing images etc) then you may be transferring vastly more data off disk and over the network than you need.

As others have said, an index is no help to you when you are selecting all rows (no where clause). Using an index would be slower in such cases because of the index read and table lookup for each row, vs full table scan.

Upvotes: 1

Davita
Davita

Reputation: 9144

The fastest way to optimize indexes in table is to use SQL Server Tuning Advisor. Take a look http://www.youtube.com/watch?v=gjT8wL92mqE <-- here

Upvotes: 4

Related Questions