retrieving data from table with 7 million entries takes time

Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..

Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?

Andy

Hi Andy,

Could you post the DDL please, including the the definintion of the indexes and the query you're running. Having an index doesn't help you if you don't select on fields that are in the index. (And even then....)

Also, pulling all 7 million rows over the network can take some time, especially when the rows are 'wide' or the network is slow. Are you selecting all the rows or just a small subset?

What you can also use is Tools > Database Engine Tuning Advisor in Mamagement studio. I haven't used it, but for these kind of question it could very well help you out.

Regards,

GJ

|||When is used a criteria (WHERE clause) you force engine to use that index that mean speed. When is not, the system make full table scan and it have to go to 1,2,3 , to the 7 million records that need time. So, you have to build or invent an appropriate WHERE clause to speed and resolve all user request.|||

o The following query will get the 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

You can get the missing index details in the following way:

The following query determines which missing indexes comprise a particular missing index group, and displays their column details.

For the sake of this example, the missing index group handle is 24.(You will need to change the handle value with handle values which comes up from the earlier query)

SELECT migs.group_handle, mid.*

FROM sys.dm_db_missing_index_group_stats migs

INNER JOIN sys.dm_db_missing_index_groups mig

ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details mid

ON (mig.index_handle = mid.index_handle)

WHERE migs.group_handle = 24 <<put your handle value here>>

For details on this refer to the following articles:

http://msdn2.microsoft.com/en-us/library/ms345421.aspx

Using Missing Index Information to Write CREATE INDEX Statements

http://msdn2.microsoft.com/en-us/library/ms345405.aspx