"Exception: Timeout expired. The timeout period elapsed prior to completion of the operation" for SQL queries within custom app

Question

"Exception: Timeout expired. The timeout period elapsed prior to completion of the operation" for SQL queries within custom app

cvar

SSC Enthusiast

Points: 120
More actions
October 23, 2017 at 12:09 pm

#324220

I have a custom CONSOLE application in .Net which I am running under VS 2015. There are temporary tables created within application which stores the information from SQL database based on value in external file ( single column value in file).
The app was for a period of time showing SQL timeout issue as in subject. The temp tables have index created against key field ( only one field) in application code. Now, the application was throwing timeout continuously for 3 days in September 2017 though it is running fine now. The issue seems to be coming within the main temp table, data is inserted using several joins with tables which contain good amount of data. NOLOCK hint is used for all tables in query except temp table. The temp table is created in application and also populated. Maybe, would like to know the issue of SQL timeout because of nolock hint was not provided in temp table when joining and querying with other resident tables in query. Will provide more information needed for issue faced on progress. Please kindly let know on
Thanks,
Aravindan

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27079 More actions · Answer 1

Can you post the SQL code that is being executed?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

cvar SSC Enthusiast Points: 120 More actions · Answer 2

Thanks for the reply. Please find attached the SQL code which gave the timeout exception. The logs for application stopped with the timeout error.when accessing / inserting into temp table #AccMatters within the script

cvar SSC Enthusiast Points: 120 More actions · Answer 3

Would be glad to know if there was a chance to check the script posted earlier please?? Request to know whether any hints on the timeout issue??

Thanks,
Aravindan C.V.

Michael L John One Orange Chip Points: 27079 More actions · Answer 4

Unfortunately, there is over 500 lines of code here. It's going to take quite a while to digest this.

At a quick glance, the begin and commit transactions may be in the wrong places. It looks like you can move these to include a lot less processing inside the transaction.
I don't really understand the point of the temp tables. They are the only thing being inserted/updated/deleted. Is there something else that gets done with the data? Like does it ultimately get committed to actual tables? It the temp tables are it, there's not much point to enclosing this in a transaction.
What benefit do you think NOLOCK is providing?
Same question about OPTION (RECOMPILE)?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

cvar SSC Enthusiast Points: 120 More actions · Answer 5

The begin and commit transactions are present as data are finally updated to base tables using data from temp tables further in application. Yes, the transactions are required for the same reason and transaction is present in app code using the BeginTransaction() method of connection object after connecting to database. NOLOCK is the equivalent of using READ UNCOMMITED as a transaction isolation level. There is a chance of reading an uncommitted row that is subsequently rolled back. This option can prevent reads being deadlocked by other operations. OPTION RECOMPILE may not be needed here and request to kindly ignore. OPTION RECOMPILE was as such used for some testing to get fresh query estimation plan and not needed in script. Apart from this option, the script attached is same as one in custom app till point of issue . If any inconvenience caused because of presence of option in script is highly regretted.

Hope it is helpful!!

Thanks a lot for spending time and appreciate your interest. Request to maybe let know once you get through with the script.

sgmunson SSC Guru Points: 110651 More actions · Answer 6

Michael L John - Tuesday, October 24, 2017 12:34 PM
Unfortunately, there is over 500 lines of code here. It's going to take quite a while to digest this.
At a quick glance, the begin and commit transactions may be in the wrong places. It looks like you can move these to include a lot less processing inside the transaction.
I don't really understand the point of the temp tables. They are the only thing being inserted/updated/deleted. Is there something else that gets done with the data? Like does it ultimately get committed to actual tables? It the temp tables are it, there's not much point to enclosing this in a transaction.
What benefit do you think NOLOCK is providing?
Same question about OPTION (RECOMPILE)?

I know this is totally off-topic, but I just couldn't resist a quick comment on your signature... "If you assassinate a DBA, do you pull a trigger?" The answer is no, because you have to "fire" a trigger, and that just ticks off Roy Rogers, who then shows up at your door, atop said Trigger, and assassinates you. Dangerous things, triggers are...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

cvar SSC Enthusiast Points: 120 More actions · Answer 7

Request to please check earlier post if you don' t mind..