run slow sp
Hi everybody!!, I really appreciate the time you'll have with the next question: What would be a reason to understand why the execution of the SQL's out from SP run faster than the same SQL embebbed into a SP? If I execute a SP with some arguments the execution lates 39 seconds, If I replace these values in each SQL run faster... I have reviewed statistics for both, tables and procedure. Is there a document where I can understand the execution mode of SP? Thanks
Get query plans for both. Are they the same? Art On Jun 25, 2010 1:48 PM, "VANIA ROBERT" <panther_coming@yahoo.com> wrote: Hi everybody!!, I really appreciate the time you'll have with the next question: What would be a reason to understand why the execution of the SQL's out from SP run faster than the same SQL embebbed into a SP? If I execute a SP with some arguments the execution lates 39 seconds, If I replace these values in each SQL run faster... I have reviewed statistics for both, tables and procedure. Is there a document where I can understand the execution mode of SP? Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. --0015175d0334a99d9f0489de99c5
Art: I have gotten all of the execution query plans for the 6 Sql that consist my SP and differ from the execution query plan that I extracted from the SP with respect to the cost and the rows scans. Why is this behaviour ? Thanks > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: run slow sp [20486] > Date: Fri, 25 Jun 2010 14:04:25 -0400 > > Get query plans for both. Are they the same? > > Art > > On Jun 25, 2010 1:48 PM, "VANIA ROBERT" <panther_coming@yahoo.com> wrote: > > Hi everybody!!, I really appreciate the time you'll have with the next > question: > > What would be a reason to understand why the execution of the SQL's out from > SP run faster than the same SQL embebbed into a SP? > > If I execute a SP with some arguments the execution lates 39 seconds, If I > replace these values in each SQL run faster... > > I have reviewed statistics for both, tables and procedure. > > Is there a document where I can understand the execution mode of SP? > > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175d0334a99d9f0489de99c5 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Hotmail: as HOT as always www.hotmailhotness.com.mx
Probably because of the replaceable parameters. If nothing else, there is a cost to the final optimization step once the parameter values are determined. If you are using 11.50 try converting the SQL insto dynamic statements. Build the arguments passed in into the SQL string and PREPARE it in the procedure. Then the optimizer should do a better job with the queries that way. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jun 25, 2010 at 6:29 PM, Tatis Flowers <jflores@dafros.com> wrote: > Art: > > I have gotten all of the execution query plans for the 6 Sql that consist > my > SP and differ from the execution query plan that I extracted from the SP > with > respect to the cost and the rows scans. Why is this behaviour ? > > Thanks > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: run slow sp [20486] > > Date: Fri, 25 Jun 2010 14:04:25 -0400 > > > > Get query plans for both. Are they the same? > > > > Art > > > > On Jun 25, 2010 1:48 PM, "VANIA ROBERT" <panther_coming@yahoo.com> > wrote: > > > > Hi everybody!!, I really appreciate the time you'll have with the next > > question: > > > > What would be a reason to understand why the execution of the SQL's out > from > > SP run faster than the same SQL embebbed into a SP? > > > > If I execute a SP with some arguments the execution lates 39 seconds, If > I > > replace these values in each SQL run faster... > > > > I have reviewed statistics for both, tables and procedure. > > > > Is there a document where I can understand the execution mode of SP? > > > > Thanks > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > --0015175d0334a99d9f0489de99c5 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > _________________________________________________________________ > Hotmail: as HOT as always > www.hotmailhotness.com.mx > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6d77eed59a0fe0489e2c99c