Re: [sqlite] Bulk load strategy

[Joseph L. Casale]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Richard Hipp
> Sent: Wednesday, May 17, 2017 8:54 AM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Bulk load strategy
> 
> Can you send ore details about your data and the updates and indexes
> you are using?

Sure, the database will be used to generate a myriad of custom reports based on
Active Directory data of specific  types. Some of the reports are not simple
in that they involve cross referencing attributes of one object such as 
sIDHistory
with attributes of another such as objectSid.

CREATE TABLE AdObject (
    Id                INTEGER PRIMARY KEY NOT NULL,
    DistinguishedName TEXT    NOT NULL COLLATE NOCASE,
    SamAccountName    TEXT    COLLATE NOCASE
);
CREATE UNIQUE INDEX AdObject_idx_0 ON AdObject (
    DistinguishedName
);
CREATE INDEX AdObject_idx_1 ON AdObject (
    SamAccountName
);

CREATE TABLE AdAttribute (
    Id       INTEGER PRIMARY KEY NOT NULL,
    Type     TEXT    NOT NULL COLLATE NOCASE,
    Value    TEXT    NOT NULL COLLATE NOCASE,
    AdObjectId INTEGER NOT NULL REFERENCES AdObject ON DELETE CASCADE ON UPDATE 
CASCADE
);
CREATE INDEX AdAttribute_idx_0 ON AdAttribute (
    Type
);
CREATE INDEX AdAttribute_idx_1 ON AdAttribute (
    Value
);
CREATE INDEX AdAttribute_idx_2 ON AdAttribute (
    AdObjectId
);

The bulk of the inserts look like:
INSERT INTO AdObject
      (DistinguishedName, SamAccountName)
  VALUES
      (@DistinguishedName, @SamAccountName);

INSERT OR IGNORE INTO AdAttribute
      (Type, Value, AdObjectId)
  VALUES
      (@Type, @Value, @AdObjectId);

(just noticed that IGNORE in the second query which serves no purpose).

Things grind to a halt when I start the following:

INSERT OR IGNORE INTO AdAttribute
      (Type, Value, AdObjectId)
  VALUES
      (@Type, @Value, (SELECT Id FROM AdObject WHERE DistinguishedName = 
@DistinguishedName));

The IGNORE above is required as the input data may ask to modify attributes for 
which no record exists.

Thanks for the help,
jlc
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users