--- In firebird-support@yahoogroups.com, Paul Vinkenoog <paul@v...> wrote:

> Hello Didier,
>
> > Table Project :
> > project_id integer (PK),
> > template_id, (defines the type of project & the template to apply)
> > more data ...
> >
> > Table project_data
> >
> > PROJECT_ID integer not null,
> > Column_id integer not null,
> > int_val integer,
> > num_val decimal (12,6),
> > date_val date,
> > char_val varchar(255)
> >
> > project_id and column_id together are making the PK
> >
> > I will soon have over 1 million records in this table.
> >
> > each project's data is held in multiple records, according to a
> > template (linked using template_id) which defines what records can be
> > created and which what type of data (int, num, date, char). A template
> > defines between 20 and 80 records per type of project.
> >
> > Not all the data is known when the project is created, not all
> > projects will need all the data defined in a template.
> >
> > During the project's life, some data will be available and some will
> > be updated from different sources. (Delphi front end, a PHP front end,
> > some through a cron'ed script via ISQL).
> >
> > my question: what would be the most efficient thing to do if I want to
> > either create a record if it does not exist OR update the record if it
> > exists:
> >
> > A: try to insert first, see if that fails & then update the existing
> > record
> > or
> > B: try to update & if that fails, then attempt to create the record?
>
> I would also consider:
>
> C: write an SP InsertOrUpdate taking all the necessary parameters. Have
> the SP do an EXISTS test and act upon that. This way, you keep the
> testing on the server side, the data go only over the wire once.
>
> Which one is quickest depends on lots of factors, though.
>

Using a SP was what I meant, using an 'exists' is probably faster and
the more linear as far as speed is concerned: the server does not have
to undo what it has just done (ie after a failed insert I should
imagine that some clean up has to take place, same after a failed update)

Thanks
Didier

> Greetings,
> Paul Vinkenoog