CODEWITHSUNDEEP
sqlsql-serverperformancet-sqldatabase-performance
Thiago Custodio
Thiago Custodio

Reputation: 18387

T-SQL multiple select statements performance

I need to execute about 20 select statements to get information for my app. I'm grouping the result sets into one, but I'm not sure if there's a better way to solve this (a more performatic way).

I'm following this approach:

SELECT TB_A.QTD,
TB_B.QTD
FROM
(
    SELECT COUNT(1) AS QTD
    FROM TB_A WITH(NOLOCK)
    WHERE TP = 2
    AND A = 1
    AND N = @N
) TB_A,
(
    SELECT COUNT(1) QTD
    FROM TB_B WITH(NOLOCK)
    WHERE G = @G
    AND U = @U
) TB_B,
...other 18 statements

PS: some variables like @n, @g and @u are parameters informed by user.

Upvotes: 0

Views: 1297

Answers (4)

cristian v
cristian v

Reputation: 1063

To decrease the execution time, if all the queries are independent as it seems, you could run them in parallel from your application but it would load much more the server.

Upvotes: 0

SQL Police
SQL Police

Reputation: 4196

I second @GordonLinoff. Alternatively, you can write it like this - but performance will be the same.

SELECT 
(
    SELECT COUNT(1) 
    FROM TB_A WITH(NOLOCK)
    WHERE TP = 2
    AND A = 1
    AND N = @N
) QTD_A, 
(
    SELECT COUNT(1) QTD
    FROM TB_B WITH(NOLOCK)
    WHERE G = @G
    AND U = @U
) QTD_B,
...other 18 statements

...Note, there is no FROM !

Upvotes: 1

Brent D
Brent D

Reputation: 908

I've found that a single huge query is typically bad for performance and that breaking it down into more manageable parts can help. Adding any covering or filtered indexes on what you're filtering by on the individual tables should help as well.

Declare @AQTD int,@BQTD int

Select @AQTD=COUNT(1)
FROM TB_A WITH(NOLOCK)
WHERE TP = 2
AND A = 1
AND N = @N

Select @BQTD=COUNT(1) 
FROM TB_B WITH(NOLOCK)
WHERE G = @G
AND U = @U

SELECT @AQTD,@BQTD

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your queries are fine (I would use an explicit CROSS JOIN rather than ,, because I abhor commas in the FROM clause).

For performance, two indexes would help: tb_a(TP, A, N) and tb_b(G, U).

Upvotes: 4

Related Questions