Some times my query is very SLOW ?

Posted by Sarvesh under Sql Server on 8/21/2009 | Views : 1705 | Status : [Member] | Replies : 3
Some times my query is very SLOW ?. Why its getting very slow some times not every time. Why its happening ? How to avoide ?

Sample:
SELECT * FROM TABLENAME




Responses

Posted by: Pandians on: 8/21/2009 [Member] [MVP] Silver

Up
0
Down
Check the following scenarions :
1. Your table may be LOCKED/BLOCKED by some session/resource.
2.Your table may be heavily FRAGMENTED.
3.Your table may have Complex COMPUTED columns.
4.Your table may have Millions of records(It will use more IO).
5.Your table may have more COLUMNS ( If I used '*' ).
6.Your table may have more COLUMN with BLOB like Type.
7.We can use WHERE Clause with Clustered Index (If required)So that optimizer don't want to search all Datapages.
8.If the Table have any INDEX , The FILLFACTOR may be very HIGH. So that the READING and Posting data into cache operation will wait for some more Free space.

The reasons given above may be a one of reason why your query is Slow.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 8/21/2009 [Member] [Moderator] Silver

Up
0
Down
HI-

To avoid these kinds of situations always create normalize database. it should maintain proper relation like primary key , foreign key and cluster and non cluster index if required. Size of the column also has impact on performance. it should be not more than the required by your application.
Frequently used column should be placed together.

Thanks & Regards
Lakhan Pal Garg

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bubbly on: 10/23/2009 [Member] Bronze

Up
0
Down
Hello,

First of all, never use * in your query rather just specify only fields which you really need. Second, use conditions whenever necessary. Try to filter out values using order by/group by as and when needed.

++
Thanks & Regards,
Deepika

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response