question

Awicurrent-5659 avatar image
0 Votes"
Awicurrent-5659 asked XingyuZhao-MSFT commented

Speed issue causing time out Microsoft SQL

I have a simple query that gets a list of inventory items. It is from a single table.

As I read each line returned I make a call to subroutine that calculates the items that have been allocated or sold from the inventory lot and calculates the remaining value to display in a grid that lists the items and available inventory. I was wondering if the frequent reading of the files in the VB subroutine is causing the problem. The sub routine is also just calling a single table.

Does SQL run into trouble with frequent reads to a table (the allocated or sold table) while reading another table (inventory lot table)?



Everything works fine normally but on some occasions the SQL server returns time out and other times returns incomplete lists or just crashes the system. Code is written in VB.net. Process is run from VB.net code and is not a stored procedure on the server.

This only happens once in a while and not all the time which complicates figuring it out.

Any thoughts would be appreciated.



sql-server-transact-sqldotnet-visual-basic
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

Does SQL run into trouble with frequent reads to a table (the allocated or sold table) while reading another table (inventory lot table)?

No, SQL Server is your obedient slave. The one who will run into trouble is you, because you have an incorrect design.

When you want to read data from SQL Server, you should try to read all in a single access. If your pattern is to call back to SQL Server for every row you receive, all sorts of bad things can happen:

  1. The query your repeat is a little slow - but this results in very slow, when you are repeating it.

  2. Your network connection is slow.

  3. Your network connection has high bandwidth, but has high latency (common in the days of the cloud).

When you repeat the same thing again and again, small things of overhead starts to cost.

So you should retrieve all data you need in a single call. You should also retrieve it in a DataSet or similar. You should need row by row and perform a lot of actions for every row. You will keep the rows locked in SQL Server, which could have repercussions.

Or you should put your computation in a stored procedure and only return the result you need.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your answer.

I tried something else which may be improving things significantly.

When leaving a sub routine in VB.net, the connections are supposed to automatically terminate as they are out of scope. However it seems to be that process is slow and may be the real issue. Now at the end of the sub, i am closing all connections and setting the calls to nothing. It seems this process is better at closing the connections and that seems to be improving the performance significantly. Only time will tell as it needs to be tested day after day and on the network.



0 Votes 0 ·

Don't know how your code looks like, but connections should always be opened in a Using block, so that they are actively closed and disposed and returned to the connection pool the block exits. If you leave it to garbage collection to take care of it, it can take quite some time before that actually happens.

Note that the physical connection to SQL Server still remains open for another 60 seconds.

By the way, you said: SQL server returns time out. No, SQL Server normally does not return a timeout. The only time that would happen if you actively set a lock timeout. Most timeout errors come from the client API. By default, most client API gives up if a query does not complete in 30 seconds. This is something you can adjust on the SqlConnection and SqlCommand objects.

0 Votes 0 ·
Awicurrent-5659 avatar image
0 Votes"
Awicurrent-5659 answered ErlandSommarskog commented

Thank you for your input.

The 60 seconds you mention was probably the cause of the issue. As I have put in the clearing of the connections in the sub that should address that issue. With the physical connection, I assume once SQL gets the close connection message it closes immediately. Is that not correct?

Thanks also for the heads up on the time out message.

I sure do not want queries taking 30 seconds, so that is probably a safe limit.

I did look at the sqlconnection for setting a time out and that seems to only impact the amount of time to wait for the open of the connection to the server.

The sqlcommand does provide for setting the response time on the specific command which is nice as it appears to impact only that command and is not universal for the Server.

I very much appreciate your responses.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I assume once SQL gets the close connection message it closes immediately. Is that not correct?


Yes.

I did look at the sqlconnection for setting a time out and that seems to only impact the amount of time to wait for the open of the connection to the server.

Sorry, about that. I was unsure, so I looked it up - and I still got it wrong. (I believe that there are APIs that permits you to set the parameter on the connection object, so that the command object can inherit it.

0 Votes 0 ·
Awicurrent-5659 avatar image
0 Votes"
Awicurrent-5659 answered XingyuZhao-MSFT commented

It appears like I just have to watch the performance on the live system and see if the issue comes up again. If it does not, then it is solved. If it comes up again then it is back to the drawing board.

You have been very helpful and I have learned some things I did not know.

Stay well and safe

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Awicurrent-5659 ,
Does the problem come up again? If not, you can consider accepting the correct reply as answer.

0 Votes 0 ·