Suppose I have two similar queries with complex joins. Query1 returns rows and columns, and Query2 returns only the number of rows in Query 1.
var mySearchSql = @ "SELECT col1, col2, col3, ... etc
FROM MyBigTable
CONNECT LINKS AnotherTable1 On ... etc
LINK LINKS AnotherTable2 On ... etc
WO someCol1 = val1
AND someCol2 = val2
And ETC ...
";
var myCountSql = @ "SELECT count (MyBigTable.PKcol)
FROM MyBigTable
CONNECT LINKS AnotherTable1 On ... etc
LINK LINKS AnotherTable2 On ... etc
WO someCol1 = val1
AND someCol2 = val2
And ETC ...
";
A self-evident logic tells me that the SQL engine (from most databases, such as SqlServer, MySql, IBM DB2, whatevs …) returns from the FASTER count query
as the result set query; AT LEAST IF WE SPEAK GREAT RESULTS.
Of course, it takes longer to transfer many rows and columns across a network. as just a single scalar count!
My questions are (a) Does the DB engine have a comparable overhead for both queries?
and (b) therefore, the bottleneck (or delay in receiving the results) is simply due to the transmission of larger data over a network?