Speed issue causing time out Microsoft SQL

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.