March 20, 2007 at 2:40 am
Hi,
i hav a query " select * from members where email='' and password='' " it returns some 100,000 rows.. it takes a long time to return data n its not using indexes too... how can i modify this query to use my index properly..? hav a non-clustered index for email and password...
March 20, 2007 at 2:59 am
100,000 records for same email and password?
Something else is wrong besides long run-time! You should revise your business rules first.
N 56°04'39.16"
E 12°55'05.25"
March 20, 2007 at 5:23 am
Hi Peter,
It is not like dat.. i need to get the rows which doesnt hav any email contact..
And email column is not unique..
March 20, 2007 at 5:42 am
How do you store an email "not there"?
Empty string? NULL?
You already have an index on email column...
N 56°04'39.16"
E 12°55'05.25"
March 20, 2007 at 5:45 am
ya just empty string..
it is givin the results correctly but only issue is the performance..
i hav index in email column..
March 20, 2007 at 9:11 am
100,000 records is just going to take a long time to return especially when you add x number of columns, latency, network traffic, etc. It's also probably going to ignore indexes and use table scans, depending on how big the whole table is and whether or not the range you're selecting is from a clustered index.
That's before you get into, just how selective is the index you're using since 100,000 rows consist of ''. You have to have upwards of 98% selectivity for an index to make a difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2007 at 10:27 am
Did you check your execution plan ?
What is the reason that optimizer does not use indexes ?
Is empty string is majority of records ?
Have statistics been updated recently ?
Did you check sys.dm_db_missing_index_details ? It gives you recommendations on what columns to build indexes.
Are you using sorting ?
You can also try hint FASTFIRSTROWS, it actually selects proper indexes for huge result sets for wide tables and when sorting is used.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply