Stored procedure taking longer time to execute.

Question

0.00/5 (No votes)

See more:

, +

I have a software which runs on Network. There are around 10 client machines connected to the Server.
All the sql queries are connected through Stored Procedure.

Steps are followed for each transaction.
Example :

A customer goes to Client Machine X and places the order.
Than he goes to Client Machine Y to get the delivery.

But one in say 1000 case the order sent from Client Machine X is not recorded in the server, and Client Machine Y cannot do the delivery.

There are huge transaction, and such cases makes a lot of disturbance.

I am not able to figure out the root cause of the problem.

Please help

What I have tried:

I have set certain blocks in database fields, but nothing is working.

Posted 16-Aug-18 23:04pm

vb_buddy

Updated 16-Aug-18 23:12pm

Add a Solution

Comments

Herman<T>.Instance 17-Aug-18 5:17am

Do you have any exceptions logged? Timeout for example?

PeejayAdams 17-Aug-18 6:48am

What kind of time variations are you getting? Is there any kind of pattern relating to which users are running the procedure?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-08-16T23:13:00

Solution 1

We have absolutely no idea.
You need to look at your code very, very carefully, and work out what is going on - and you are the only person who can do that, because:
0) We can't see how you validate your inputs, or pass them to the server.
1) We can't see your code - so we don't know if you are swallowing exceptions, using bad practices, anything.
2) We can't see your SP - so we also have no idea what that does,m o0r how it does it.
3) We have no access to the DB, or to the values you are trying to put into it - so we can't tell if there is any common factor in any of the missing records.

Sorry, but you are going to have to work this out for yourself - nobody else has the required information (which is all of it!)

Posted 16-Aug-18 23:13pm

OriginalGriff

Comments

vb_buddy 21-Aug-18 7:53am

Thank you all for your valuable time for my query.
Can we put timeout value for queries in stored procedure.
say 10 seconds, and if it crosses the time, the entire transaction is rolled back.

Thanks in advance

OriginalGriff 21-Aug-18 8:40am

Yes ... but ... it's a poor idea.
Put

SET LOCK_TIMEOUT 10000;

at the top of the query, and it will throw an exception if exceeds 10 seconds. That could cause a rollback - but you'll have to be careful to notify the user.

It probably won't solve your earlier reported problem, just introduce new ones...

vb_buddy 23-Aug-18 3:08am

Thank you... will try

OriginalGriff 23-Aug-18 3:16am

You're welcome!

vb_buddy 23-Aug-18 3:34am

U are true... this might not solve the problem...
I will explain you the process of transaction :

The software runs on VB.net
-> the user clicks on the save button
-> it goes to a function and gives all the data viz.. name of the stored procedure, columns names, raw data through command.Parameters.AddWithValue
and than the whole set of queries is executed in the stored procedure.
And than it again comes back to vb.net and takes the result. In case if anything goes wrong in between, the entire transaction is rolled back and throws an error through VB.Net.

My idea is if this whole set of transaction takes more than a specified amount of time, it should not be executed and should throw an error to the user. And than the user has to again process the transaction.

I have a gut feeling that you will solve my problem.

Warm Regards..

OriginalGriff 23-Aug-18 3:44am

I think you need to look at your SP and see what it is doing: user input taking in excess of 10 seconds to save into a modern DB? Either they are entering WAY too much data at a time - and that's bad because any mistake or a power fluctuation can lose them huge amounts of work - or there is something very, very wrong in your data flow / design.
A single user input should take milliseconds at worst to INSERT / UPDATE.

vb_buddy 27-Aug-18 1:16am

thank you... will scrutinise the SP