At 01:00 AM 27-09-01 +0000, you wrote:

>Thanks for your time and efforts Helen,
>
>I entered the procedure and executed it - I stopped Interbase after 1
>1/2 hours.
>
>So no great improvement.

So something else is going on in your setup. With the volume of data involved, that solution should have been close to sub-second.

>I have been developing apps using Interbase for over 4 years and have
>about 30 customers using Interbase - I am looking down the barrel of
>dumping Interbase - this is not what I want to choose.

What kind of front end and connectivity are you using/have you been using? Is this the kind of performance you have been used to?

>The basis of my question I guess is why is it so difficult with
>Interbase and so easy for SQL_SERVER?

The normal experience is for the boot to be on the other foot. Queries that take hours or days (or never complete) in SQL Server are scores or hundreds of times faster in IB.

>Hours compared to 2 seconds, for exactly the same query - on exactly
>the same server.

Definitely something wrong then. Give us some details - server setup, client setup, versions, client tools, etc.

>Why do I need to enter a procedure for Interbase, when I don't to get
>a decent response from SQL_SERVER?

A correlated subquery that is slow on IB should be even slower on SQL Server. If it only takes 2 secs on SQL Server, it should be as fast or faster on IB unless there is something fundamentally wrong with your IB setup.

The (functionally equivalent) procedure should be faster on both because it is not creating the huge volume of intermediate sets that your original CSQ is doing.

>I have over 30 customers using Interbase - and my larger customers
>(ie database size > 100mb) the reports die with blown temp files on
>the server.

Then you need to configure the temp files (these can be multiple and of any max size up to the OS limit). Perhaps this is your problem...please provide details.

>SQL_SERVER replies in seconds with the same complex queries entered
>using SQL_Explorer.

Yep, sounds like a configuration problem if you are querying both databases through the BDE - they should be equally crippled.

Are you by any chance trying to use the BDE with an IB 6 Dialect 3 database?

>Do I have to re-write my app to use SQL_SERVER instead?

Your choice. :) But it sounds as if you have a serious IB misconfiguration there. To avoid guessing games, please provide details (paste in a copy of your ibconfig file).

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________