Outline ·
[ Standard ] ·
Linear+
.net app getting sql data slow from network
|
TSnarf03
|
 Nov 14 2023, 09:10 PM, updated 3y ago
|
|
I have a .net app that connect to SQL server machine using gigabit lan, both pc running on the same gb lan switich, tested no problem copying huge file. almost max out gb connection.
by slow mean, it connect to sql server get like 10k rows of data consists of ~30 columns, and put up to datagridview, takes about 10 seconds to complete.
if i run the app directly in the sql server machine, the exact same operation take less than 1 second.
both pc are i7 or better, no 32G ram, nothing in the background hogging any resource.
I wonder if there are anything limiting the speed of data transfer or displaying data i need to be aware of ?
|
|
|
|
|
|
TSnarf03
|
Nov 14 2023, 09:27 PM
|
|
|
QUOTE(soul78 @ Nov 14 2023, 09:20 PM) if both pc got dual network, maybe can try connecting the lan directly bypassing the switch...? hmm, can try that, but will need a cross cable and fixed ip. testing with just ssms, select all from a table that consists or 1.3 mil rows, only take 20 seconds in the machine with sql server running, while from the network, 10 minutes also cannot complete, while the load on network is like only 1%(10mbps) |
|
|
|
|
|
TSnarf03
|
Nov 15 2023, 12:44 AM
|
|
|
figured out 90%, most likely too many layers of switch and maybe some of them have heavy traffic, if just 1 gigabit switch without any other traffic, able to achieve 100mb/s, that alone speed things up by 10x.
wonder if its possible for the sql server to max out gigabit switch capacity ? like go 9x more faster, achieving 900mb/s
|
|
|
|
|
|
TSnarf03
|
Nov 15 2023, 03:18 PM
|
|
|
QUOTE(angch @ Nov 15 2023, 09:34 AM) Gut feeling is that you're grabbing the rows one by one, necessitating a network round trip per row. Fetch all of them in one shot, not row by row. Don't know .net or SQL Server enough, but for Go and PostgreSQL, there's this concept of "single round trip query mode": https://pkg.go.dev/github.com/jackc/pgx/v5#QueryExecModeto slurp all results at once. A little more of a leap, but understanding this: https://colin-scott.github.io/personal_webs...ve_latency.html and the implications allows you to visualize performance bottlenecks better. e.g. in your case, doing best case, one network round trip might be about 0.5ms. 10k rows = 5 seconds already spent sending and waiting for network, at least. It's not about bandwidth. It's about latency. not by row, not using SP, doing something like select * from table1, no filter |
|
|
|
|
|
TSnarf03
|
Nov 15 2023, 03:20 PM
|
|
|
I wonder does it matter if i use computer name / ip v6 / ip v4 ?
or it only matters when connection is established, after that it no longer matter(as long as each query doesnt disconnect)
|
|
|
|
|
|
TSnarf03
|
Nov 16 2023, 12:01 AM
|
|
|
QUOTE(angch @ Nov 15 2023, 11:38 PM) Show me your loop. Are you slurping all data into memory before iterating through the result? Or fetching per row? ie, are you: (php code, adapt to your current language) CODE
$sql = 'select * from table1';
$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
// Some drivers don't batch and prefetch rows, so you can have early termination.
// This incurs latency, though, but you don't use as much memory.
while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
$data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
print $data;
}
or CODE
$sql = 'select * from table1';
$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$rows = $sth->fetchAll(); // slurp all, less latency incurred, at the cost of memory
foreach ($row as $row) {
$data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
print $data;
}
no loop, something like this CODE private static DataSet SelectRows(DataSet dataset,
string connectionString,string queryString)
{
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(
queryString, connection);
adapter.Fill(dataset); // <----- huge delay here.
return dataset;
}
} |
|
|
|
|
Quote
0.0155sec
1.33
6 queries
GZIP Disabled