Board index » delphi » IBConsole Freezes

IBConsole Freezes

Hi, I'm running the following query:

update client c
set field_1 =
(select sum(transactionjournal.credit)
from CLIENT
INNER JOIN TRANSACTIONJOURNAL ON (CLIENT.CLIENTID =
TRANSACTIONJOURNAL.CLIENTID)
where client.clientid = c.clientid)

when I run it the hard drive thrashes around for a while and then stops.
When it stops IBConsole has completely frozen and the only way to get out is
to kill it off from the task manager.  I have looked in the server log and
have noticed one line which may be of interest:

internal gds software consistency check (Too many savepoints (287))

Can anyone help??

Cheers

Paul

 

Re:IBConsole Freezes


Quote
Paul Rowling wrote:
> Hi, I'm running the following query:

> update client c
> set field_1 =
> (select sum(transactionjournal.credit)
> from CLIENT
> INNER JOIN TRANSACTIONJOURNAL ON (CLIENT.CLIENTID =
> TRANSACTIONJOURNAL.CLIENTID)
> where client.clientid = c.clientid)

> when I run it the hard drive thrashes around for a while and then stops.
> When it stops IBConsole has completely frozen and the only way to get out is
> to kill it off from the task manager.  I have looked in the server log and
> have noticed one line which may be of interest:

> internal gds software consistency check (Too many savepoints (287))

> Can anyone help??

What version of IBCOnsole and Interbase are you running, Open SS for Linux?

Re:IBConsole Freezes


IB Console is version 1.0.1.343 and Interbase version is WI-06.0.2.0
This is all running on a windows 2000 platform.

Cheers

Paul

Quote
"Krzysztof Golko" <krzysztofgo...@yahoo.com> wrote in message

news:3e32f333@newsgroups.borland.com...
Quote
> Paul Rowling wrote:
> > Hi, I'm running the following query:

> > update client c
> > set field_1 =
> > (select sum(transactionjournal.credit)
> > from CLIENT
> > INNER JOIN TRANSACTIONJOURNAL ON (CLIENT.CLIENTID =
> > TRANSACTIONJOURNAL.CLIENTID)
> > where client.clientid = c.clientid)

> > when I run it the hard drive thrashes around for a while and then stops.
> > When it stops IBConsole has completely frozen and the only way to get
out is
> > to kill it off from the task manager.  I have looked in the server log
and
> > have noticed one line which may be of interest:

> > internal gds software consistency check (Too many savepoints (287))

> > Can anyone help??

> What version of IBCOnsole and Interbase are you running, Open SS for
Linux?

Re:IBConsole Freezes


Just realised that IBConsole isn't freezing, it is just hogging 100% of the
cpu.  The query does eventually work, but it takes over 20 minutes to
complete.  There are approx 12000 records in the client table and approx
6000 in the transactions table.  Why does this take so long to complete??
Quote
"Paul Rowling" <paul_rowl...@toro-crm.co.uk> wrote in message

news:3e33e93a@newsgroups.borland.com...
Quote
> IB Console is version 1.0.1.343 and Interbase version is WI-06.0.2.0
> This is all running on a windows 2000 platform.

> Cheers

> Paul
> "Krzysztof Golko" <krzysztofgo...@yahoo.com> wrote in message
> news:3e32f333@newsgroups.borland.com...
> > Paul Rowling wrote:
> > > Hi, I'm running the following query:

> > > update client c
> > > set field_1 =
> > > (select sum(transactionjournal.credit)
> > > from CLIENT
> > > INNER JOIN TRANSACTIONJOURNAL ON (CLIENT.CLIENTID =
> > > TRANSACTIONJOURNAL.CLIENTID)
> > > where client.clientid = c.clientid)

> > > when I run it the hard drive thrashes around for a while and then
stops.
> > > When it stops IBConsole has completely frozen and the only way to get
> out is
> > > to kill it off from the task manager.  I have looked in the server log
> and
> > > have noticed one line which may be of interest:

> > > internal gds software consistency check (Too many savepoints (287))

> > > Can anyone help??

> > What version of IBCOnsole and Interbase are you running, Open SS for
> Linux?

Re:IBConsole Freezes


Quote
Paul Rowling wrote:
> Hi, I'm running the following query:

> update client c
> set field_1 =
> (select sum(transactionjournal.credit)
> from CLIENT
> INNER JOIN TRANSACTIONJOURNAL ON (CLIENT.CLIENTID =
> TRANSACTIONJOURNAL.CLIENTID)
> where client.clientid = c.clientid)

> when I run it the hard drive thrashes around for a while and then
> stops. When it stops IBConsole has completely frozen and the only way
> to get out is to kill it off from the task manager.  I have looked in
> the server log and have noticed one line which may be of interest:

> internal gds software consistency check (Too many savepoints (287))

> Can anyone help??

> Cheers

> Paul

give aliases on all client tables!

update client c
set field_1 =
(select sum(transactionjournal.credit)
from CLIENT c2
INNER JOIN TRANSACTIONJOURNAL ON (c2.CLIENTID =
TRANSACTIONJOURNAL.CLIENTID)
where c2.clientid = c.clientid)

Re:IBConsole Freezes


Hi Paul,

Caught up with this thread from the last one.  So it works, just V slowly
:-).

Can you send the plan that the query produces (with the table aliases and
every field using a table alias).

Also the DBstats including the stats for the tables (from you meta D I can't
see how broad the rows are).

When you say returns instantly, how instantly (in ms)?

We'll get there,

JAC.

Quote
"Paul Rowling" <paul.rowl...@toro-crm.co.uk> wrote in message

news:3e350406$1@newsgroups.borland.com...
Quote
> Hi Bojidar,

> Here are the DDL statements, please note that the transactionjournal table
> is now called transactions and the client id in both tables is now called
> toro_clientid:

> /* Table: CLIENT */

> CREATE TABLE CLIENT (
>     TORO_CLIENTID INTEGER NOT NULL,
>     CLIENTID INTEGER,
>     TITLE DTITLE,
>     FIRST_NAME DNAME,
>     MIDDLE_NAME DNAME,
>     LAST_NAME DNAME,
>     DOB DDATE,
>     ADDRESS1 DADDRESSLINE,
>     ADDRESS2 DADDRESSLINE,
>     ADDRESS3 DADDRESSLINE,
>     ADDRESS4 DADDRESSLINE,
>     ADDRESS5 DADDRESSLINE,
>     ADDRESS6 DADDRESSLINE,
>     POSTCODE DPOSTCODE,
>     HOME_TELEPHONE DTELEPHONE,
>     WORK_TELEPHONE DTELEPHONE,
>     MOBILE_TELEPHONE DTELEPHONE,
>     EMAIL_ADDRESS DEMAIL,
>     GENDER DGENDER,
>     DOCTOR_NAME DDOCTORNAME,
>     DOCTOR_TELEPHONE DTELEPHONE,
>     SESSIONDISCOUNT DPERCENTAGE,
>     COURSEDISCOUNT DPERCENTAGE,
>     PRODUCTDISCOUNT DPERCENTAGE,
>     CREDIT_LIMIT DCURRENCY,
>     DO_NOT_MAIL DBOOLEAN,
>     DATE_ENROLLED DDATE,
>     BRANCH_ID INTEGER,
>     SKIN_TYPE INTEGER,
>     SOURCE_ID INTEGER,
>     MEMBERSHIP_ID VARCHAR (25) character set WIN1251 collate WIN1251,
>     FIELD_1 NUMERIC (15, 2),
>     NEW_PROPERTY CHAR (50),
>     NEW_STREET CHAR (50),
>     NEW_LOCALITY CHAR (50),
>     NEW_TOWN CHAR (50),
>     NEW_COUNTY CHAR (50),
>     NEW_POSTCODE CHAR (10),
>     PC_CODE CHAR (50),
>     DUP_CODE CHAR (10) character set WIN1251 collate WIN1251);

> /* Primary keys definition */

> ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (TORO_CLIENTID);

> /* Foreign keys definition */

> ALTER TABLE CLIENT ADD  CONSTRAINT FK_BRANCH_ID FOREIGN KEY (BRANCH_ID)
> REFERENCES BRANCH (BRANCH_ID);

> /* Table: TRANSACTIONS */

> CREATE TABLE TRANSACTIONS (
>     TORO_TRANSACTIONID ID NOT NULL,
>     TRANSACTION_ID INTEGER NOT NULL,
>     BRANCH_ID INTEGER,
>     TRANSACTION_DATE DDATE,
>     TRANSACTION_TYPE_ID DCODE,
>     CLIENTID INTEGER,
>     TORO_CLIENTID INTEGER,
>     STUSERID INTEGER,
>     SUMMARY DDESCRIPTION,
>     CREDIT DCURRENCY,
>     DEBIT DCURRENCY,
>     FIELD_1 INTEGER);

> /* Primary keys definition */

> ALTER TABLE TRANSACTIONS ADD CONSTRAINT PK_TRANSACTIONS PRIMARY KEY
> (TORO_TRANSACTIONID);

> /* Foreign keys definition */

> ALTER TABLE TRANSACTIONS ADD  CONSTRAINT FK2_TORO_CLIENTID FOREIGN KEY
> (TORO_CLIENTID) REFERENCES CLIENT (TORO_CLIENTID) ON DELETE CASCADE;
> ALTER TABLE TRANSACTIONS ADD  CONSTRAINT FK_TRANSACTION_TYPE_ID FOREIGN
KEY
> (TRANSACTION_TYPE_ID) REFERENCES TRANSACTION_TYPE (TRAN_TYPE_ID);

> Indexes are as follows:

> Client Table

> RDB$FOREIGN11                   BRANCH_ID
> Active                           Ascending
> RDB$PRIMARY1                     TORO_CLIENTID                    Unique
> Active                           Ascending

> Transactions Table

> RDB$FOREIGN18                    TORO_CLIENTID
> Active                           Ascending
> RDB$FOREIGN20                    TRANSACTION_TYPE_ID
> Active                           Ascending
> RDB$PRIMARY22                    TORO_TRANSACTIONID               Unique
> Active                           Ascending

> If I run the nested select seperately, the results are displayed almost
> instantaneously!!!!

> I appreciate your time Bojidar.

> Thanks

> Paul

> "Bojidar Alexandrov" <b...@kodar.net> wrote in message
> news:3e34ff10@newsgroups.borland.com...
> > Paul Rowling wrote:
> > > Hi Bojidar,

> > > I tried aliasing them all but it still runs the same.
> > > "Bojidar Alexandrov" <b...@kodar.net> wrote in message
> > > news:3e34fa21@newsgroups.borland.com...
> > >> Paul Rowling wrote:
> > >>> Hi, I'm running the following query:

> > >>> update client c
> > >>> set field_1 =
> > >>> (select sum(transactionjournal.credit)
> > >>> from CLIENT
> > >>> INNER JOIN TRANSACTIONJOURNAL ON (CLIENT.CLIENTID =
> > >>> TRANSACTIONJOURNAL.CLIENTID)
> > >>> where client.clientid = c.clientid)

> > >>> when I run it the hard drive thrashes around for a while and then
> > >>> stops. When it stops IBConsole has completely frozen and the only
> > >>> way to get out is to kill it off from the task manager.  I have
> > >>> looked in the server log and have noticed one line which may be of
> > >>> interest:

> > >>> internal gds software consistency check (Too many savepoints (287))

> > >>> Can anyone help??

> > >>> Cheers

> > >>> Paul

> > >> give aliases on all client tables!

> > >> update client c
> > >> set field_1 =
> > >> (select sum(transactionjournal.credit)
> > >> from CLIENT c2
> > >> INNER JOIN TRANSACTIONJOURNAL ON (c2.CLIENTID =
> > >> TRANSACTIONJOURNAL.CLIENTID)
> > >> where c2.clientid = c.clientid)

> > What are DDL statements for these tables. Give them please, indexes and
> plan
> > you receive.
> > Try to run nested select separatelly to view the results.

> > I suspect that there something goes in undefinite loop. Triggers?

> > --
> > Bojidar Alexanrov
> > Kodar Ltd.
> > http://www.Kodar.net

Re:IBConsole Freezes


Hi Jason,

Thanks very much for your help!!  I've got a bit of a rush job on at the
moment so it may be a couple of days before I can get the information you
require - sorry for the delay.  I'll keep you posted though.

Best Regards

Paul
"Jason Chapman (JAC2)" <ja...@jac2.co.uk> wrote in message
news:3e357a2f@newsgroups.borland.com...

Quote
> Hi Paul,

> Caught up with this thread from the last one.  So it works, just V slowly
> :-).

> Can you send the plan that the query produces (with the table aliases and
> every field using a table alias).

> Also the DBstats including the stats for the tables (from you meta D I
can't
> see how broad the rows are).

> When you say returns instantly, how instantly (in ms)?

> We'll get there,

> JAC.

> "Paul Rowling" <paul.rowl...@toro-crm.co.uk> wrote in message
> news:3e350406$1@newsgroups.borland.com...
> > Hi Bojidar,

> > Here are the DDL statements, please note that the transactionjournal
table
> > is now called transactions and the client id in both tables is now
called
> > toro_clientid:

> > /* Table: CLIENT */

> > CREATE TABLE CLIENT (
> >     TORO_CLIENTID INTEGER NOT NULL,
> >     CLIENTID INTEGER,
> >     TITLE DTITLE,
> >     FIRST_NAME DNAME,
> >     MIDDLE_NAME DNAME,
> >     LAST_NAME DNAME,
> >     DOB DDATE,
> >     ADDRESS1 DADDRESSLINE,
> >     ADDRESS2 DADDRESSLINE,
> >     ADDRESS3 DADDRESSLINE,
> >     ADDRESS4 DADDRESSLINE,
> >     ADDRESS5 DADDRESSLINE,
> >     ADDRESS6 DADDRESSLINE,
> >     POSTCODE DPOSTCODE,
> >     HOME_TELEPHONE DTELEPHONE,
> >     WORK_TELEPHONE DTELEPHONE,
> >     MOBILE_TELEPHONE DTELEPHONE,
> >     EMAIL_ADDRESS DEMAIL,
> >     GENDER DGENDER,
> >     DOCTOR_NAME DDOCTORNAME,
> >     DOCTOR_TELEPHONE DTELEPHONE,
> >     SESSIONDISCOUNT DPERCENTAGE,
> >     COURSEDISCOUNT DPERCENTAGE,
> >     PRODUCTDISCOUNT DPERCENTAGE,
> >     CREDIT_LIMIT DCURRENCY,
> >     DO_NOT_MAIL DBOOLEAN,
> >     DATE_ENROLLED DDATE,
> >     BRANCH_ID INTEGER,
> >     SKIN_TYPE INTEGER,
> >     SOURCE_ID INTEGER,
> >     MEMBERSHIP_ID VARCHAR (25) character set WIN1251 collate WIN1251,
> >     FIELD_1 NUMERIC (15, 2),
> >     NEW_PROPERTY CHAR (50),
> >     NEW_STREET CHAR (50),
> >     NEW_LOCALITY CHAR (50),
> >     NEW_TOWN CHAR (50),
> >     NEW_COUNTY CHAR (50),
> >     NEW_POSTCODE CHAR (10),
> >     PC_CODE CHAR (50),
> >     DUP_CODE CHAR (10) character set WIN1251 collate WIN1251);

> > /* Primary keys definition */

> > ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (TORO_CLIENTID);

> > /* Foreign keys definition */

> > ALTER TABLE CLIENT ADD  CONSTRAINT FK_BRANCH_ID FOREIGN KEY (BRANCH_ID)
> > REFERENCES BRANCH (BRANCH_ID);

> > /* Table: TRANSACTIONS */

> > CREATE TABLE TRANSACTIONS (
> >     TORO_TRANSACTIONID ID NOT NULL,
> >     TRANSACTION_ID INTEGER NOT NULL,
> >     BRANCH_ID INTEGER,
> >     TRANSACTION_DATE DDATE,
> >     TRANSACTION_TYPE_ID DCODE,
> >     CLIENTID INTEGER,
> >     TORO_CLIENTID INTEGER,
> >     STUSERID INTEGER,
> >     SUMMARY DDESCRIPTION,
> >     CREDIT DCURRENCY,
> >     DEBIT DCURRENCY,
> >     FIELD_1 INTEGER);

> > /* Primary keys definition */

> > ALTER TABLE TRANSACTIONS ADD CONSTRAINT PK_TRANSACTIONS PRIMARY KEY
> > (TORO_TRANSACTIONID);

> > /* Foreign keys definition */

> > ALTER TABLE TRANSACTIONS ADD  CONSTRAINT FK2_TORO_CLIENTID FOREIGN KEY
> > (TORO_CLIENTID) REFERENCES CLIENT (TORO_CLIENTID) ON DELETE CASCADE;
> > ALTER TABLE TRANSACTIONS ADD  CONSTRAINT FK_TRANSACTION_TYPE_ID FOREIGN
> KEY
> > (TRANSACTION_TYPE_ID) REFERENCES TRANSACTION_TYPE (TRAN_TYPE_ID);

> > Indexes are as follows:

> > Client Table

> > RDB$FOREIGN11                   BRANCH_ID
> > Active                           Ascending
> > RDB$PRIMARY1                     TORO_CLIENTID                    Unique
> > Active                           Ascending

> > Transactions Table

> > RDB$FOREIGN18                    TORO_CLIENTID
> > Active                           Ascending
> > RDB$FOREIGN20                    TRANSACTION_TYPE_ID
> > Active                           Ascending
> > RDB$PRIMARY22                    TORO_TRANSACTIONID               Unique
> > Active                           Ascending

> > If I run the nested select seperately, the results are displayed almost
> > instantaneously!!!!

> > I appreciate your time Bojidar.

> > Thanks

> > Paul

> > "Bojidar Alexandrov" <b...@kodar.net> wrote in message
> > news:3e34ff10@newsgroups.borland.com...
> > > Paul Rowling wrote:
> > > > Hi Bojidar,

> > > > I tried aliasing them all but it still runs the same.
> > > > "Bojidar Alexandrov" <b...@kodar.net> wrote in message
> > > > news:3e34fa21@newsgroups.borland.com...
> > > >> Paul Rowling wrote:
> > > >>> Hi, I'm running the following query:

> > > >>> update client c
> > > >>> set field_1 =
> > > >>> (select sum(transactionjournal.credit)
> > > >>> from CLIENT
> > > >>> INNER JOIN TRANSACTIONJOURNAL ON (CLIENT.CLIENTID =
> > > >>> TRANSACTIONJOURNAL.CLIENTID)
> > > >>> where client.clientid = c.clientid)

> > > >>> when I run it the hard drive thrashes around for a while and then
> > > >>> stops. When it stops IBConsole has completely frozen and the only
> > > >>> way to get out is to kill it off from the task manager.  I have
> > > >>> looked in the server log and have noticed one line which may be of
> > > >>> interest:

> > > >>> internal gds software consistency check (Too many savepoints
(287))

> > > >>> Can anyone help??

> > > >>> Cheers

> > > >>> Paul

> > > >> give aliases on all client tables!

> > > >> update client c
> > > >> set field_1 =
> > > >> (select sum(transactionjournal.credit)
> > > >> from CLIENT c2
> > > >> INNER JOIN TRANSACTIONJOURNAL ON (c2.CLIENTID =
> > > >> TRANSACTIONJOURNAL.CLIENTID)
> > > >> where c2.clientid = c.clientid)

> > > What are DDL statements for these tables. Give them please, indexes
and
> > plan
> > > you receive.
> > > Try to run nested select separatelly to view the results.

> > > I suspect that there something goes in undefinite loop. Triggers?

> > > --
> > > Bojidar Alexanrov
> > > Kodar Ltd.
> > > http://www.Kodar.net

Other Threads