SQLite performance question

[dinodod]

Question for all you SQL admins / DBA's

I'm making a script that is taking inventory of a PC and then injecting the data into a SQLite db. However, I seem to be making several calls to the DB which concerns me for performance issues and I was wondering if anyone could suggest a better method. I'm leaning towards a ARRAY system but right now, I literally have nearly 900,000 entries in my spreadsheet.

Here is the breakdown -->

1) Scan the PCs and log the data into 1 spreadsheet. The spreadsheet has only 2 columns, PC_Name & Software_title.

2) Once the PCs are done being scanned, I then inject the software titles into the DB. The software title is unique so no duplicates are entered.

3) Once that is done, I have to re-scan the spreadsheet for the PC names and software titles then get the ID of each title so I can update another table that has the PC names already populated in it with a field called ID_Software which I will use to hold the ID's of the titles in the format 1,2,3,4. later I was thinking of reading the entire line and performing a stringsplit to separate each ID and then run a match against my Software table

I'm not sure on the limits of an Array and I'm wondering if I should load up all the ID's & titles of the software into a 2-D array instead of hitting the DB so much? I might have a few thousand entries in the software (Need to verify). I like the thought of using a memory array -vs- querying the DB but is there a better solution to this?

Thanks!

###############################

Here is a schematic of my DB

###############################

DB

|

|

-- Table: PCINFO

|

|

-- Field: ID

-- Field: PCName

-- Field: ID_Software

-- Table: Software

|

|

-- Field: ID

-- Field: Software_Title

[jchd]

Your design is not SQL-ish as it stands.

You have a one to many relationship between PCs and Softwares. In conventional languages, you'd indeed make a table (or linked list or whatever structure) to hold the list of softwares for a given PC.

SQL is a set-theoretic thingy and this isn't the proper way to denote such a relationship. It would BTW be utterly unefficient.

Your PC table:

CREATE TABLE "PCs" (
  "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  "Name" CHAR NOT NULL COLLATE NOCASE);
CREATE UNIQUE INDEX "ixPCname" ON "PCs" ("Name" COLLATE NOCASE);

Your Software table:

CREATE TABLE "Software" (
  "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  "Title" CHAR NOT NULL COLLATE NOCASE);
CREATE UNIQUE INDEX "ixSoftName" ON "Software" ("Title" COLLATE NOCASE);

Your relationship table (it uses foreign keys to PCs and Software tables):

CREATE TABLE "PcSw" (
  "PCid" INTEGER NOT NULL CONSTRAINT "fkPC" REFERENCES "PCs"("Id") ON DELETE CASCADE ON UPDATE CASCADE MATCH FULL NOT DEFERRABLE INITIALLY DEFERRED, 
  "SWid" INTEGER NOT NULL CONSTRAINT "fkSW" REFERENCES "Software"("Id") ON DELETE CASCADE ON UPDATE CASCADE MATCH FULL NOT DEFERRABLE INITIALLY DEFERRED, 
  CONSTRAINT "" PRIMARY KEY ("PCid", "SWid"));

I've created case-insensitive indices on PC names and software titles as you'll need them for efficient lookup.

You also have an index on SWid to speed up answers to the question: "on which PCs is software XYZ installed?"

Size eaten up by those indices is unimportant in a very small DB like this one.

Now, you need to scan your input only once using this pseudo-code:

SQL: begin;
while not EOF
read input
split $PCname and $SWtitle
SQL: insert or ignore into PCs (name) values (<$PCname>);
SQL: insert or ignore into Software (title) values (<$SWtitle>);
SQL: insert or ignore into PCSW (pcid, swid) values ((select id from PCs where name = <$PCname>), (select id from software where title = <$SWtitle>));
wend
SQL: commit;

The "or ignore" clauses make it safe to insert without querying first.

Remember to use _SQLite_Escape to properly enclose string variables (that's what <$PCname> means for instance).

Now you can query PCs on names, partial or full, software title, list PCs with XYZ installed, about any query you can imagine in SQL.

You know where to chime if you need more help.

[Zedna]

Here is my older SQLite simple example demonstrating some optimization's techniques:

Edited June 14, 2011 by Zedna

[dinodod]

Wow, thank you very much both of you!

I only have a basic understanding of SQL from like 10 years ago when I was a web designer. This is an awesome solution!