--- In ib-support@y..., Helen Borrie <helebor@d...> wrote:

> 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?

>

A Delphi 3 then 4 then 5 app using the BDE.

>
> >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.

I and my customers have a range of servers all running NT4 or Windows
2000 Adv Server (some smaller customers use Windows 98 as servers -
but they have correspondingly smaller db's and are not having
problems).

My dev server is Pentium II 350 with 128 MB RAM on W2k Adv Server

Clients range from Petium II to my dev machine Athelon 1.2 MHz with
512 Mb RAM, etc

The BDE is version 5.01 the application is written in delphi 5.

>
>
> >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.

>

How do I do this?

> >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?

No the IB6 servers have just used a restored IB5.6 backup - so
should be dialect 1 ... correct?

>
>
> >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).

All default values -

IBConfig file from IB6.0.1.6 (opened using notepad)

V4_LOCK_MEM_SIZE 98304
V4_LOCK_SEM_COUNT 32
V4_LOCK_SIGNAL 16
V4_EVENT_MEM_SIZE 32768
DATABASE_CACHE_PAGES 75
SERVER_PRIORITY_CLASS 1
SERVER_CLIENT_MAPPING 4096
SERVER_WORKING_SIZE_MIN 0
SERVER_WORKING_SIZE_MAX 0
V4_LOCK_GRANT_ORDER 1
ANY_LOCK_MEM_SIZE 98304
ANY_LOCK_SEM_COUNT 32
ANY_LOCK_SIGNAL 16
ANY_EVENT_MEM_SIZE 32768

Thanks again Helen,

If this can fixed I would be delighted.

John

PS How do you usually override these values?

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