Question: I have a table with about 20+ million records.

Structure is like:

```
EventId UNIQUEIDENTIFIER
SourceUserId UNIQUEIDENTIFIER
DestinationUserId UNIQUEIDENTIFIER
CreatedAt DATETIME
TypeId INT
MetaId INT

```

Table is receiving about 100k+ records each day.

I have indexes on each column except MetaId, as it is not used in 'where' clauses

The problem is when i want to pick up eg. latest 100 records for desired SourceUserId

Query sometimes takes up to 4 minutes to execute, which is not acceptable.

Eg.

```
SELECT TOP 100 * FROM Events WITH (NOLOCK)
WHERE SourceUserId = '15b534b17-5a5a-415a-9fc0-7565199c3461'
AND 
(
 TypeId IN (2, 3, 4)
    OR 
 (TypeId = 60 AND SrcMemberId != DstMemberId)
)
ORDER BY CreatedAt DESC

```

I can't do partitioning etc as I am using Standard version of SQL Server and Enterprise is too expensive.

I also think that the table is quite small to be that slow. 

I think the problem is with ORDER BY clause as db must go through much bigger set of data.

Any ideas how to make it quicker ?

Perhaps relational database is not a good idea for that kind of data.

Data is always being picked up ordered by CreatedAt DESC

Thank you for reading.

PabloX

Answer: 
We've realised a minor gain by moving to a BIGINT IDENTITY key for our event table; by using that as a clustered primary key, we can cheat and use that for date ordering.
If there are 100K records added each day, you should check your index fragmentation.
And rebuild or reorganize it accordingly.
More info :
[SQLauthority](http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/)