0

we have 3000 rows of data, with 10 columns in a view. The first connection takes 2.2 seconds and the second time the same grid is shown, the time falls into 150 milliseconds.

we are using something like (this is not the real table, no real data, but just to give an idea), from SQL/C# - Best method for executing a query:

private static void ReadOrderData(string connectionString)
   {
       string queryString = 
           "SELECT OrderID, CustomerID FROM dbo.Orders;";
       using (SqlConnection connection = new SqlConnection(
                  connectionString))
       {
           SqlCommand command = new SqlCommand(
               queryString, connection);
           connection.Open();
           SqlDataReader reader = command.ExecuteReader();
           try
           {
               while (reader.Read())
               {
                   Console.WriteLine(String.Format("{0}, {1}",
                       reader[0], reader[1]));
               }
           }
           finally
           {
               // Always call Close when done reading.
               reader.Close();
           }
       }
   }

Here is the connection string style:

 Server=...;Database=...;Uid=...;Pwd=...;Encrypt=True;TrustServerCertificate = true;Asynchronous Processing=true;MultipleActiveResultSets=True

We used DEvexpress wpf Grid and https://fastwpfgrid.codeplex.com/ to tests. The problem is that we have a window with 4 elements like this one and the first opening f that window will take 8 seconds. We can hack all this and try to get it down to one connection, but we doNt understand, when we have very erformant servers and fast internet connection and only 3 000 rows it will take 2.2 secondes to open the connection and then the second time is faster !

We also tried the automatic manging of the datagrid de DevExpress with the assyncrous connection, but, since we have such small amount of data, this did not help so much and all this mecanism, some times, made the first opening a bit more slow.

Localy, without connection to another server for the db, its going a bit faster. We changed the name of the server for its ip adress and we only gained 0.02 seconds.

By the way, originaly we where using entity framework, the code has been modified because of all this performance roblem (wich DevExpress did not like though).

Any one has any idea ? Thank you !

Community
  • 1
  • 1
user1644338
  • 15
  • 1
  • 3
  • SQLServer tends to cache results. If it believes the data hasn't changed, the second time around it is probably returning a cached value. – Gratus D. May 11 '17 at 03:11
  • How are you modeling your modeling yoru data? Cold start vs warm start of the application will always have a difference in following queries. – mvermef May 11 '17 at 03:32
  • Have you tried using the Stopwatch class to determine exactly on which statements the time is being spent? – finrod May 11 '17 at 04:24
  • What happens if you reboot your sql server, then connect sqlserver management studio and run your query again? Is it still 2.2 seconds then faster for the second running? If sqlserver Man studio runs fast all the time, it is unlikely to be he database fault. Look at two connection time, maybe web server is going to sleep, unloading the application etc. If however ssms runs the query slowly on first time then faster, you need to take a look why your sqlserver struggles; overloaded disk? Huge table with many blobs? Etc.... – Caius Jard May 11 '17 at 05:06
  • 1
    Are you sure it's the query taking all of that time, or is the actual display of the WPF form that goes from 2.2 seconds to 150 milliseconds? That is, the first time you open the form, it is slow, but the second time you open the form, it is faster. – Brendon May 11 '17 at 14:19

0 Answers0