SQL Server Performance Tuning – Looping inside the query or calling application

By Santhosh N

This explains where to have a loop if at all required to have one to get the desired result from the database.

If you required having a loop for fetching the desired records from the database, it’s always better to have a loop inside the query rather than having a loop from the calling application (like C#, VB, or Java).

The reason is to have these two benefits:


1) Avoid the network round trip between the application and Database servers.


2) Having a complex query with temporary table to have loop inside query, the query optimizer optimizes the query better rather than calling the query in a loop from external application.

Related FAQs

This explains how to approach and check for bottlenecks in the SQL queries.
This explains the various precautions needs to be taken care while indexing and choosing what columns needs to be indexed for better performance.
This explains about using functions with Indexed columns in Sql query
This explains the performance overhead of using NULL values in the columns of the Database table.
This describes the usage of DBCC SHOW_STATISTICS command which is used for performance tuning in the Sql Server.
This explains difference between typed and untyped XML Type in Sql Server.
SQL Server Performance Tuning – Looping inside the query or calling application  (1018 Views)