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)
AJI 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" <aj70000@.hotmail.com> wrote in message
news:6097f505.0408272303.752889bc@.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|||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|||AJ (aj70000@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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!|||AJ Jr (aj70000@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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" <aj70000@.hotmail.com> wrote in message
news:6097f505.0408281317.3e394ab2@.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|||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|||AJ (aj70000@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
But I am kinda lost...By defining #triggerdisable..What do you mean?
Thanks again
AJ|||AJ (aj70000@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks Erland/Dan for all your help.
AJ
No comments:
Post a Comment