SQLAnywhere INNER JOINS Differ from 5.0.03 to 5.5.04?????

I have the following query in SqlAnywhere:

SELECT
    ( IF sn.IsMainName = '1' THEN
      sn.ShFullName
    ELSE
      left( String( sn.ShFullName, ' (', sh.ShFullName, ')'), 40)
    ENDIF )
    AS ShFullName,
    c.ClntChrtID,
    c.ClntActvtyStatus AS _,
    Sh.PrsnDob, Sh.PrsnSex, Sh.ShId, Sh.Status, a.shPhone1,
    a.shAddrss1, a.shAddrss2, a.shCity
    FROM ShName sn
    JOIN Sh ON sn.ShId = sh.ShId
    JOIN Clnt c on c.ClntShId = sh.ShId
    LEFT OUTER JOIN ShAddrss a ON c.ClntShId = a.ShId  AND a.OwnrHwSiteId =
c.OwnrHwSiteId
    WHERE Sh.Status = 'A'
    AND sn.Status = 'A'
    AND sn.ShFullName like 'SomeNameISubInHere%'
    AND c.OwnrHwSiteId = GetThisSite(*)
    ORDER BY sn.ShFullName

This query runs in well under a second in SQLA 5.0.03.  However, its taking
8-10 mins in SQLA 5.5.04.  Some things to note:
1)  Clnt has 0 records
2)  ShName, and Sh have about 70 000 records each
3)  ShAddrss has about 1000 records.

In both versions, the query plan shows that the proper primary keys and
indexes are being used.  None of the tables are accessed seqentially.
Because of the data in this case, the query should return nothing.  In both
cases it returns nothing, but 5.5.04 takes way to long.  (Its like its doing
all outer joins and then getting rid of stuff after the fact).

I'd appreciate any help,
Thanks,
Chris Pettingill



0
Chris
5/2/1998 12:39:01 AM
sybase.sqlanywhere.general 32637 articles. 22 followers. Follow

5 Replies
677 Views

Similar Articles

[PageSpeed] 4
Get it on Google Play
Get it on Apple App Store

Try:
 WHERE Sh.Status = 'A'

Or try:
 WHERE sn.Status = 'A'

Just not both at the same time.

You are already joining on those two columns anyhow so you might try
specifying at one end or the other.  I don't know your data so this may be
a wild good chase but I had some luck with a similar method a couple years
ago.

-- 
Jim Egan [TeamPS]
Dealer Solutions, LLC
Houston, TX
0
Jim
5/2/1998 12:39:08 AM
Sorry, that would be "wild goose chase" not "a wild good chase".  Don't
know where my mind was....
-- 
Jim Egan [TeamPS]
Dealer Solutions, LLC
Houston, TX
0
Jim
5/4/1998 11:49:19 AM
Unfortunately, I need to check both sh.Status and Sn.Status.  (Its actually
sh.Shid and sn.Shid that I'm joining on).  I tried fooling with this a bit,
but it didn't work.  I realized however, something that I missed before.  In
5.0.03 the optimizer seqentially searches through the Clnt table first,
which in this case is good because there are very few records in Clnt 0 -
1400 compared to about 70 000 in ShName and Sh.  In 5.5.04, It uses the
primary keys/indecies of all of the tables involved, and Clnt, is the third
one joined in so I go through joining 70 000 records in the other 2 tables
first.  The optimizer does not seem to be working as smart in this case.  I
don't want to force the optimizer to always search on Clnt first however,
because most sites have roughly the same number of records in Clnt, Sh, and
ShName.  Its a special case at a few sites where Clnt has such few records
compared to the number in Sh and ShName.  One interesting thing I did
notice.  In 5.5.04, when I 'Order By ShFullName' (the first column), I get
the poor optimization and performance.  But, if I 'Order By 1', I get the
sort of results I'd expect.  Unfortunately, this query is actually part of a
bigger query, and the Order By clause gets changed dynamically at run time
to possibly include any of the return columns in several different orders.
I'd rather find another solution than using column numbers rather than names
if I could.  One other strange thing, I tried giving the optimizer very
dramatic 'hints' (to force it to search on Clnt first), but it seemed to
ignore them.

Any ideas?

Thanks


0
Chris
5/5/1998 3:27:57 PM
No good ideas, but I have seen something very similar.  I have a script
that joins to a table with 0 or just a handful of rows, using a foreign
key, and results in an ISQL estimate of 2 billion rows.  The only reason it
comes back quickly is because my name and address tables are pretty small
(< 5000 rows).  If the name table had 70,000 rows, like yours, I suspect
this would take many minutes to find no rows.

I know that dropping the optimizer statistics "fixes" this anomaly.  In my
case it is harmless, I believe, because the database is almost brand new. 
I don't know if there is some magical number of rows that you need to have
in your 0 row table that might cause better behavior.

I also can confirm seeing lots of optimizer changes, or rather, lots of
negative side effects as the result of some optimizer changes, somewhere
around 5.5.01  I have alot of ugly selects that used to work fine in 4.0d,
and 5.0something, that went off the charts in a bad way all of a sudden. 
Many of them involve views, which your script doesn't.  In many cases I am
also unable to force a good plan using estimates.  I'm working with
technical support on one of these right now.  If I learn anything that
might apply to your case I'll let you know.


Chris Pettingill <ChrisPettingill@compuserve.com> wrote in article
<XcG3rkCe9GA.166@forums.powersoft.com>...
> Unfortunately, I need to check both sh.Status and Sn.Status.  (Its
actually
> sh.Shid and sn.Shid that I'm joining on).  I tried fooling with this a
bit,
> but it didn't work.  I realized however, something that I missed before. 
In
> 5.0.03 the optimizer seqentially searches through the Clnt table first,
> which in this case is good because there are very few records in Clnt 0 -
> 1400 compared to about 70 000 in ShName and Sh.  In 5.5.04, It uses the
> primary keys/indecies of all of the tables involved, and Clnt, is the
third
> one joined in so I go through joining 70 000 records in the other 2
tables
> first.  The optimizer does not seem to be working as smart in this case. 
I
> don't want to force the optimizer to always search on Clnt first however,
> because most sites have roughly the same number of records in Clnt, Sh,
and
> ShName.  Its a special case at a few sites where Clnt has such few
records
> compared to the number in Sh and ShName.  One interesting thing I did
> notice.  In 5.5.04, when I 'Order By ShFullName' (the first column), I
get
> the poor optimization and performance.  But, if I 'Order By 1', I get the
> sort of results I'd expect.  Unfortunately, this query is actually part
of a
> bigger query, and the Order By clause gets changed dynamically at run
time
> to possibly include any of the return columns in several different
orders.
> I'd rather find another solution than using column numbers rather than
names
> if I could.  One other strange thing, I tried giving the optimizer very
> dramatic 'hints' (to force it to search on Clnt first), but it seemed to
> ignore them.
> 
> Any ideas?
> 
> Thanks
> 
> 
> 
0
Rick
5/5/1998 6:48:18 PM
Those are very interesting results.  I'm sure that the Sybase tech support people would like to hear
this.  They are usually very interested in strange optimizer results.  Can you call this in?

Leo Tohill - Team Powersoft
-- Please post in newsgroup, not via email <
0
leotohill
5/6/1998 3:24:13 AM
Reply:

Similar Artilces:

5.5.03 OR 5.5.04 ?
I've just installed the 5.5.04 maint release. On opening my database (created in 5.5), its properties state that the version is 5.5.03. As many have no doubt upgraded to 5.5.04, could someone pls advise if this is 'correct' behaviour? Many thanks in advance -- MAX HUGEN Hugen Enterprises Pty Ltd Email: maxhugen@ozemail.com.au Tel: +612 9560 3061 Fax: +612 9564 5073 >I've just installed the 5.5.04 maint release. On opening my database >(created in 5.5), its properties state that the version is 5.5.03. >As many have no doubt upgraded to 5.5.04, could...

5.5.03 or 5.5.04??
I hope this has not been asked to death? But I will be upgrading our test system from 5.5.02 build 1495 to either 5.5.03 or 5.5.04. First is there anything in 03 that is not included in 04? Second, are there any known problems with 04? I'll listen to facts, rumors, opinions, etc. TIA Shannon 5.5.04 seems pretty stable. -- Terry Black HCIA, Inc. e-mail:tblac@hcia.com Shannon Griffith wrote in message ... >I hope this has not been asked to death? But I will be upgrading our test >system from 5.5.02 build 1495 to either 5.5.03 or 5.5.04. First is there >a...

outer joins and union ... differences between 5.5.04 and 5.5.05 !
Hi, wa have a very peculiar problem on sql anywhere 5.5.04 server and client the following select fails sometimes SELECT 1, "tparam"."lib_parm", "tpers"."nom_pers", "tlibrues"."lib_rue", "tadr"."num_rue", "tadr"."num_boit", "tcplib"."c_postal", "tcplib"."lib_lclt", "tpers"."langue" FROM {oj "tpers" LEFT OUTER JOIN "tparam" ON "t...

ASA6 or SQLAnywhere 5.5 on Netware 5.0?
This is a multi-part message in MIME format. --------------E6256398D09B1293B9B2E246 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, Does anyone know if either ASA6.0 or SQLAnywhere 5.5x will run on Netware 5.0? I've asked Sybase several times (in the past 3 month period) and I still haven't received an answer. TIA, Bernard Mikowski --------------E6256398D09B1293B9B2E246 Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Bernard Mikowski Content-Di...

Upgrading 5.0.03 to 5.5.04
I need to upgrade several client sites from 5.0.03 to 5.5.04 (authenticated version). Many of the sites are very far away, and have little (or none) on-site tech people. Often times, sites aren't aware that they have a server, or which computer it is. Most of our support is done via PC-Anywhere. However, we are seldom able to dial in to the server, but instead have to dial in to a client machine. Some sites are using NT servers, and others are using Novell. I realize the service manager on the server will need to be stopped for the upgrade to occur, but other than that, I hop...

Dbremote 5.5.0 and 5.5.03 Crash.
Have a problem with dbremote crashing. I have 4 remote sites. At one of the remotes site and the consolidated site, dbremote crashes (GPF or something) after it prints Processing Active Transaction Log and before it prints anything else. This has had some unfortunate side effects. It appears that data was getting to the consolidated site, but not back out. Except for the one remote site. Any ideas? Wayne@aprompt.com Wayne, I have not had GPF problems but did have trouble similar to yours where stuff was coming in okay from the remotes but not replicating out correctly....

SQLAnywhere 5.5.04ebf(1868), 5.5.05
Hello! I have recieved message from "owner-inews-tech-sqlanywhere@sybase.com" with Subject "Delphi users". Where can I obtain new ebf(s)? I found only 5.5.03 in the Software library. (http://support.sybase.com/esd/sqlany.html) ==== Subject: Bug Fix / Enhancement (PTrack 479443) - .... Summary: Delphi can't insert Blobs > 32k - SQLBindParameter only inserted the first 32k of a given buffer. Versions affected: 5.5.x Versions fixed: 5.5.04ebf(1868), 5.5.05 P4 change number: 52135, 52298 Modules affected: wod50 .... === You'll have to contact Tech ...

Behavioural differences between 5.5.04 and 5.5.05
We are looking at going to the latest EBF for 5.5.05. Are their any behavioural differences between 5.5.05 and 5.5.04 other than bug fixes. We are currently running Build #1921 5.5.04 on WINNT. Thanks, Todd Thompson A difference that I have encountered is in using the db_backup() functions to mimic the functionality of the Dbbackup utility. When backing up the log file you can't read pages until SQLE_NOTFOUND is returned because it returns SQLE_BACKUP_NOT_STARTED after all pages have been read. Todd L.T. wrote in message <39c26c79@rpc1284.daytonoh.ncr.com>......

5.5.03->5.5.04 upgrade
I'm running 5.5.03. Can you download the latest updates from FTP and upgrade 5.5.03 to the latest 5.5.04, or does the .03-.04 upgrade have to be purchased somewhere first? George Hale SSC Group, Inc. Kerrville, TX Overlooking the Guadalupe You can download the upgrade for free from support.sybase.com. You'll have to register yourself to get a login ID, but that's free too. -- Jim Egan [TeamPS] Dealer Solutions, Inc. Houston, TX See you at the Powersoft User Conference in August - http://www.sybase.com/events/psuc98 George Hale <sschale@ktc.co...

5.5.04 Creates 5.5.03 databases
Why does 5.5.04 create 5.5.03 databases? All of the files are the same date, 10/1/97, including DBINIT. I created the database using DBINIT and Sybase Central. Jack T. >Why does 5.5.04 create 5.5.03 databases? All of the files are the same >date, 10/1/97, including DBINIT. I created the database using DBINIT and >Sybase Central. I suppose you are looking at the version information from sql central. This shows the version of the db file format. The file format does not change with every release, and in fact did not change with the 5.5.04 release. Regards, ...

5.5.03 Server + 5.5.0 Clients?
We have an OS/2 server running the 5.5.0 engine. Sometimes we encounter assertions and would like to apply the patch 5.5.03. All clients are remote and communicate over IPX/SPX. Some clients use ODBC, others use ESQL. Is it possible to apply the patch to the server without updating the clients? Thanks for your help Tobias -- ====================================================================== Dipl.-Ing. Tobias Krueger Tel.: +49 6103 5881-41 Giegerich & Partner GmbH Fax: +49 6103 5881-49 Daimlerstrasse, D-63303 Dreieich eM...

From SQLAnywhere 5.5.0.4 to ASA in PB6.5
Hi all, I am upgrading our single user PB application to a multiuser application We are using as backend Adaptive Server Anywhere 6.0.1.1165. Now I have a serious problem: when insert a row into a table, which has an autoincremental field as primary key, the new key value is not returned to my datawindow/datastore and I really have selected the primary key as identifier. I have been looking in the file PBODB60.ini but I have not found a section with [ADAPTIVE SERVER ANYWHERE]. Should there be such a section ??? Is there someone who can help with getting my autoincremental ke...

SQLAnywhere 5.5
We are running SQLAnywhere on a Novell 4.11 server. The DBSRV50.NLM is version 5.5.00 Build #1073. I am assuming this is the version of the database we are running of SQLAnywhere? Does this version support SMP? We are thinking of ordering another processor for the server, but don't want to waste money is the version of SQLAnywhere is not SMP aware or capable of using the extra processor? Thanks, Keith I already answered this in the general newsgroup. Please do not cross post. -- Jim Egan [TeamSybase] Houston, TX I only crossed posted because this group has al...

SQLAnywhere 5.5
We are running SQLAnywhere on a Novell 4.11 server. The DBSRV50.NLM is version 5.5.00 Build #1073. I am assuming this is the version of the database we are running of SQLAnywhere? Does this version support SMP? We are thinking of ordering another processor for the server, but don't want to waste money is the version of SQLAnywhere is not SMP aware or capable of using the extra processor? Thanks, Keith SQL Anywhere does not support SMP, ASA does but not on Novell. But, it won't hurt to add another processor. The OS will continue to use one and SQL Anywhe...

Web resources about - SQLAnywhere INNER JOINS Differ from 5.0.03 to 5.5.04????? - sybase.sqlanywhere.general

IBM Tivoli Storage Manager - Wikipedia, the free encyclopedia
IBM Tivoli Storage Manager ( TSM or ITSM ) is a centralized, policy-based, enterprise class, data backup and recovery package. The software enables ...

Archives - Caelum's Blog
Caelum's Blog Random Stuff Navigation Home - Articles Tags 256colors 64 64bit 8 activeperl activestate advent ajax alsa amd64 asa asus automation ...

keynote bingo - Google Search
Search Images Maps Play YouTube News Gmail Drive More Calendar Translate Mobile Books Wallet Shopping Blogger Finance Photos Videos Even more ...

Mobile and Wireless Partners - Partners - Sybase Inc
Thanks for visiting the Partners section of Sybase.com. Here you will find information about Mobile and Wireless Partners - Partners. For more ...

Datensynchronisierung - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Browse file extension list beginning with letter A
Browse file extension list beginning with letter A - File-Extensions.org search page

OpenLink ODBC Adapter for Ruby on Rails: OpenLink ODBC Adapter for Ruby on Rails: Downloads
OpenLink ODBC Adapter for Ruby on Rails: OpenLink ODBC Adapter for Ruby on Rails: Downloads

Developer Edition - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

IBM - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Datenaustausch - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Resources last updated: 11/25/2015 6:11:36 PM