Markus Kemper wrote:

> From: "Markus Kemper" <mkemper@...>
>
> The next time this happens please take a look at the database
> header page. (eg. gstat -h <db_name>) This could be a data
> issue, db design issue, an indexing issue or perhaps a problem
> with InterBase. I don't see the ability of killing the query
> as a real solution to your problem because you still want the
> query to execute quickly. The ability to kill it in this
> scenario would be more of an 'emergency' tactic which may be
> a good reason to have the functionality.
>

I agree about the 'emergency' tactic but it is usefull.
I thing its an order by problem.
I tested as a beta tester with IB 6.0 on NT and performs better
I will try the gstat -h

>
> Is the printout optimized into a stored procedure or view?

It is an SP who calls other SPs. any attempt to do it with views was extremelly
slow.
In fact I use SP approach because IB performs much much buetter (over 1000 times)
if you are able to "break:" a select like:

SELECT T1.F1,T2.F2,T3.F3,T4.F4
FROM T1
JOIN T2 ON .... AND ....
JOIN T3 ON .... AND ....
JOIN T4 ON ... AND ...
WHERE ....

in someething like

FOR SELECT T1.F1,T1.F2..
FROM T1
WHERE ...
INTO :F1,:F2..
DO BEGIN
SELECT T2.F1,T2.F1
FROM T2
WHERE T2.F? = :F1
INT :F?;
.
.
.
SUSPEND;
END

> I try to recommend that when persons are 'developing' reports
> that they do so on a 'development' server not the live system.
> Once satisfied with performance and function, then more the
> report into production.

> I definatelly aggree with that, but the DB administrator is a person hired by my
> client to maintain the hardware

and also to help with the software. Its far easier to educate a person fully to
your programs than to educate 24 persons.
On the other han neigther I can enforce my client to have a 'development' server
nor the DBA to test his queries when others are not working. In fact at the
begining we were doing that in his computer with local IB, and we kill the local
IB when was sach a case. But with Clasic IB - Linux all you have to do is kill the
proccess and clear the /tmp directory

> Are the queries optimized with views or stored procedures.
> Where they executed with a local connection path.
>
> (eg. connect /db/mydb.gdb vs. connect myserver:/db/mydb.gdb)
>

both with the same results

> Is this the case when the client (gds_inet_server) is waiting
> on a resonse from a request or is it just sitting idle maintaining
> a connection?

is waiting on a responce. I had never had problems when it is just maintaining a
connection (I have red such problems in
mers or / and forums but I had never have them)
In fact I love this behaviour. I woul like just to be able to tune the idle time.

Any way most of the problems I have had until now (past two years) are mostly with
the SQL optimizer (I would deeply appreciate something like Sylvanian Fausts's SQL
optimizer for Oracle / I tried to write one but every time I try to use a plan
other than nutural the server responce index XXX can not be used with the
specified plan or even crashes. This hapens even when I try to use the plan he
(the server) generated. The test I did with IB 6.0 shows that it is better for eg.

it generates the corect plan for queries I had to put tricky ANDs in the JOIN or
WHERE clauses in 5.6)
and the agregate function and ORDER BY or GROUP BY performance.

I thing thus, that these are the points that the OS plan must be focused in the
begining.

As far as for Clasic vs SS I prefer to see how is Linux with the threads.

Last but not least do you have any idea why

1) I have AD (After Delete) trigers that update / delete records from other tables
when a record in the main table is deleted. This culd be cascaded three or four
levels. I tried to delete 100 records from the main table and after two hours I
killed the proccess. Is it a garbage collection issue ? (I red some Ann's comments
on this)

2) In some situations a view is not correctly executed when is called from an SP.
I mean it returns no or faulty results. The same view works fine when executed
from a client (local or remote) This is tested with IB 5,5.1,5.5,5.6. It works
correctly on 6.0 but I don't know if there are other cases.

>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com