|
|
You're in the
sql server (alternate)
group:why is execution of a storedprocedure in QueryAnalyzer faster than executing this SP as scheduled job?
sql server (alternate):
-- Andrew J. Kelly SQL MVP [quoted text, click to view] "Heiko Pliefke" <sledge_hammer@freenet.de> wrote in message news:41D437B8.9010909@freenet.de... > Hi! > > You are great! Thank you very much, Dan and Tibor! > With NOCOUNT OFF the runtime is reduced to < 30 seconds! Super! > > > > Best regards and Happy new year!! > > -- > Heiko > > > Tibor Karaszi schrieb: >>>It appears you have an INSERT trigger on the table that iterates over the >>>inserted table. >> >> >> Good catch, Dan! >>
[quoted text, click to view]
> I have noticed something strange: After executing the SP the queryanalyzer > outputs "1 Row(s) affected." line by line - 100.000 times... Obviously > every line is inserted one by one. It appears you have an INSERT trigger on the table that iterates over the inserted table. To improve performance, add SET NOCOUNT ON at the beginning of the trigger and/or proc to suppress DONE_IN_PROC messages. Also, consider revising the trigger code to use a set-based technique. -- Happy Holidays Dan Guzman SQL Server MVP [quoted text, click to view] "Heiko Pliefke" <sledge_hammer@freenet.de> wrote in message news:1104416731.799992@nbgm66x... > Hi NG! > > I wrote a stored procedure which at first deletes 100.000 rows and then > inserts 100.000 new rows. > > There is a huge difference between executing this SP in the query-analyzer > (runtime approx. 2 minutes), and scheduling this SP as an Job (runtime > > 30 minutes!!!). > > Why? Whats the issue? > And how can i improve the "job"-runtime? > > The INSERT-Statement gets the data from a joined and grouped SELECT - here > is the code (exemplary): > > > INSERT INTO [Table1] > (Field1, Field2,... ) > SELECT DISTINCT Field1, Count(field2), (Count(field2) * (-1)) > FROM Table2 T2 > INNER JOIN Table3 T3 ON T2.[ID] = T3.[ID] > INNER JOIN Table4 T4 ON T2.[DID] = T4.[ID] > INNER JOIN Table5 T5 ON T2.[SID] = T5.[ID] > GROUP BY field1, field2... > > > I have noticed something strange: After executing the SP the queryanalyzer > outputs "1 Row(s) affected." line by line - 100.000 times... Obviously > every line is inserted one by one. > > Any idea? > > Would it help, to create a temporary-table with SELECT INTO, and then just > INSERT this data (w/o aggregation) into the target table? > > > Any help is greatly appreciated, tia and best regards > > --- > Heiko
Heiko Pliefke (sledge_hammer@freenet.de) writes:
[quoted text, click to view] > But there's still one question: Maybe there is a trigger, why is the > execution in QA 10-15times faster than the Job? Doesn't the trigger have > the same "performance-impact" on this two ways? I think Tibor addressed this: Anyhow, it is indeed the " rows affected" messages that causes the difference between QA and Agent. Agent has a delay for each such "DONE IN PROC" signal. There are also differences between QA and Agent with regarding to the default for some SET options, so with Agent ARITHABORT and QUOTED_IDENTIFIER are off. This can have severe effect if there is an indexed view or an indexed computed column somewhere. The optimizer does not consider these indexes when these two options are off. (There are four more options that must be on.) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at
Hi NG!
I wrote a stored procedure which at first deletes 100.000 rows and then inserts 100.000 new rows. There is a huge difference between executing this SP in the query-analyzer (runtime approx. 2 minutes), and scheduling this SP as an Job (runtime > 30 minutes!!!). Why? Whats the issue? And how can i improve the "job"-runtime? The INSERT-Statement gets the data from a joined and grouped SELECT - here is the code (exemplary): INSERT INTO [Table1] (Field1, Field2,... ) SELECT DISTINCT Field1, Count(field2), (Count(field2) * (-1)) FROM Table2 T2 INNER JOIN Table3 T3 ON T2.[ID] = T3.[ID] INNER JOIN Table4 T4 ON T2.[DID] = T4.[ID] INNER JOIN Table5 T5 ON T2.[SID] = T5.[ID] GROUP BY field1, field2... I have noticed something strange: After executing the SP the queryanalyzer outputs "1 Row(s) affected." line by line - 100.000 times... Obviously every line is inserted one by one. Any idea? Would it help, to create a temporary-table with SELECT INTO, and then just INSERT this data (w/o aggregation) into the target table? Any help is greatly appreciated, tia and best regards ---
Strange. If you have several "1 rows affected", you are executing several statements. You get one
such message per DML (INSERT, UPDATE, DELETE or SELECT) statement you are executing. So it sounds to like you either have a loop of some kind (cursor perhaps) or in some other way a whole bunch of DML statements. I would look into this as the first step. Anyhow, it is indeed the " rows affected" messages that causes the difference between QA and Agent. Agent has a delay for each such "DONE IN PROC" signal. You can lesser the effect by adding SET NOCOUNT ON in the beginning of the proc code. If there still is a significant difference, try using a CmdExec jobstep instead from where you execute the proc using OSQL.EXE. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ [quoted text, click to view] "Heiko Pliefke" <sledge_hammer@freenet.de> wrote in message news:1104416731.799992@nbgm66x... > Hi NG! > > I wrote a stored procedure which at first deletes 100.000 rows and then inserts 100.000 new rows. > > There is a huge difference between executing this SP in the query-analyzer (runtime approx. 2 > minutes), and scheduling this SP as an Job (runtime > 30 minutes!!!). > > Why? Whats the issue? > And how can i improve the "job"-runtime? > > The INSERT-Statement gets the data from a joined and grouped SELECT - here is the code > (exemplary): > > > INSERT INTO [Table1] > (Field1, Field2,... ) > SELECT DISTINCT Field1, Count(field2), (Count(field2) * (-1)) > FROM Table2 T2 > INNER JOIN Table3 T3 ON T2.[ID] = T3.[ID] > INNER JOIN Table4 T4 ON T2.[DID] = T4.[ID] > INNER JOIN Table5 T5 ON T2.[SID] = T5.[ID] > GROUP BY field1, field2... > > > I have noticed something strange: After executing the SP the queryanalyzer outputs "1 Row(s) > affected." line by line - 100.000 times... Obviously every line is inserted one by one. > > Any idea? > > Would it help, to create a temporary-table with SELECT INTO, and then just INSERT this data (w/o > aggregation) into the target table? > > > Any help is greatly appreciated, tia and best regards > > --- > Heiko
[quoted text, click to view]
> It appears you have an INSERT trigger on the table that iterates over the inserted table. Good catch, Dan! -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ [quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:qpUAd.4264$F67.156@newssvr12.news.prodigy.com... >> I have noticed something strange: After executing the SP the queryanalyzer outputs "1 Row(s) >> affected." line by line - 100.000 times... Obviously every line is inserted one by one. > > It appears you have an INSERT trigger on the table that iterates over the inserted table. > > To improve performance, add SET NOCOUNT ON at the beginning of the trigger and/or proc to suppress > DONE_IN_PROC messages. Also, consider revising the trigger code to use a set-based technique. > > -- > Happy Holidays > > Dan Guzman > SQL Server MVP > > "Heiko Pliefke" <sledge_hammer@freenet.de> wrote in message news:1104416731.799992@nbgm66x... >> Hi NG! >> >> I wrote a stored procedure which at first deletes 100.000 rows and then inserts 100.000 new rows. >> >> There is a huge difference between executing this SP in the query-analyzer (runtime approx. 2 >> minutes), and scheduling this SP as an Job (runtime > 30 minutes!!!). >> >> Why? Whats the issue? >> And how can i improve the "job"-runtime? >> >> The INSERT-Statement gets the data from a joined and grouped SELECT - here is the code >> (exemplary): >> >> >> INSERT INTO [Table1] >> (Field1, Field2,... ) >> SELECT DISTINCT Field1, Count(field2), (Count(field2) * (-1)) >> FROM Table2 T2 >> INNER JOIN Table3 T3 ON T2.[ID] = T3.[ID] >> INNER JOIN Table4 T4 ON T2.[DID] = T4.[ID] >> INNER JOIN Table5 T5 ON T2.[SID] = T5.[ID] >> GROUP BY field1, field2... >> >> >> I have noticed something strange: After executing the SP the queryanalyzer outputs "1 Row(s) >> affected." line by line - 100.000 times... Obviously every line is inserted one by one. >> >> Any idea? >> >> Would it help, to create a temporary-table with SELECT INTO, and then just INSERT this data (w/o >> aggregation) into the target table? >> >> >> Any help is greatly appreciated, tia and best regards >> >> --- >> Heiko > >
Hi!
Thank you all for your answers! I think I know what to do now - i will try and feedback how it works ;) But there's still one question: Maybe there is a trigger, why is the execution in QA 10-15times faster than the Job? Doesn't the trigger have the same "performance-impact" on this two ways? Dan Guzman schrieb: [quoted text, click to view] > It appears you have an INSERT trigger on the table that iterates over the > inserted table. > > To improve performance, add SET NOCOUNT ON at the beginning of the trigger > and/or proc to suppress DONE_IN_PROC messages. Also, consider revising the > trigger code to use a set-based technique. > Best regards,
Hi!
You are great! Thank you very much, Dan and Tibor! With NOCOUNT OFF the runtime is reduced to < 30 seconds! Super! Best regards and Happy new year!! -- Heiko Tibor Karaszi schrieb: [quoted text, click to view] >>It appears you have an INSERT trigger on the table that iterates over the inserted table.
> > > Good catch, Dan!
Hi!
You are great! Thank you very much, Dan and Tibor! With NOCOUNT OFF the runtime is reduced to < 30 seconds! Super! Best regards and Happy new year!! --
[quoted text, click to view]
> But there's still one question: Maybe there is a trigger, why is the > execution in QA 10-15times faster than the Job? Doesn't the trigger have > the same "performance-impact" on this two ways? SQL Agent jobs are more susceptible to DONE_IN_PROC messages as Tibor stated. See http://support.microsoft.com/default.aspx?scid=kb;en-us;249730 -- Happy Holidays Dan Guzman SQL Server MVP [quoted text, click to view] "Heiko Pliefke" <sledge_hammer@freenet.de> wrote in message news:1104421665.669657@nbgm66x... > Hi! > > Thank you all for your answers! > I think I know what to do now - i will try and feedback how it works ;) > > But there's still one question: Maybe there is a trigger, why is the > execution in QA 10-15times faster than the Job? Doesn't the trigger have > the same "performance-impact" on this two ways? > > Dan Guzman schrieb: >> It appears you have an INSERT trigger on the table that iterates over the >> inserted table. >> >> To improve performance, add SET NOCOUNT ON at the beginning of the >> trigger and/or proc to suppress DONE_IN_PROC messages. Also, consider >> revising the trigger code to use a set-based technique. >> > > > Best regards, > > Heiko
[quoted text, click to view]
> why is the execution in QA 10-15times faster than the Job? Doesn't the trigger have the same > "performance-impact" on this two ways? See my first reply regarding the wait Agent introduces for "rows affected" messages. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ [quoted text, click to view] "Heiko Pliefke" <sledge_hammer@freenet.de> wrote in message news:1104421665.669657@nbgm66x... > Hi! > > Thank you all for your answers! > I think I know what to do now - i will try and feedback how it works ;) > > But there's still one question: Maybe there is a trigger, why is the execution in QA 10-15times > faster than the Job? Doesn't the trigger have the same "performance-impact" on this two ways? > > Dan Guzman schrieb: >> It appears you have an INSERT trigger on the table that iterates over the inserted table. >> >> To improve performance, add SET NOCOUNT ON at the beginning of the trigger and/or proc to >> suppress DONE_IN_PROC messages. Also, consider revising the trigger code to use a set-based >> technique. >> > > > Best regards, > > Heiko
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |