Help to Tune Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help to Tune Query

I need help to tune this Query I have around one million rows in a Table.
This table has 15 columns, and one Column stores record status in integers.
I need all rows in the tabel which has status columns > 0.
I wrote like this select * from my table where statusColumn <> 0 But this sql takes too much time.
I tried with IN, NOT IN but not luck. Thank you in advance for u r help. Keerthi.
i am inclined to think that a large number of rows meet the SARG criteria, hence the execution plan is a table scan.
there is nothing really wrong with the performance of a table scan,
however, for a large table, SQL might want to get a table lock, which it might not be able to do, hence stays with row locks, which can be expensive.
if it not transaction sensitive, (i would hope that such a query is not)
try the nolock hint, ie,
SELECT * FROM table WITH (NOLOCK) WHERE xx also, if a large amount data is returned, perhaps your network is also slow? is it a full duplex gigabit network?

quote:I need all rows in the tabel which has status columns > 0.
I wrote like this select * from my table where statusColumn <> 0
How about:
select * from my table where statusColumn > 0
If you have index on status column it would be used if you use > 0 condition instead of <> 0.
Thanks for u r tips.
I have tried with NOLOCK option but still dont see that much difference in performance. Here it is additional info i forgot to menction. Table has index on status comulmn with desc order.
Table has total total half a million rows.
Table has only 4000 [four thousand ] rows with > 0.
[So basically i need these 4K rows]
My network data transfer is good. Again Thanks for u r help.

Ok, then create index on status column (with fillFactor let’s say 80, you’ll need to defragment that index regularly) and use where statusColumn > 0 condition.
do u need all the columns to returned f not then instead of * character use specific column name.
It sounds like you not only have an index on this field, but a clustered index to boot. This should help performance with the > 0 query. When you are in query analyser, what is the estimated exectution path? Does it use the index? Does it do a seek or a scan?
Firts off is status > 0 used in the majority of queries run (or for status > or = to anything). If so then you might consider an clustered index if not already. However can you post the DDL of the table and the indexes plus how many unique values are there in status. Need to know all we can about the tables structure to know first what we are dealing with and next the execution plan would be nice so we know what it is doing wrong now.