Optimizing Query

Sachin VCO

15 pts.

Tags:

Database performance

Query optimization

SELECT statement

Hi, I am having a table which is having around 170 Columns(Don't blame me but my Developer) and there are only 989 records in the table.I created all sorts of Indexes but still the query (Select * from Table) takes almost 2 mins to display 989 records.I need all the columns to be displayed.Is there any way I can make this query to run faster.Thanks In Advance - Sachin

Asked: June 11, 2008 11:30 AM Last updated: July 4, 2008 9:18 AM

Answer Wiki

Last updated: June 11, 2008 11:12 PM GMT

Denny Cherry68,390 pts.
History
Contributors
- Ordered by most recent
- Denny Cherry68,390 pts.

Thanks. We'll let you know when a new response is added.

If you are returning all records adding indexes won’t do you any good. A table scan is being done. How much data is in the table? What is the latency between you and the SQL Server?

Discuss This Question: 3 Replies

Denny Cherry Jun 11, 2008 11:12 PM GMT

Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.

68,390 pointsBadges:

report
Sachin VCO Jun 12, 2008 6:04 AM GMT

There is around 1000 records only.and Latency is Pinging [172.30.5.48] with 32 bytes of data: Reply from 172.30.5.48: bytes=32 time=218ms TTL=122 Reply from 172.30.5.48: bytes=32 time=218ms TTL=122 Reply from 172.30.5.48: bytes=32 time=221ms TTL=122 Reply from 172.30.5.48: bytes=32 time=218ms TTL=122

15 pointsBadges:

report
Ritchie1 Jul 4, 2008 9:18 AM GMT

Try Select * from Table with (nolock) - if this runs faster then the issue is not indexes etc but locking. If it is still slow try defragmenting table and its indexes. Also such a large number of columns shows lack of normalisation and are you sure you need: Select * from table without restricting which columns or using a where statement to restrict which rows? I would look at the whole design issue and try to improve table and query structure.

210 pointsBadges:

report