Re: [GENERAL] Major Performance issue

[Tom Lane]

John Hughes <[EMAIL PROTECTED]> writes:
> The problem is that inserting into temp_holding takes more than a SECOND PER 
> ROW!

Offhand I'd bet on the repeated computations of max(core_leads.id) as
being the bulk of the problem.  That's completely broken anyway since
if concurrent insertions are happening there's no guarantee you'll
get the same result each time.  Consider using a sequence to
generate these IDs, and doing just one nextval() not several per
trigger call.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Major Performance issue

[John Hughes]

Hi, I've been working the past couple of days to import a bunch of csv data 
into my system.

I have a temp_holding table which basically just matches the csv format.

I have a trigger before insert on each row on the temp_holding table, below.

The problem is that inserting into temp_holding takes more than a SECOND PER 
ROW!

What can I do to speed this up, because importing 106 records is going to take 
a day and a half???

Kind regards,

John Hughes

CREATE FUNCTION data_import() RETURNS "trigger"
AS '
DECLARE
  User RECORD;
  check RECORD;
BEGIN
  SELECT INTO check * FROM core_contacts WHERE primaryphone = NEW.number; -- 
Checks for duplicate entries
  IF NOT FOUND THEN
SELECT INTO User id FROM core_users WHERE username = NEW.username;

IF User.id IS NULL THEN
  User.id := 37;
END IF;

INSERT INTO core_contacts (
  primaryphone,
  zip,
  regionid,
  city,
  address,
  firstname,
  lastname)
VALUES (
  NEW.number,
  NEW.zip,
  (SELECT id FROM core_regions WHERE name = NEW.state),
  NEW.city,
  NEW.street,
  NEW.first_name,
  NEW.last_name);

INSERT INTO core_leads (contactid, leadstatustypeid, createdbyuserid, 
leadtypeid, notes)
  VALUES ( max(core_contacts.id),
   1,
   User.id,
   1,
   NEW.agent_comments );
IF NEW.lead_date =  THEN
  NEW.lead_date := ''now'';
END IF;
INSERT INTO core_leadevents ( leadid, leadeventtypeid, userid, created, 
notes )
  VALUES ( max(core_leads.id),
   4,
   User.id,
   date(NEW.lead_date),
   ''Imported on ''+''now'' );

INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.loan_amount, 18, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.property_value, 1, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.interest_rate, 14, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.interest_type, 13, max(core_leads.id));
  ELSE
NEW.duplicate = true;
  END IF;
  RETURN NEW;
END;
'
LANGUAGE plpgsql;

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly