1

I have install Sql Server Management Studio in my PC.

Then am connecting to one of my databases remotely

I execute a query that returns 6000 records in almost 1 or 2 seconds but when i run the same query from .Net Application (WPF Desktop) also from the same PC the data transfer takes almost 8-9 seconds

My Code in .Net is very simple and straightforward

This is the code

 Private Sub Button_Click(sender As Object, e As RoutedEventArgs)
    Dim Sql_Connection As New SqlClient.SqlConnection("MyRemoteSQLConnectionString")
    Dim Sql_Command As New SqlClient.SqlCommand("select Col1,Col2,Col3,Col4,Col5,Col6 from Table", Sql_Connection)
    Sql_Connection.Open()
    dr = Sql_Command.ExecuteReader

    Dim DataTable As New DataTable
    DataTable.Load(dr)
    'The above code takes 8-9 seconds to finished but in ssmo takes only 1-2 seconds for 6000 Records
    Sql_Connection.Close()

End Sub

why this happens? dose ssms has different way to manage the data transferred ?

Stelios
  • 330
  • 5
  • 21
  • 1
    There is no difference. How you *send* the query to the server doesn't affect how it executes there. If you find differences, it's your code. Which btw doesn't do what you think it does. The query executes when you call `ExecuteReader`. `Load` runs locally and loads the results into a datatable. You didn't post the SSMO code but I guess it doesn't load any results – Panagiotis Kanavos Dec 22 '17 at 14:52
  • BTW 6000 records are far to few to take 2 seconds, and far too many to display to humans. In fact, users are probably *not* going to read all the records, just the first few pages. Loading all 6K records at once wastes time without much benefit. If you intend to display them on a grid use paging and/or virtualized scrolling to load only what you are going to display – Panagiotis Kanavos Dec 22 '17 at 14:53
  • 1
    Try to run profiler to see what actual queries where executed. You might see the difference that would explain you the reason. – Dmitrij Kultasev Dec 22 '17 at 14:59
  • What i mean is that if ssms needs 2 seconds to transfer almost 3mb data over the network from the remote database to my local PC and give me the results in ssms in 2 seconds why the DT.Load needs 8 seconds to fill the data?Dose ssms handles different way the data transfer over the network? or handles different way how to display the data in the results panel? because both ssms and .net executes the same query against the same connectionString and am trying to identity why .net takes 8 seconds and ssms takes 2 seconds to display the results. – Stelios Dec 22 '17 at 15:17
  • Possible duplicate of [SQL Query slow in .NET application but instantaneous in SQL Server Management Studio](https://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-st) – Tab Alleman Dec 22 '17 at 16:24
  • @user2160275, how long does it take if you replace `DataTable.Load(dr)` with `While dr.Read End While`? – Dan Guzman Dec 23 '17 at 03:19
  • @DanGuzman Again it takes 7 seconds – Stelios Dec 23 '17 at 18:52
  • 1
    @user2160275, SSMS also uses ADO.NET. It is likely the query plans are different. I suggest you peruse http://www.sommarskog.se/query-plan-mysteries.html. – Dan Guzman Dec 23 '17 at 19:02
  • @PanagiotisKanavos.I know 6K is too many for humans but when you have to deal with invoices stocks and generally with an accounting system. sometimes you want the data ready there.Assume a combobox in invoice details that contains items.You want to type the name and see it there instantly and proceed to the next item.In production environment users don't realize paging here they want to do their job fast.Believe me i am with you. If Paging is the only way out then i will implement it. but in my case i should do the best to have the data there and ready for the user as possible fast as it can. – Stelios Dec 24 '17 at 11:03

1 Answers1

1

OK, seems i found the answer in this POST

Same query with the same query plan takes ~10x longer when executed from ADO.NET vs. SMSS

MultipleActiveResultsSet option in ado.net ConnectionString should be FALSE

and @DanGuzman thank you for clearing this. "SSMS also uses ADO.NET."

Stelios
  • 330
  • 5
  • 21