473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Application times out while doing the updates

AJ
here's the scenario..

I am running a DTS to collect the summarized info from Oracle database
into SQL server. I then have a update job which updates my
transactional table from the summarized table.

The update takes a very long time (~ 3 minutes)even though it has
around 1500 rows which causes the application to timeout. I want this
job to be done in less than a minute.

Thoughts on improving performance. Is stored procedure a way to go?
(I have used Isolation,row hints etc etc..nothing seems to be working)

AJ
Jul 20 '05 #1
11 1532
I suggest you review the execution plan of the UPDATE query. Perhaps
additional indexes or query changes will speed it up substantially. If you
need additional help, please post you table DDL (including constraints,
indexes and triggers) along with you query and sample data.

Simply encapsulating the query in a proc is unlikely to improve performance.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AJ" <aj*****@hotmail.com> wrote in message
news:60**************************@posting.google.c om...
here's the scenario..

I am running a DTS to collect the summarized info from Oracle database
into SQL server. I then have a update job which updates my
transactional table from the summarized table.

The update takes a very long time (~ 3 minutes)even though it has
around 1500 rows which causes the application to timeout. I want this
job to be done in less than a minute.

Thoughts on improving performance. Is stored procedure a way to go?
(I have used Isolation,row hints etc etc..nothing seems to be working)

AJ

Jul 20 '05 #2
AJ
Thanks Dan,
Here's the entire structure and query

Table A has these columns

a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
and few other columns

ftq,break_offs, completes,assigned are the summarized cols. which are
updated every hour from the summary_table.

Summary table has these cols.

a_id,ftq break_offs,completes,assigned.

I am issuing this update statement

UPDATE A SET
break_offs = (select case when break_offs<0 then 0 else break_offs end
break_offs from summary_table where A.ano = summary_table.ano),
ftq=(select ftq from summary_table where A.ano=summary_table.ano),
actually_assigned=(select member_assigned from summary_table where
A.ano=summary_table.ano),
qualified_completes=( select member_completes from summary_table where
A.ano=summary_table.ano)
WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
summary_table.ano) and
end_date>=getdate().
-------------------------------------

Apart from this we also have stored procs, trigger on the same
table.But the thing is the jobs runs 15 minutes past the hour and
stalls everything.

Thanks again

AJ
Jul 20 '05 #3
AJ (aj*****@hotmail.com) writes:
Here's the entire structure and query

Table A has these columns

a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
and few other columns

ftq,break_offs, completes,assigned are the summarized cols. which are
updated every hour from the summary_table.

Summary table has these cols.

a_id,ftq break_offs,completes,assigned.
Note here: Dan asked for the DDL. By this he means the CREATE TABLE
statements. These are useful if you want to test a query, and they
are also easier to read than a free-form list. In this case, the
cure appears simple enough anyway:
UPDATE A SET
break_offs = (select case when break_offs<0 then 0 else break_offs end
break_offs from summary_table where A.ano = summary_table.ano),
ftq=(select ftq from summary_table where A.ano=summary_table.ano),
actually_assigned=(select member_assigned from summary_table where
A.ano=summary_table.ano),
qualified_completes=( select member_completes from summary_table where
A.ano=summary_table.ano)
WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
summary_table.ano) and
end_date>=getdate().


This should be faster:

UPDATE A
SET break_offs = case when s.break_offs < 0 then 0
else s.break_offs
end,
ftq = s.ftq,
actually_assigned = s.member_assigned,
qualified_completes = s.member_completes,
FROM A
JOIN summary_table s ON a.ano = s.ano
and A.end_date >= getdate()

This is uses syntax that is proprietary to MS SQL Server and Sybase,
but it is a lot more effecient, since in your version each subquery
is evaluated separately.

Permit me also to note that the last condition looks funky. getdate()
returns the current time, so if A.end_date is a date only, this condition
may not do what you expect. That is, rows where end_date = today will
not be updated.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Hi Dan/Erland,

Is there a way to save the execution plan?..I ran trace/execution. My
update job seems to be firing at the end since I have lots of table
level triggers. The changes that Erland is suggesting is infact taking
longer than my query.
Thoughts/Comments

AJ

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5
AJ Jr (aj*****@hotmail.com) writes:
Is there a way to save the execution plan?..I ran trace/execution. My
update job seems to be firing at the end since I have lots of table
level triggers. The changes that Erland is suggesting is infact taking
longer than my query.


You can run a query from Query Analyzer and press CTRL/K to get a graphical
showplan. Or you can press CTRL/L to get an estimated plan, so that the
query is not run. Tnis is not useful if you run the entire procedure, but
mainly if you run the troublesome statement separately.

If you have a Profiler trace with the Execution Plan event, you can find
the plan for the query, select that row, and the cut and paste from the
lower pane. Normally the plan you are looking for is the one before
StmtCompleted, but if there are triggers on the table, it may not be in
this case.

Too bad that my suggestion performed worse. That was indeed a bit of a
surprise. It would be interesting to see both plans.

It would help to have the complete CREATE TABLE and CREATE INDEX statements
for the tables.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6
To add to Erland's response, you may want to add unique constraints or
indexes on the ano columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AJ" <aj*****@hotmail.com> wrote in message
news:60**************************@posting.google.c om...
Thanks Dan,
Here's the entire structure and query

Table A has these columns

a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
and few other columns

ftq,break_offs, completes,assigned are the summarized cols. which are
updated every hour from the summary_table.

Summary table has these cols.

a_id,ftq break_offs,completes,assigned.

I am issuing this update statement

UPDATE A SET
break_offs = (select case when break_offs<0 then 0 else break_offs end
break_offs from summary_table where A.ano = summary_table.ano),
ftq=(select ftq from summary_table where A.ano=summary_table.ano),
actually_assigned=(select member_assigned from summary_table where
A.ano=summary_table.ano),
qualified_completes=( select member_completes from summary_table where
A.ano=summary_table.ano)
WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
summary_table.ano) and
end_date>=getdate().
-------------------------------------

Apart from this we also have stored procs, trigger on the same
table.But the thing is the jobs runs 15 minutes past the hour and
stalls everything.

Thanks again

AJ

Jul 20 '05 #7
AJ
Hi Dan/Erland

I have identified a problem : It is the Update trigger which has a
cursor which checks all the records before allowing the update.

Comments/Thoughts on this one :

The update job is the scheduled job..it runs as user x (I can specify
sa or DBO).
Is there a way in SQL server that I can fire the trigger on a table
for a particular user only (Application user).

Thanks ia advance

AJ
Jul 20 '05 #8
AJ (aj*****@hotmail.com) writes:
I have identified a problem : It is the Update trigger which has a
cursor which checks all the records before allowing the update.

Comments/Thoughts on this one :

The update job is the scheduled job..it runs as user x (I can specify
sa or DBO).
Is there a way in SQL server that I can fire the trigger on a table
for a particular user only (Application user).


No, but you can use ALTER TRIGGER to disable the trigger. But this is
a little risky if the job dies for some reason in the middle of and leaves
the trigger disabled.

A better technique is to define a temp table, call it #triggerdisable, just
before the update, and then in the trigger add:

if object_id('tempdb..#triggerdisable') IS NOT NULL
RETURN

Note: what columns the table has or what data there is does not matter. It's
the sheer existence that matter.

But of course the best solution would be rewrite the trigger to use set-
based operations!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9
AJ
Thanks Erland,

But I am kinda lost...By defining #triggerdisable..What do you mean?

Thanks again

AJ
Jul 20 '05 #10
AJ (aj*****@hotmail.com) writes:
But I am kinda lost...By defining #triggerdisable..What do you mean?


CREATE TABLE #triggerdisable(a int NOT NULL)

The temp table serves as a process-global flag to test for.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #11
AJ
thanks Erland/Dan for all your help.

AJ
Jul 20 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: nickdu | last post by:
I'm trying to isolate "applications" into their own application domain within a single process. I've quoted applications because it's a logical representation of an application. Basically it...
7
by: Bobby C. | last post by:
My company is in the process of getting ready (well actually QTR 2 2004) to roll out a rewritten version of a vertical market application for the municipal market (small and medium sized cities). ...
15
by: limeydrink | last post by:
Hi all, I want to create a mobile field worker data solution. Let me explain... I work for a company that has some software used by call takers to enter information into a database about...
59
by: Rico | last post by:
Hello, I have an application that I'm converting to Access 2003 and SQL Server 2005 Express. The application uses extensive use of DAO and the SEEK method on indexes. I'm having an issue when...
25
by: Charles Law | last post by:
What I mean is, I want my web client to be updated in real-time. The scenario is that I have a database that is updated asynchronously, and when the update takes place I want to 'notify' my web...
17
by: Timothy.Rybak | last post by:
Hello all, This is my first attempt at an application, so kid gloves are appreciated. I need to make a very simple form that only has a few elements. One is TraceCode - a text field that is...
4
by: skotapal | last post by:
Hello I manage a web based VB .net application. This application has 3 components: 1. Webapp (this calls the executibles) 2. database 3. business logic is contained in individual exe...
1
by: Cramer | last post by:
I'm running XP Pro/SP2 + patches and updates, with Visual Studio Professional 2008 (and no prior installation of Visual Studio ever installed). When attempting to open an ASP.NET Web application...
7
by: Roger | last post by:
This is the vba code Set db = CurrentDb On error goto fErr …. loop strSql = "UPDATE tblInvAnalysisWhse" & _ " SET ltDeviation = " & Sqr(dblError / intPeriods) & _ " WHERE item = '" &...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.