Use SQL server queries when possible; their performance is better than the Fetch XML queries. When using SQL queries, use stored procedures. They are precompiled in SQL Server and perform better than a query embedded in a report that needs to be interpreted and compiled every time we run the report.
We can also improve the performance of our SQL queries by looking at the execution plan of the query in SQL Server Management Studio.
This is something that can be enabled by clicking on the icon called Display Estimated Execution Plan, as shown in the following screenshot:
With this feature enabled, executing the query will suggest the indexes we might need to create to speed our query, as shown in the following screenshot:
Creating indexes as this tool suggests will improve query performance a lot. For more information about working with this tool, go to http://msdn.microsoft.com/en-us/library/ms190402.aspx.