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.

Greetings,
Paul Vinkenoog