-
Sep 17th, 2018, 09:38 AM
#1
Thread Starter
Addicted Member
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
-
Sep 17th, 2018, 11:34 AM
#2
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
-
Sep 17th, 2018, 02:45 PM
#3
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
-
Sep 18th, 2018, 03:13 AM
#4
Thread Starter
Addicted Member
Re: Tips for issue on SQL Server and tool in .net
Originally Posted by Shaggy Hiker
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.
-
Sep 18th, 2018, 03:22 AM
#5
Thread Starter
Addicted Member
Re: Tips for issue on SQL Server and tool in .net
Originally Posted by techgnome
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.
-
Sep 20th, 2018, 06:22 PM
#6
Re: Tips for issue on SQL Server and tool in .net
Originally Posted by giodepa
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
-
Sep 20th, 2018, 06:43 PM
#7
Re: Tips for issue on SQL Server and tool in .net
Originally Posted by techgnome
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:
private int FillInternal(DataSet dataset, DataTable[] datatables, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior) { bool flag = command.Connection == null; try { IDbConnection connection3 = DbDataAdapter.GetConnection3(this, command, "Fill"); ConnectionState originalState = ConnectionState.Open; if (MissingSchemaAction.AddWithKey == this.MissingSchemaAction) behavior |= CommandBehavior.KeyInfo; try { DbDataAdapter.QuietOpen(connection3, out originalState); behavior |= CommandBehavior.SequentialAccess; IDataReader dataReader = (IDataReader) null; try { dataReader = command.ExecuteReader(behavior); if (datatables != null) return this.Fill(datatables, dataReader, startRecord, maxRecords); return this.Fill(dataset, srcTable, dataReader, startRecord, maxRecords); } finally { dataReader?.Dispose(); } } finally { DbDataAdapter.QuietClose(connection3, originalState); } } finally { if (flag) { command.Transaction = (IDbTransaction) null; command.Connection = (IDbConnection) null; } } }
-
Sep 20th, 2018, 09:30 PM
#8
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
-
Sep 24th, 2018, 03:14 AM
#9
Thread Starter
Addicted Member
Re: Tips for issue on SQL Server and tool in .net
Originally Posted by Shaggy Hiker
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.
-
Sep 24th, 2018, 03:24 AM
#10
Thread Starter
Addicted Member
Re: Tips for issue on SQL Server and tool in .net
Originally Posted by Shaggy Hiker
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|