Geek asks and answers

SQL Server Query Grouping

This question is not about grouping in SQL.

Suppose the application server is located between the application UI and SQL Server. This server, of course, executes SQL queries on SQL Server. For each such request, there is some non-trivial overhead. I am curious if there is a way to group multiple requests and send them together, reducing communication costs.

For example, the server wants to make requests such as

Select * from teams...

and

Select * from users...

and instead of processing them separately, it will send something like List<sqlRequest>and get it back List<sqlResponse>(of course, transparent to the programmer).

In my specific case, I am using SQL Server. More generally, is there any SQL Server / SQL mapping database? Is (will) the performance gain caused by this worth the effort in general?

+3
source share
1 answer

You can achieve better performance if

  • The second result set is based on the first
  • First set of road results to create

Consider the following

CREATE PROC GetTeamsAndUsersForACity(@CityId Int)
BEGIN       

   DECLARE @Teams as Table (TeamId int, TeamName varchar(10))

   INSERT into @Teams
   SELECT TeamId, TeamName
   FROM 
         Teams
   WHERE       
         CityId = @CityID


   SELECT TeamId, TeamName FROM @Teams

   SELECT
          UserId, UserName, TeamId
   FROM
          Users 
   WHERE
          TeamId in (Select TeamID FROM @Teams)
END

Notice how we reuse @teams to get users and related commands without querying the command table.

You can achieve these result sets in other ways. For example, you can receive commands from the first result, and then pass this SQL Server to the second set.

, . WHERE TeamId in (Select TeamID FROM Teams where CityID = @CityID).

select * from teams inner join users....Where city id= @cityid, .

, , .

GetTeamsAndUsersForACity . , .NET,

ORM,

+1
source

More articles:


All Articles