From: | Thor Tall <tall_thor(at)yahoo(dot)com> |
---|---|
To: | Alexandre Leclerc <alexandre(dot)leclerc(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Slow delete/insert. |
Date: | 2006-08-24 08:24:31 |
Message-ID: | 20060824082431.76181.qmail@web50313.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Please see my comments below
--- Alexandre Leclerc <alexandre(dot)leclerc(at)gmail(dot)com>
wrote:
> 2006/8/23, Thor Tall <tall_thor(at)yahoo(dot)com>:
> > The processing is done as follows:
> > 1. Start transaction
> >
> > 2. check if message is in table 1 and if so delete
> > records from table 1(1 row),2(2 rows),3(30
> rows),4(50
> > rows).
> >
> > 3. split the message and insert the date in table
> 1(1
> > row),2(2 rows),3(30 rows),4(50 rows)
> >
> > 4. end transaction
>
> In addition to the other post, the SQL requests can
> be the cause of
> the slow performance. I don't know how you do it,
> but the more you
> think the database way the better your SQL requests
> will be done.
> Also, are the tables auto-deleting the records using
> a real
> Relationnal approach in the DB (references fields
> with defined
> update/delete auto-events) or are they deleted
> manually by you with
> SQL requests?
The tables are deleted using cascaded deletes.
>
> The process can be very much slow of super-fast
> depending of the
> actual DB / Index / Table / Logic implementation and
> the actual
> programmer's SQL / Logic / requests.
>
> Best regards.
>
> --
> Alexandre Leclerc
>
I have the following simple tables which are populate:
CREATE TABLE tb_Flight (
FLIGHT_ID integer UNIQUE NOT NULL,
IFPLID varchar (20) NOT NULL ,
TIMESTAMP timestamp NULL ,
EVENT varchar (50) NULL ,
EVENTCLASS varchar (50) NULL ,
FLTSTATE varchar (50) NULL ,
ARCID varchar (10) NULL ,
ADEP varchar (4) NULL ,
ADES varchar (4) NULL ,
MODELTYP varchar (50) NULL ,
ARCTYP varchar (4) NULL ,
ETO_ADEP timestamp NULL ,
ETO_ADES timestamp NULL ,
ProcessTime_ms integer NULL ,
PRIMARY KEY (FLIGHT_ID)
);
CREATE SEQUENCE seq_Flight_Id;
CREATE TABLE tb_Flight_AD (
FLIGHT_ID integer REFERENCES tb_Flight ON DELETE
CASCADE,
AD_ID integer UNIQUE NOT NULL ,
AD varchar (4) NOT NULL ,
ETO timestamp NOT NULL
);
CREATE SEQUENCE seq_Flight_AD_Id;
CREATE TABLE tb_Flight_ASP (
FLIGHT_ID integer REFERENCES tb_Flight ON DELETE
CASCADE,
ASP_ID integer UNIQUE NOT NULL,
AIRSPDES varchar (20) NOT NULL ,
ETI timestamp NOT NULL ,
XTI timestamp NOT NULL
);
CREATE SEQUENCE seq_Flight_ASP_Id;
CREATE TABLE tb_Flight_RTEPTS (
FLIGHT_ID integer REFERENCES tb_Flight ON DELETE
CASCADE,
RTEPTS integer UNIQUE NOT NULL,
PT varchar (10) NOT NULL ,
ETO timestamp NOT NULL ,
Obsolete integer NOT NULL
);
CREATE SEQUENCE seq_Flight_RTEPTS_Id;
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Alexandre Leclerc | 2006-08-24 12:08:40 | Re: Slow delete/insert. |
Previous Message | Jason Minion | 2006-08-24 05:30:56 | Re: [PERFORM] Query tuning |