Filter

masterbaker

Hi,

Since I have so many rows in DB, I try to use filter, say, ID>10, I thought it would be faster, but it is not, any ideas? thx

masterbaker

hi,

Any ideas? thx

richardjm

One thought is that the clustered index on the table isn't related to the ID column so a table scan has to be performed. Have a look at the execution plan for a select columns from table where ID > 0 to see which indexes are being used and how.

That's for starters anyway.

HTH

masterbaker

This is the structure I have:
TableA:
AID (Clustered PK)
some fields


TableB:
BID (Clustered PK)
AID
some fields

Inside the filter, I use AID >= 100 for both tables, it is slow because AID in TableB is not Clustered PK? thx

richardjm

I would have thought that will be the issue in TableB certainly. The server has to scan all the rows to determine if or not a the row has an AID > a certain value. Whereas for TableA it can just get a chunk of the table and use that immediately.