Trigger in SQL2k causing ASP.NET web page to timeout, help
-
Hi
If my web application after some user click, will update one of
my database table column which will trigger a stored procedure
to run. This stored procedure will take around 20 second depending
on the data records. Most of the time, it will cause my web
application to timeout.
I need the stored procedure to run when user update
that specific column of the table. Is there anyway to
solve my issue.
I am thinking of just updating a table of that column
update, then have an agent to monitor that table and
then run that stored procedure.
Is there any better method/way to implement similar
method. Please advise. Thank you
-
You'd probably get a faster response (a better one, too), if you posted the offending code, the table(s) schema, and give us a hint on the table size.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I definitely agree with Jeff - perhaps your stored proc is doing much more work than is required. We need some background to the problem to better help you.
However, I assume you are saying that you have a number of columns in a table. You have a trigger that is for UPDATE. You want this trigger to execute a certain stored proc ONLY if a change is detected in a particular column. In that case, look in SQL Server Books Online (the help manual with SQL Server) for triggers. You'll see that within a trigger you have access to special tables called inserted and deleted. These tables will have the same definition as the table against which the trigger is created. You can, using a simple select query, check the value of the column in each table to see if it has changed. Beware that your trigger will execute once per update batch, not once per updated row. This means that the inserted and deleted tables may have multiple rows - you can use your primary key to join them.
Hope that helps to at least reduce the unnecssary executions of your stored proc. However, you'd be better off getting help with your SP. For example, if your SP reprocesses every row in your table, you'd be better off passing it a parameter indicating the pkey of the changed row so that it has MUCH less work to do. If your SP really does need to run for 20secs, then you could also - and this is messy - schedule a job to run in SQL Server Agent in X seconds time. This way your trigger can execute quickly and SQL Server Agent can asynchronously execute your lengthy stored proc call...
Cheers
-
Hi,
I also thinking of using Ian method but afraid it the job will hog the CPU resources.
Currently, some SP is causing high CPU usage, I don't want the agent to hog the CPU further.
Anyway, thank for the feedback and advise.
-
There's no way you should have a trigger that takes 20s. Optimise the code or get it out of the trigger and do it some other way. Doing it in a job is a last resort, as it is utterly non-transactional. The problem can almost certainly be fixed by improving the details of the code rather than changing your approach radically. Unfortunately you haven't posted any code so no-one can properly help you. What a shame. Still, you have only yourself to blame. You were even asked.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
thats why his user id is "Rookie"
-
Ermmm... nope... that's his posting status... "Rookie" is about 40 more than "Newbie"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi,
Thank everyone for their feedback and comment.
-
if your processing does not need to be executed online-realtime, only execute it online-neartime.
have your update-trigger raise an error (nonblocking, but logged) and define an alert for it , so the alert activates a job that executes your stored proc.
check out help to tighten use of cmdshell or sp_start_job
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply