Welcome Guest ( Log In | Register )

Bump Topic Topic Closed RSS Feed

Outline · [ Standard ] · Linear+

 .net app getting sql data slow from network

views
     
angch
post Nov 15 2023, 09:34 AM

On my way
****
Junior Member
636 posts

Joined: Jul 2006
QUOTE(narf03 @ Nov 14 2023, 09:10 PM)
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 ?
*
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#QueryExecMode
to 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.

This post has been edited by angch: Nov 15 2023, 09:39 AM
angch
post Nov 15 2023, 11:38 PM

On my way
****
Junior Member
636 posts

Joined: Jul 2006
QUOTE(narf03 @ Nov 15 2023, 03:18 PM)
not by row, not using SP, doing something like select * from table1, no filter
*
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;
   }
angch
post Nov 15 2023, 11:38 PM

On my way
****
Junior Member
636 posts

Joined: Jul 2006
QUOTE(narf03 @ 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)
*
No, it doesn't matter.
angch
post Nov 16 2023, 01:22 AM

On my way
****
Junior Member
636 posts

Joined: Jul 2006
QUOTE(narf03 @ Nov 16 2023, 12:01 AM)
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;
   }
}

*
Hmm, ok, got it. Low level wireshark or tcpdump might help.

On the other hand, your use of DataAdapter might be problematic: https://learn.microsoft.com/en-us/dotnet/fr...m-a-dataadapter

See the note, copy pasted here:

QUOTE
Using the DataAdapter to retrieve all of a table takes time, especially if there are many rows in the table. This is because accessing the database, locating and processing the data, and then transferring the data to the client is time-consuming. Pulling all of the table to the client also locks all of the rows on the server. To improve performance, you can use the WHERE clause to greatly reduce the number of rows returned to the client. You can also reduce the amount of data returned to the client by only explicitly listing required columns in the SELECT statement. Another good workaround is to retrieve the rows in batches (such as several hundred rows at a time) and only retrieve the next batch when the client is finished with the current batch.
It appears that it not only selects the data, but prepares them individually for updates, locking up the table in the db. Don't know what it does, but Fill() in itself might be the cause of the latency.

Might well be a specific .NET and SQL Server thing, which is out of my depth. Good luck.

This post has been edited by angch: Nov 16 2023, 01:24 AM
« Next Oldest · Codemasters · Next Newest »
 

Topic ClosedOptions
 

Change to:
| Lo-Fi Version
 0.0195sec    1.90    6 queries    GZIP Disabled
Time is now: 23rd December 2025 - 11:14 PM
All Rights Reserved © 2002- 2025 Vijandren Ramadass (~unite against racism~)
Removal Request
Powered by Invision Power Board © 2025  IPS, Inc.