>I have a SQL command like this:
>
>

>SELECT Task.Numero AS LXPrimaryKey,Task.Title,RobotTimeFrame.Title_E AS RobotTimeFrame,'' AS PrecisedTime,
>Task.Instance,Task.Class,Task.Method,Robot.Title_E,Server.Title_E AS Server,'' AS Log2,Task.LoopDateStart,
>Task.LoopDateEnd,Member.LastName,Task.AddDate,Member2.LastName AS LastName2,Task.ModDate,Task.Precised,Task.Log
> FROM Task (NOLOCK) 
> INNER JOIN Robot ON Task.NoRobot=Robot.Numero 
> INNER JOIN Server ON Robot.NoServer=Server.Numero
> LEFT JOIN RobotTimeFrame ON Task.NoRobotTimeFrame=RobotTimeFrame.Numero
> INNER JOIN Member ON Task.AddUser=Member.Numero
> INNER JOIN Member AS Member2 ON Task.ModUser=Member2.Numero
> ORDER BY Task.Title 
>

>
>This is pretty simple. There is no where clause. The total number of records is from the Task table and is 43. All those tables are small except Member which is about 10000 records, but that is relatively small in the SQL world.
>
>This takes 1.43 seconds. I took a look at the execution plan and it is not obvious to know where to optimize. It seems I have to create a combo index at the Task level but not sure exactly how to define it. It is more easy where there is a where clause. But, in this case, there isn't. This SQL should return the result immediately.
>
>Anyone would have any suggestion?

I would start from trying to discover which JOIN takes the most time by eliminating JOINs one by one. Recently I had a similar situation (although the query was much more complex). I removed LEFT JOIN and made a temporary table of the rest and then LEFT JOINed with that temp table instead. The query that took originally more than 1 minute, now takes less than a second. The client called that procedure "lightingly fast".

And after that experience I figured out that it's always a good idea to improve the performance gradually to get these comments :)