Queries that were well behaved in Version 7.0.4 no longer work in Version 8.0.2.

We ran into the following problem when trying to upgrade to Sybase Version 
8.0.2.  Queries that were well behaved in Version 7.0.4 no longer work in 
Version 8.0.2.


Here is a typical example of what is happening occurs for Table RTRONET:


Table RTRONET has the following characteristics:

CREATE TABLE RTRONET (APORT_KEY INT NOT NULL DEFAULT 0, CHILD_KEY INT NOT 
NULL DEFAULT 0, CHILD_TYPE SMALLINT NOT NULL DEFAULT 0, ANLCFG_KEY INT NOT 
NULL DEFAULT 0, AGGREGATED SMALLINT NOT NULL DEFAULT 0, CBPERIL_ID SMALLINT 
NOT NULL DEFAULT 0, MDL_RGN_ID SMALLINT NOT NULL DEFAULT 0, RRGN_KEY INT 
NOT NULL DEFAULT 0, BLOCK_ID INT NOT NULL DEFAULT 0, NETNET LONG BINARY);

CREATE UNIQUE INDEX RTRONET_I1 ON RTRONET (APORT_KEY, CHILD_KEY, 
CHILD_TYPE, ANLCFG_KEY, AGGREGATED, CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, 
BLOCK_ID);



In Version 7.0.4 we execute the following query:

05/04/2003 01:11:22:00456: fetchSQL()_2 SELECT CHILD_KEY, CHILD_TYPE, 
CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID, NETNET FROM RTRONET WHERE 
APORT_KEY = 3 AND ANLCFG_KEY = 1 AND CHILD_KEY > 0 AND CHILD_TYPE > 0 AND 
AGGREGATED = 0  ORDER BY APORT_KEY, CHILD_KEY, CHILD_TYPE, ANLCFG_KEY, 
AGGREGATED, CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID
05/04/2003 01:11:22:00460:         End fetchSQL()_2

The fetchSQL()_2 represents the place where we requested the record set. 
The End fetchSQL()_2 represents the place where the first block of records 
were actually returned.  Note that the return of the records is almost 
instantaneous.



In Version 8.0.2 We execute the same query:



05/04/2003 02:07:16:00013: fetchSQL()_2 SELECT CHILD_KEY, CHILD_TYPE, 
CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID, NETNET FROM RTRONET WHERE 
APORT_KEY = 3 AND ANLCFG_KEY = 1 AND CHILD_KEY > 0 AND CHILD_TYPE > 0 AND 
AGGREGATED = 0  ORDER BY APORT_KEY, CHILD_KEY, CHILD_TYPE, ANLCFG_KEY, 
AGGREGATED, CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID
05/04/2003 03:02:17:00231:         End fetchSQL()_2
05/04/2003 03:02:17:00231:         ===================== Elapsed time 
exceeds 30 seconds_2


Note in this case, it took 55 minutes to return the first record (This 
table has a couple hundred thousand records that we are trying to return).



We also tried adding all fields explicitly to the select and where clauses 
in the same order as they are in the index and this did not help.

In all cases, the plan for the query in Version 8.0.2 shows that the 
planner wants to create a temporary table and sort it rather than just use 
the index the way it did in Version 4.0.4.


Why is there this big difference in behaviour between 7.0.4 and 8.0.2.





0
Ray_Kincaid
5/5/2003 8:06:46 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

1 Replies
710 Views

Similar Articles

[PageSpeed] 30

1) Did you do an unload/reload to upgrade from 7.0.4 to 8.0.2?
    If not, then do so. The 8.x optimizer assumes an 8.x file structure,
while a 7.x database file can be run on an 8.x engine performance is likely
to suffer.

2) What is the setting for Optimization_goal? The default for 8.0.2 is
'all-rows'.  If you are looking to minimize the time taken to return the
first record (but not necessarily the entire result set) then you will
likely want to change this to 'first-row' for this query.

3) I would certainly suggest submitting the reproducible case to Tech
Support so that we can investigate.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
 choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Ray_Kincaid> wrote in message
news:3D3AA3D18C04449D006E7B8B85256D1D.006E7B9B85256D1D@webforums...
>
> We ran into the following problem when trying to upgrade to Sybase Version
> 8.0.2.  Queries that were well behaved in Version 7.0.4 no longer work in
> Version 8.0.2.
>
>
> Here is a typical example of what is happening occurs for Table RTRONET:
>
>
> Table RTRONET has the following characteristics:
>
> CREATE TABLE RTRONET (APORT_KEY INT NOT NULL DEFAULT 0, CHILD_KEY INT NOT
> NULL DEFAULT 0, CHILD_TYPE SMALLINT NOT NULL DEFAULT 0, ANLCFG_KEY INT NOT
> NULL DEFAULT 0, AGGREGATED SMALLINT NOT NULL DEFAULT 0, CBPERIL_ID
SMALLINT
> NOT NULL DEFAULT 0, MDL_RGN_ID SMALLINT NOT NULL DEFAULT 0, RRGN_KEY INT
> NOT NULL DEFAULT 0, BLOCK_ID INT NOT NULL DEFAULT 0, NETNET LONG BINARY);
>
> CREATE UNIQUE INDEX RTRONET_I1 ON RTRONET (APORT_KEY, CHILD_KEY,
> CHILD_TYPE, ANLCFG_KEY, AGGREGATED, CBPERIL_ID, MDL_RGN_ID, RRGN_KEY,
> BLOCK_ID);
>
>
>
> In Version 7.0.4 we execute the following query:
>
> 05/04/2003 01:11:22:00456: fetchSQL()_2 SELECT CHILD_KEY, CHILD_TYPE,
> CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID, NETNET FROM RTRONET WHERE
> APORT_KEY = 3 AND ANLCFG_KEY = 1 AND CHILD_KEY > 0 AND CHILD_TYPE > 0 AND
> AGGREGATED = 0  ORDER BY APORT_KEY, CHILD_KEY, CHILD_TYPE, ANLCFG_KEY,
> AGGREGATED, CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID
> 05/04/2003 01:11:22:00460:         End fetchSQL()_2
>
> The fetchSQL()_2 represents the place where we requested the record set.
> The End fetchSQL()_2 represents the place where the first block of records
> were actually returned.  Note that the return of the records is almost
> instantaneous.
>
>
>
> In Version 8.0.2 We execute the same query:
>
>
>
> 05/04/2003 02:07:16:00013: fetchSQL()_2 SELECT CHILD_KEY, CHILD_TYPE,
> CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID, NETNET FROM RTRONET WHERE
> APORT_KEY = 3 AND ANLCFG_KEY = 1 AND CHILD_KEY > 0 AND CHILD_TYPE > 0 AND
> AGGREGATED = 0  ORDER BY APORT_KEY, CHILD_KEY, CHILD_TYPE, ANLCFG_KEY,
> AGGREGATED, CBPERIL_ID, MDL_RGN_ID, RRGN_KEY, BLOCK_ID
> 05/04/2003 03:02:17:00231:         End fetchSQL()_2
> 05/04/2003 03:02:17:00231:         ===================== Elapsed time
> exceeds 30 seconds_2
>
>
> Note in this case, it took 55 minutes to return the first record (This
> table has a couple hundred thousand records that we are trying to return).
>
>
>
> We also tried adding all fields explicitly to the select and where clauses
> in the same order as they are in the index and this did not help.
>
> In all cases, the plan for the query in Version 8.0.2 shows that the
> planner wants to create a temporary table and sort it rather than just use
> the index the way it did in Version 4.0.4.
>
>
> Why is there this big difference in behaviour between 7.0.4 and 8.0.2.
>
>
>
>
>


0
Robert
5/12/2003 6:39:31 PM
Reply:

Similar Artilces:

superreview requested: [Bug 362139] bump SeaMonkey versions to 1.0.7/1.1 on 1.8.0/1.8 branches, localeVersion to 1.8.1 on 1.8 branch : [Attachment 246921] 1.8.0 patch: SeaMonkey version -> 1.0.7
Robert Kaiser <kairo@kairo.at> has asked neil@parkwaycc.co.uk <neil@httl.net> for superreview: Bug 362139: bump SeaMonkey versions to 1.0.7/1.1 on 1.8.0/1.8 branches, localeVersion to 1.8.1 on 1.8 branch https://bugzilla.mozilla.org/show_bug.cgi?id=362139 Attachment 246921: 1.8.0 patch: SeaMonkey version -> 1.0.7 https://bugzilla.mozilla.org/attachment.cgi?id=246921&action=edit ------- Additional Comments from Robert Kaiser <kairo@kairo.at> This is the 1.8.0 branch patch for bumping SeaMonkey version to 1.0.7 ...

superreview granted: [Bug 362139] bump SeaMonkey versions to 1.0.7/1.1 on 1.8.0/1.8 branches, localeVersion to 1.8.1 on 1.8 branch : [Attachment 246921] 1.8.0 patch: SeaMonkey version -> 1.0.7
neil@parkwaycc.co.uk <neil@httl.net> has granted Robert Kaiser <kairo@kairo.at>'s request for superreview: Bug 362139: bump SeaMonkey versions to 1.0.7/1.1 on 1.8.0/1.8 branches, localeVersion to 1.8.1 on 1.8 branch https://bugzilla.mozilla.org/show_bug.cgi?id=362139 Attachment 246921: 1.8.0 patch: SeaMonkey version -> 1.0.7 https://bugzilla.mozilla.org/attachment.cgi?id=246921&action=edit ...

New versions after 1.5.0.12 2.0.0.3 and 2.0.0.4 do not work properly
Name: Jelle van Buuren Email: jelleatvan-buurendotnet Product: Firefox Summary: New versions after 1.5.0.12 2.0.0.3 and 2.0.0.4 do not work properly Comments: Helleo, I'm sorry to say I am not a webdeveloper expert at all, just a loyal and up till now satisfied daily user. I got lost in your official bug-reports-system, so had to do it this way. I don't even know if my problem can be called a bug, but please take a look to f.i. www.nbrecords.com or www.ns.nl through the new browser FF 2.0 etc. and then do so through FF 1.5.0.12 or IExplorer and you will notice immedi...

Does ODAC 9.2.0.7.0 work with Oracle database version 9i 9.2.0.6.0 using VS2005?
I am using VS2005 and need to connect to an Oracle database. The Oracle database version is Oracle 9i 9.2.0.6.0 I have taken a look at the Oracle site (http://www.oracle.com/technology/software/tech/windows/odpnet/index.html). I can download the Oracle Data Access Component - Oracle9i Release 2 ODAC 9.2.0.7.0, but will this work with our current Oracle database version 9i 9.2.0.6.0 ? Any help appreciated. Regards, Paul. Apsolutely.Install it, it will work w/o problems. Regards,Dejan VesićMCAD for .Net technologies | http://www.vesic.org/english/ | Blog: http://www.vesic.org...

Version 2.0.0.7 vs 2.0.0.6
Name: Tina Rolston Email: trolstonatfranklindotk12dotgadotus Product: Firefox Summary: Version 2.0.0.7 vs 2.0.0.6 Comments: We are having problems with the updated version of FF. Is there any way I can get the 2.0.0.6 dmg to downgrade. We are running Infinite Campus software on MAC OS X and for some reason, the upgrade is not working. Please help!! Browser Details: Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4 ...

Version 2.0.0.6 repair Bugfixes to 2.0.0.7
Name: Patrick Dreier Email: patrick2406atsmsdotat Product: Thunderbird Summary: Version 2.0.0.6 repair Bugfixes to 2.0.0.7 Comments: Hello! Your can change to delete the Acount comleplete. The Webmail http:\\www.sms.at import to Mozilla Thunderbird. Please Entry the Emailformats StarMail sdm... to the Import Programm. Thank you! Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6 ...

2.0.0.2 to version 2.0.0.3
Name: Ed Strait Email: estraitateds-placedotcom Product: Firefox Summary: 2.0.0.2 to version 2.0.0.3 Comments: A week or so ago I was prompted to download and install version 2.0.0.3 as an upgrade from 2.0.0.2. I selected the upgrade and was running the ..3 version since. This morning, out of now where, it downloaded the .2 version and ask to to install which it looked as if I had no choice. After it restarted it was showing that I was running the .2. I then went and checked for any updates and it found the .3 again and installed it. Now I'm back to the .3 again. Any rea...

CodeWarrior Version 7.0 and SQL Anywhere Version 7.0.3 #2
I was working with CodeWarrior Version 5.0 and SQL Anywhere Studio Version 7.0.3 Now I am migrating to the version 7.0 of CodeWarrior, When having opened the previous version it asks me I want convert. I answer that yes. And when I compile the application the following message: Illegal use of precompiled header ulstore.h line 832 } Please Any help in this topics is appreciated. Thank you Attentivly Gloria Salas Hi Gloria; Try adding : #ifndef PILOT_PRECOMPILED_HEADERS_OFF #define PILOT_PRECOMPILED_HEADERS_OFF #endif to your application header. -- Davi...

upgrade from 4.0.1 install version to 4.0.2 install version, how?
Hi, I have a workable, clean 4.0.1 installed version of DNN running and am trying to upgrade to 4.0.2. i tried to follow the instruction on the documentation unzip the DNN_402_Install and put in corresponding directory in 4.0.1's website, run up the website and upgrade it. but it keeps hung up on the upgrade screen and going nowhere.  what files exactly should i extract and place in where, any special commands for running this upgrade? thank you very much in advance.  ...

superreview granted: [Bug 336772] bump SeaMonkey version to 1.0.2 on 1.8.0 branch : [Attachment 220956] bump version number to 1.0.2
neil@parkwaycc.co.uk <neil@httl.net> has granted Robert Kaiser <kairo@kairo.at>'s request for superreview: Bug 336772: bump SeaMonkey version to 1.0.2 on 1.8.0 branch https://bugzilla.mozilla.org/show_bug.cgi?id=336772 Attachment 220956: bump version number to 1.0.2 https://bugzilla.mozilla.org/attachment.cgi?id=220956&action=edit ...

superreview requested: [Bug 336772] bump SeaMonkey version to 1.0.2 on 1.8.0 branch : [Attachment 220956] bump version number to 1.0.2
Robert Kaiser <kairo@kairo.at> has asked neil@parkwaycc.co.uk <neil@httl.net> for superreview: Bug 336772: bump SeaMonkey version to 1.0.2 on 1.8.0 branch https://bugzilla.mozilla.org/show_bug.cgi?id=336772 Attachment 220956: bump version number to 1.0.2 https://bugzilla.mozilla.org/attachment.cgi?id=220956&action=edit ------- Additional Comments from Robert Kaiser <kairo@kairo.at> OK, here's the patch. If still check in before the freeze today nightnight, we can go without additional drivers' approval, else we need to request that as well. ...

Upgraded from 4.0.2 to 4.2.1
Hi, I followed the instructions on http://www.bugzilla.org/docs/4.2/en/html/upgrade.html to upgrade from 4.0.2 to 4.2.1. When I go to the login page in the browser, it shows the version as 4.0.2 rather than 4.2.1. How can I tell what version of Bugzilla I really have installed? Thanks for your help. - Melissa PS At my company, Bugzilla is running on OpenSolaris. ...

multiple tabs in firefox 2.0.7 and 2.0.8 do not function well while using MAC OS 10.4.8
Name: Hans van den Boomen Email: rommeligatchellodotnl Product: Firefox Summary: multiple tabs in firefox 2.0.7 and 2.0.8 do not function well while using MAC OS 10.4.8 Comments: After opening a new tab I only see an enpty screen and on the left a very small detail of the new tabscreen which i cann't enlarge. I encountered this problem on my MAC with OS Tiger version 10.4.8 Browser Details: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; nl; rv:1.8.1.8) Gecko/20071008 Firefox/2.0.0.8 ...

XForms 0.8.0.2 release not working on linux firefox 2.0.0.4
Hi, I just upgraded the xforms to 0.8.0.2 from 0.8.0.1 on firefox (linux) 2.0.0.4. All xhtml pages stopped rendering. Is this the issue with the new release or I should look something into my code. Thanx for help in advance. -regards, Piyush A problem also on Mac OS X version 0.8.0.2. It doesn't install not valid hash file (probably corrupted download). -261 On 5 Lug, 13:49, Piyush <piyush.sin...@gmail.com> wrote: > Hi, > > I just upgraded the xforms to 0.8.0.2 from 0.8.0.1 on firefox (linux) > 2.0.0.4. All xhtml pages stopped rendering. &g...

Web resources about - Queries that were well behaved in Version 7.0.4 no longer work in Version 8.0.2. - sybase.sqlanywhere.general

Notes and Queries - Wikipedia, the free encyclopedia
it is now published by Oxford University Press . The journal was originally subtitled "a medium of inter-communication for literary men, artists, ...

How Does Facebook Handle Graph Search Queries In A Timely Fashion?
How is Facebook able to quickly process the sort of queries about users and their friends generated by features such as Graph Search , despite ...

Facebook tests ad placements on Graph Search results page, not related to queries
Facebook began a small test today that inserts FBX and Marketplace ads between pages of Graph Search results, a spokesperson tells us. These ...

faroo_p2p: Six month after launch the FAROO Search API http://t.co/RQlyOBZC serves 24 million queries/month ...
faroo_p2p: Six month after launch the FAROO Search API http://t.co/RQlyOBZC serves 24 million queries/month. A tenfold increase in the last month. ...

App Store - IDdx: Infectious Disease Queries
Get IDdx: Infectious Disease Queries on the App Store. See screenshots and ratings, and read customer reviews.

Michael Jackson queries - Google - Flickr - Photo Sharing!
Explore Search Engine Land's photos on Flickr. Search Engine Land has uploaded 1714 photos to Flickr.

'Safari UniBar' Brings Searches and Web Queries to One Unified Bar - YouTube
Retweet: http://clicktotweet.com/27wfR Name: Safari UniBar Description: Combined web queries and search from one bar, just like Google Chrome. ...

Spaced out: NASA denies world's end as queries skyrocket
LOS ANGELES: If there's one US government body really looking forward to December 22, it's NASA. The space agency said it had been flooded with ...

Virgin queries Qantas on Tasman
Virgin Australia has urged the competition regulator to stop Qantas and Emirates from forming an alliance on the trans-Tasman route unless there ...

Firefox 14 protects search queries from 'bad guys,' not advertisers
Firefox 14 protects search queries from 'bad guys,' not advertisers Australian Macworld However, search engine guru Danny Sullivan from Search ...

Resources last updated: 11/22/2015 9:11:59 AM