different plans

While working (in past), i have seen few instances of query
plans being different when the query is executed by itself
and the same query is called from inside a procedure.

Anyone else have encountered such issues and any specific
action taken to resolve these issues?
0
jobless
12/6/2010 5:21:08 PM
sybase.ase.performance+tuning 2395 articles. 0 followers. Follow

2 Replies
568 Views

Similar Articles

[PageSpeed] 38

> While working (in past), i have seen few instances of
> query plans being different when the query is executed by
> itself and the same query is called from inside a
> procedure.
>
> Anyone else have encountered such issues and any specific
> action taken to resolve these issues?

Yes, there are instances of the above particularly if
variables are involved. At the time that the query plan is
generated for a stored procedure, the value assigned to the
variable is not known. This is because the actual value is
not assigned until the stored procedure is run. Therefore
(typically) heuristical values are used to determine
selectivity. These may or may not be accurate. On the other
hand, with ad-hoc SQL the values can be known because the
assignment is done prior to the execution.

Michael Mamet
0
Michael
12/8/2010 10:12:19 PM
> > While working (in past), i have seen few instances of
> > query plans being different when the query is executed
> > by itself and the same query is called from inside a
> > procedure.
> >
> > Anyone else have encountered such issues and any
> > specific action taken to resolve these issues?
>
> Yes, there are instances of the above particularly if
> variables are involved. At the time that the query plan is
> generated for a stored procedure, the value assigned to
> the variable is not known. This is because the actual
> value is not assigned until the stored procedure is run.
> Therefore (typically) heuristical values are used to
> determine selectivity. These may or may not be accurate.
> On the other hand, with ad-hoc SQL the values can be known
> because the assignment is done prior to the execution.
>
> Michael Mamet


At one of the places i recall having same issue with
variables(on a recently upgraded ASE instance to 15.0.3
esd#3):

The proc was generating bad plan. Query by itself ran fine.
The change was done to declare the variable within the proc
which worked fine:
(datatypes were verified, stat were double checked)

create proc p1 ( @var1 int, @var2 varchar(10))
as
..... query was using @var1 and @var2

Changed To:

create proc p1 ( @var1 int, @var2 varchar(10))
as
declare @var1_new int
declare @var2_new varchar(10)
select @var1_new = @var1
select @var2_new = @var2
..... query was changed to use @var1_new and @var2_new

The procedure then generated expected right plan. Revert
back to old proc version it created bad plan. Anyone want to
post-mortem the behaviour!
0
jobless
12/9/2010 5:29:52 PM
Reply:

Similar Artilces:

ASE optimizer behavior differences and Performance differences between ASEs: 11.50/ 11.9.2
Hi, I wish I could report that the 11.9.2.4 ASE is performing better then our 11.50 AS instances, but then I would probably not be submitting this post. I have two similar ASE instances which appear to have identical table,indexes,segments,devices. The row counts of the two tables are only off by 1000 and that is expected. Showplan reveals that the optimizer is behaviing very differently between the two instances , not supprised. The new 11.9.2.4/1175/P/SWR/9520 ASE is performing poorly and I can not proced with upgrading my other servers to 11.9.2 untill I accertain the performanc...

ASE 15 Performance and Tuning Guides
Greetings, Does anyone know when the updated ASE 15 versions of "Performance and Tuning Guides" will be released? The guides on sybooks are for ASE12.5.1 Thanks in advance. Alberto > Greetings, > > Does anyone know when the updated ASE 15 versions of > "Performance and Tuning Guides" will be released? I *think* that they were supposed to be released sometime later this year, but I don't have any clear information. Michael I'm not aware of when these will be released. I'm guessing some time after the 15.0.2 release. Ho...

Very different plans and performance in 2 servers.
This is a multi-part message in MIME format. ---=_forums-2-dub422dd95b Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit Hi All, We run the same stored procedure in two different servers. One is 12.0.0.6 ( slower 2 cpu) version, the second 12.0.0.8 ( faster 3 cpu). Both have the same data base (dump and load). In 12.0.0.6 the sp uses worker processes and it runs 12 seconds. In 12.0.0.8 the sp doesn't use the same plan and without using sort_merge on it ran 12 minutes. Using sort_merge on decreased it to 90 sec. But if I rep...

Difference between plans from different sources
Greetings hi-teks, what's the difference between a plan shown using Index Consultant in ASA 9.0.2.2551 and a plan captured using LogExpensiveQueries on ASA 9.0.2.3228. Thanks, Pavel Pavel Karady wrote: > > what's the difference between a plan shown using Index Consultant in ASA > 9.0.2.2551 and a plan captured using LogExpensiveQueries on ASA 9.0.2.3228. > Plans are affected by a number of factors: - isolation level of current connections - current state of the cache - optimization goal of the current connection - etc... It is possible to ...

Performance differences between System 10 and ASE 11.5
On a IBM Risc machine, OS AIX 4.2.1 with 128M of RAM, HD 4 Gb. SCSI Is possible to obtain better times in SQL Server 10.0.2 that in ASE 11.5? Both are setting the same values for the configuration parameters. Thanks Mariana Arcadia arcadia@impsat1.com.ar Mariana Arcadia wrote: > > On a IBM Risc machine, OS AIX 4.2.1 with 128M of RAM, HD 4 Gb. SCSI > > Is possible to obtain better times in SQL Server 10.0.2 that in ASE > 11.5? yes it is. You probably need to add more memory to your host/sql server. The Sybase ROT in upgrading from 10.x to 11.5 i...

Check List for ASE 11.9.2 Performance Tuning
Dear all, We need to troubleshoot the performance issue in an ASE 11.9.2 server. During the system in a slow response time state, about 20 Sh_page locks (Non Cursor Lock) found on the syslogins when I execute the sp_lock. Can anyone give me some advise on which areas I should check first (may be a check list) ? Cheers, Jack You might want to read through the Performance & Tuning guide for things like query performance management with things like sp_showplan and sp_sysmon. What you could consider is this. Any running task may be running with a bad query plan. Her...

Differing performance response to multithreaded application between two ASE 12.5 servers
I have an unusual problem with the performance of a prototype enhancement I am developing for a proprietary utility. This utility takes data in the form of flat files from a directory and posts the data into several tables on a database. Currently it is implemented as a C binary that is called from a perl script using a system() call. The C binary connects to the database using ct-lib. In order to make things more efficient, the plan is to encapsulate the whole thing as a single binary and to maintain the connections required over the lifetime of that process. As part of this...

different exchang plans on different resellers
Hallo, i have a question about the assignment of exchange plans to OU's. I understand that every reseller see all available exchange plans in the solution. Is it possible to reduce this. It must be that we have for example four exchange plans. Reseller 1 should see plan 1 and 2, reseller 2 should see only plan 3 and 4. This should be also be for the customers which are under the reseller.  Today alle reseller see all plans. Is there another function to reduce this or to assign the plans not only to customer also to reseller I understand what your saying, but that's not what...

Performance differences w/ different OS
Hi all, is there any information about performace differences on different operating systems. One of our customers will upgrade to 7.x and here is the chance to change the OS. Any information is welcome. Thanks Holger Holger Dehnhardt wrote: > Hi all, > > is there any information about performace differences on different operating > systems. > One of our customers will upgrade to 7.x and here is the chance to change > the OS. > > Any information is welcome. > > Thanks Holger I find it quite fast on NetWare, certainly faster t...

Difference between ASE and ASE Small Business
Can anyone tell me the general differences between ASE and ASE Small Business? I'm assuming there are some functionality differences, but have been unable to locate that information (a comparison list, etc.) on sybase.com. There are really 3 versions: ASE Enterprise, SBE, and Developer. A Developer license allows 25 users as of 12.5.2 ESD1. From 12.5.1 through 12.5.2 GA, the limit was 5. Only 1 CPU is permitted, which effectively means no parallelism. However, almost all of the licensed features are enabled. Only EFTS and DBXRAY are not. An SBE license allows 256 users and 4...

Differences between ASE 12 and ASE 12.5
Hi! I am in the process to migrate mi ASE from version 11.5.1 to an upper one. I am thinking that the logic step is to go to ASE 12, but I was wondering about the version 12.5. would there be any strong reason why I should choose to upgrade to version 12.5 other than just jumping to a newer version than 12. ? What are the main differences between ASE versions 12 and 12.5? greetings !! =:D We are going from 11.9 to 12.5, skipping 12.0. We have two reasons. First, because we are tired of making regular upgrades and figure we can stay on 12.5 for years whereas...

RecordMyDesktop vs Istanbul
I've tried using both RecordMyDesktop and Istanbul to do some video capture of applications. On my home PC (openSUSE 11.3) then RecordMyDesktop has terrible performance and doesn't capture most of what happens (close a window and re-open it and if you took long enough then it might disappear briefly in the video) but Istanbul does okay, even with capturing Compiz effects. On my work PC (Fedora 13) then Istanbul is passable, but the mouse movements are jerky even under Metacity, where as RecordMyDesktop does clean and smooth captures, even with Compiz effects. Does anyone k...

huge performance difference on systems that I think should perform similarly
I have an application that runs on ASA7 for which I am trying to explain some performance differences. The same software version (both the application and ASA7) is installed on a desktop that has a 2.8Ghz Pentium and a server that has dual 2.8Ghz Xeons. The same task (input file) on the same database file (copied from one to the other) which is deleting alot of data and inserting new data takes 2 hours on the desktop but 4 hours on the server. The question I need to answer is why is there such a huge difference and hopefully have some way of making the server at least come close to ...

same query, two different approaches, vastly different performance
I asked this on Stackoverflow and on Perlmonks, but hopefully I will get = a more satisfactory and revealing insight straight from the DBI folks. I have a Postgres table with more than 8 million rows. Given the = following two ways of doing the same query, I get wildly different = results. $q .=3D '%'; ## query 1 my $sql =3D qq{ SELECT a, b, c FROM t=20 WHERE Lower( a ) LIKE '$q' }; my $sth1 =3D $dbh->prepare($sql); $sth1->execute(); ## query 2 my $sth2 =3D $dbh->prepare(qq{ SELECT a, b, c FROM t =20 ...

different plan...
We have serverA running ASE15.0.2 on UnixboxA; We created serverB on UnixboxB by coping same binary and config file for ServerA. Create the 'user' database same as on ServerA. Dump and loaded the database 'D1' from ServerA to ServerB. We run one proc on serverA and ServerB, it creates different plans. since we dump and load D1, the stats would be same. What else would affect the optimizer to select a different plan on the serverB? Does the proc reference any objects in another database (either on the same dataserver or via a proxy in a remote dataserver)? ...

Web resources about - different plans - sybase.ase.performance+tuning

Best Black Friday 2015 deals for laptops from around the interent
Predicted to be the biggest ever Black Friday, here are this year's best offerings for laptops from shops such as Currys, Ebay and Lenovo

Action Movie Kid Carves His First Thanksgiving Turkey With a Special Effects Light Saber
James Hashimoto , the star of the ongoing video series “Action Movie Kid” carved his first Thanksgiving turkey with a light saber thanks to the ...

The latest Star Wars: The Force Awakens TV spot dives into the dark side
We're just a few weeks out from Star Wars: The Force Awakens' worldwide premiere, and Disney and Lucasfilm have offered up a tasty Thanksgiving ...

The $5 computer that can change the world
The world collectively dropped its jaw when the Raspberry Pi was first unveiled. A $35 computer that could be used to power just about anything? ...

Thanksgiving / Football Open Thread
Happy Thanksgiving from all of us at Crooks and Liars. We are very grateful for YOU, our readers and supporters. Here's the NFL schedule for ...

UPDATE 4-Britain's Cameron says time to bomb militants in Syria
Telegraph.co.uk UPDATE 4-Britain's Cameron says time to bomb militants in Syria Reuters ... * Cameron says Britain cannot subcontract defence. ...

Mohamed Abrini seen with Salah Abdeslam before Paris attacks had visited Syria
The terror suspect spotted driving with Paris attacker Salah Abdeslam is thought to have fought in Syria before secretly returning to Europe. ...

Donald Trump Is Playing Adele At Rallies
There are two certainties in this world: Everyone is listening to Adele, and Donald Trump is playing music at his rallies that its creators don't ...

Frank Gifford’s Family Says CTE Found In Brain, Still Supports NFL
Late NFL Hall of Famer Frank Gifford was suffering obvious symptoms of chronic traumatic encephalopathy, his family said, and donated his brain ...

9to5Toys Turkey Break: iPad Air 2 $374, Apple Watch $100 off, iTunes Gift Cards BOGO 40% off, more
The best iPad deals (so far): Air 2 from $374 (Reg. $499), free gift card offers , more Apple Watch Deals: Save up to $100 at Best Buy – Sport ...

Resources last updated: 11/26/2015 8:11:03 PM