Results 1 to 10 of 10

Thread: Tips for issue on SQL Server and tool in .net

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2010
    Posts
    164

    Tips for issue on SQL Server and tool in .net

    Hi all,
    I created a tool where I fill a datatable from SQL Server installed on my PC.
    Code is this:
    Code:
    ...
    Dim SQLconn As New SqlConnection()
    Dim ConnString As String = "Data Source=(local);Integrated Security=True;"
    SQLconn.ConnectionString = ConnString
    SQLconn.Open()
    query_Relation = "... my string..."
    Dim table_Relation as datatable= New DataTable
    Dim adapter = New SqlDataAdapter(query_Relation, ConnString)
    adapter.SelectCommand.CommandTimeout = 15000
    adapter.Fill(table_Relation)
    Elapsed time for this query is very long (more than 1 minute) before tool displays result in datagridView, meanwhile if I launch this query on SSMS elapsed time is 6 seconds.
    Moreover I executed tool connecting to another server changing Connstring

    Code:
    ConnString = "Data Source=172.2.2.143;User Id=user;Password=user123;"
    and in this case table is filled after 10 seconds.

    So I cannot understand what is issue.
    Can someone give me some tips to check and solve this issue?

    Thank in advanced
    gio

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Tips for issue on SQL Server and tool in .net

    Have you done any timing (most likely using the StopWatch object) to figure out where the time cost is coming from? Specifically, have you timed from the opening of the connection through the filling of the datatable (not the datagridview)? If you have timed the code snippet you have shown, and have determined that ALL of the 10s delay is between the call to Open through the call to adapter.Fill, then the issue is with the DB/connection. If you haven't specifically timed that, then the most likely cause of the slowdown would be one of these two:

    1) There's FAR more data in one DB than in the other.
    2) The problem is somewhere else.

    Only timing can determine where the problem lies, but on the face of it, I would guess that the actual slowdown is not coming from where you think it is coming from, unless that local DB has millions of records and the other DB has hundreds of records.
    My usual boring signature: Nothing

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Tips for issue on SQL Server and tool in .net

    1) What does the query look like?
    2) You do know you're connecting to the master database initially there, right? Try actually specifying a database to connect to in your connection string, not just the server information.
    Personally I'd use a command object, get a data reader and use that to populate the datatable. Being a forward-only, read-only stream, it should perform faster/more efficient than going through an adaptor.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Oct 2010
    Posts
    164

    Re: Tips for issue on SQL Server and tool in .net

    Quote Originally Posted by Shaggy Hiker View Post
    Have you done any timing (most likely using the StopWatch object) to figure out where the time cost is coming from? Specifically, have you timed from the opening of the connection through the filling of the datatable (not the datagridview)?
    Yes I did. I created a RichTextBox where it showes "Starting" when connection is opened and when table is filled "Loaded into Table Relation"
    Between two messages elapsed time is 10s for one SQL server and more than 1min on my pc. Tables are same lenght (I checked) but I don't understand why if I launch the query with SSMS elapsed time is 6sec.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2010
    Posts
    164

    Re: Tips for issue on SQL Server and tool in .net

    Quote Originally Posted by techgnome View Post
    1) What does the query look like?
    2) You do know you're connecting to the master database initially there, right? Try actually specifying a database to connect to in your connection string, not just the server information.
    Personally I'd use a command object, get a data reader and use that to populate the datatable. Being a forward-only, read-only stream, it should perform faster/more efficient than going through an adaptor.

    -tg
    Query stores data in temporary Table and afterwards I get data.
    I have to use only server connection as query involves two database
    Anyway I'll try with dataReader as maybe issue is in adapter as if I launch query with SSMS it is very speed.
    But I cannot understand why using connection string with another server adapter works well and I checked lenght of tables and they are same.

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Tips for issue on SQL Server and tool in .net

    Quote Originally Posted by giodepa View Post
    Yes I did. I created a RichTextBox where it showes "Starting" when connection is opened and when table is filled "Loaded into Table Relation"
    Between two messages elapsed time is 10s for one SQL server and more than 1min on my pc. Tables are same lenght (I checked) but I don't understand why if I launch the query with SSMS elapsed time is 6sec.
    That isn't the timing I was talking about. It's pretty rare to have times so terribly slow that some visual representation like that will work, though it does appear to be working in your case.

    What I was talking about was the Stopwatch class in .NET. This allows you to time down to the millisecond, and also allows you to time what amounts to individual rows. That can be pretty useful for cases like this. From your description, I'm not quite sure whether you were timing JUST the .Fill call. After all, you can write to a control at any time, but that information will not display on the screen until the program gets around to displaying it, so depending on how you wrote to the RTB, it may have really been showing the timing of the .Fill call, or it may have included the displaying of the datatable in some other control, which is where I suspect the problem lies. By using the Stopwatch object, you can start it right before the .Fill, stop it right after the .Fill, and show the ElapsedMilliseconds....whenever you want to.
    My usual boring signature: Nothing

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Tips for issue on SQL Server and tool in .net

    Quote Originally Posted by techgnome View Post
    Personally I'd use a command object, get a data reader and use that to populate the datatable. Being a forward-only, read-only stream, it should perform faster/more efficient than going through an adaptor.
    The Fill method of a data adapter uses a data reader internally. This is a few calls deep in SqlDataAdapter.Fill:
    csharp Code:
    1. private int FillInternal(DataSet dataset, DataTable[] datatables, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
    2. {
    3.   bool flag = command.Connection == null;
    4.   try
    5.   {
    6.     IDbConnection connection3 = DbDataAdapter.GetConnection3(this, command, "Fill");
    7.     ConnectionState originalState = ConnectionState.Open;
    8.     if (MissingSchemaAction.AddWithKey == this.MissingSchemaAction)
    9.       behavior |= CommandBehavior.KeyInfo;
    10.     try
    11.     {
    12.       DbDataAdapter.QuietOpen(connection3, out originalState);
    13.       behavior |= CommandBehavior.SequentialAccess;
    14.       IDataReader dataReader = (IDataReader) null;
    15.       try
    16.       {
    17.         dataReader = command.ExecuteReader(behavior);
    18.         if (datatables != null)
    19.           return this.Fill(datatables, dataReader, startRecord, maxRecords);
    20.         return this.Fill(dataset, srcTable, dataReader, startRecord, maxRecords);
    21.       }
    22.       finally
    23.       {
    24.         dataReader?.Dispose();
    25.       }
    26.     }
    27.     finally
    28.     {
    29.       DbDataAdapter.QuietClose(connection3, originalState);
    30.     }
    31.   }
    32.   finally
    33.   {
    34.     if (flag)
    35.     {
    36.       command.Transaction = (IDbTransaction) null;
    37.       command.Connection = (IDbConnection) null;
    38.     }
    39.   }
    40. }

  8. #8
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Tips for issue on SQL Server and tool in .net

    you dont need to open the connection if youre using an adapter, the adapter handles that. in your case you may have 2 connections open to the server, one of which may not be closing

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Oct 2010
    Posts
    164

    Re: Tips for issue on SQL Server and tool in .net

    Quote Originally Posted by Shaggy Hiker View Post
    That isn't the timing I was talking about. It's pretty rare to have times so terribly slow that some visual representation like that will work, though it does appear to be working in your case.

    What I was talking about was the Stopwatch class in .NET. This allows you to time down to the millisecond, and also allows you to time what amounts to individual rows. That can be pretty useful for cases like this. From your description, I'm not quite sure whether you were timing JUST the .Fill call. After all, you can write to a control at any time, but that information will not display on the screen until the program gets around to displaying it, so depending on how you wrote to the RTB, it may have really been showing the timing of the .Fill call, or it may have included the displaying of the datatable in some other control, which is where I suspect the problem lies. By using the Stopwatch object, you can start it right before the .Fill, stop it right after the .Fill, and show the ElapsedMilliseconds....whenever you want to.
    Hi,
    I used stopwatch with following method:
    Code:
     Dim stopwatch As Stopwatch = New Stopwatch
            stopwatch.Start()
            objDR = mycommand.ExecuteReader
            table_UGsmRelation.Load(objDR)
            objDR.Close()
            mycommand.Dispose()
            stopwatch.Stop()
            MessageBox.Show(String.Format("{0}min : {1}sec", stopwatch.Elapsed.Minutes, stopwatch.Elapsed.Seconds))
    With my first SQL Server elapsed time is 1m11s
    with second SQL Server elapsed time 8s

    I checked again my query...Removing this part in my query:
    Code:
    left join  dbo.UGsmRelation s2
      on (t.Userlabel +'-'+cast(t.lac as varchar))=(s2.userLabel +'-'+ cast(s2.lac as varchar))
      where s2.userLabel is null
    Time ealpsed is same (3sec) using both SQL Server
    So I copied table UGsmRelation from server 2 to server 1 in order to have same rows, but elapsed time is equal to one reported above..
    So I cannot understand...
    Last edited by giodepa; Sep 24th, 2018 at 03:25 AM.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Oct 2010
    Posts
    164

    Re: Tips for issue on SQL Server and tool in .net

    Quote Originally Posted by Shaggy Hiker View Post
    That isn't the timing I was talking about. It's pretty rare to have times so terribly slow that some visual representation like that will work, though it does appear to be working in your case.

    What I was talking about was the Stopwatch class in .NET. This allows you to time down to the millisecond, and also allows you to time what amounts to individual rows. That can be pretty useful for cases like this. From your description, I'm not quite sure whether you were timing JUST the .Fill call. After all, you can write to a control at any time, but that information will not display on the screen until the program gets around to displaying it, so depending on how you wrote to the RTB, it may have really been showing the timing of the .Fill call, or it may have included the displaying of the datatable in some other control, which is where I suspect the problem lies. By using the Stopwatch object, you can start it right before the .Fill, stop it right after the .Fill, and show the ElapsedMilliseconds....whenever you want to.
    Hi,
    I used stopwatch with following method:
    Code:
     Dim stopwatch As Stopwatch = New Stopwatch
            stopwatch.Start()
            objDR = mycommand.ExecuteReader
            table_UGsmRelation.Load(objDR)
            objDR.Close()
            mycommand.Dispose()
            stopwatch.Stop()
            MessageBox.Show(String.Format("{0}min : {1}sec", stopwatch.Elapsed.Minutes, stopwatch.Elapsed.Seconds))
    With my first SQL Server elapsed time is 1m11s
    with second SQL Server elapsed time 8s

    I checked again my query...Removing this part in my query:
    Code:
    left join  dbo.UGsmRelation s2
      on (t.Userlabel +'-'+cast(t.lac as varchar))=(s2.userLabel +'-'+ cast(s2.lac as varchar))
      where s2.userLabel is null
    Time ealpsed is same (3sec) using both SQL Server
    So I copied table UGsmRelation from server 2 to server 1 in order to have same rows, but elapsed time is equal to one reported above..
    So I cannot understand...

    Another useful information...
    Query without lef join returns on both server the same rows (5493)
    Last edited by giodepa; Sep 24th, 2018 at 03:52 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width