What's the PLAN on remote 2.0.3? Just wondering if this is a huge table and indexes on Name or CompanyName are missing (it has to go NATURAL on C, but should use at least one index for C2).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robin Davis
Sent: 24. oktober 2007 16:44
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Why has this dupe search statement stopped working over TCP/IP?

Hi Set,

Thank you for your more refined query, it works on 2.0.1 remote and
2.0.3 local just fine. However, the same problem exists with remote 2.0.3.

Very strange.

Rib Davis


Svein Erling Tysvær wrote:

> Are you sure it works on a local machine? I wasn't aware that you could compare EXISTS with true/false (it may be possible, I've just never seen that before). Try this "simplified" statement:
>
> SELECT c.CONTACTID, c.NAME, c.COMPANYNAME, c.ADDRESS1, c.ADDRESS2,
> c.ADDRESS3, c.ADDRESS4, c.POSTCODE, c.WORKPHONE, c.WORKEXTENSION,
> c.MOBILEPHONE, c.EMAILNAME
> FROM Contacts AS c
> WHERE Exists (select * from Contacts c2
> where c2.Name = c.Name
> and c2.CompanyName = c.CompanyName
> and NOT (c2.ContactID = c.ContactID))
> ORDER BY 2, 1;
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robin Davis
> Sent: 24. oktober 2007 16:07
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Why has this dupe search statement stopped working over TCP/IP?
>
> Hi,
>
> I'm obviously losing my marbles somewhere, but I seem to have the
> strangest problem.
>
> Using a 2.0.1 remote server on XP, this query works fine.
>
> SELECT c.CONTACTID, c.NAME, c.COMPANYNAME, c.ADDRESS1, c.ADDRESS2,
> c.ADDRESS3, c.ADDRESS4, c.POSTCODE, c.WORKPHONE, c.WORKEXTENSION,
> c.MOBILEPHONE, c.EMAILNAME
> FROM Contacts AS c
> WHERE (((Exists (select * from Contacts c2
> where c2.Name = c.Name
> and c2.CompanyName = c.CompanyName
> and NOT (c2.ContactID = c.ContactID)))<>False))
> ORDER BY 2, 1;
>
> Using the same query on a remote 2.0.3 server, latest release, the query
> stalls and returns no data. The really weird thing is, it DOESN'T cauase
> a problem on a local 2.0.3 server, only on remote.
>
> Anybody any clues as to where I should start?
>
> Thanks in advance,
>
> Rob Davis

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links