Sign in to follow this  
Followers 0
dinodod

SQLite performance question

4 posts in this topic

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


Digital Chaos - Life as we know it today.I'm a Think Tank. Problem is, my tank is empty.The Quieter you are, the more you can HearWhich would you choose - Peace without Freedom or Freedom without Peace?Digital Chaos Macgyver ToolkitCompletely Dynamic MenuSQLIte controlsAD FunctionsEXCEL UDFPC / Software Inventory UDFPC / Software Inventory 2GaFrost's Admin Toolkit - My main competitor :)Virtual SystemsVMWAREMicrosoft Virtual PC 2007

Share this post


Link to post
Share on other sites



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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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!


Digital Chaos - Life as we know it today.I'm a Think Tank. Problem is, my tank is empty.The Quieter you are, the more you can HearWhich would you choose - Peace without Freedom or Freedom without Peace?Digital Chaos Macgyver ToolkitCompletely Dynamic MenuSQLIte controlsAD FunctionsEXCEL UDFPC / Software Inventory UDFPC / Software Inventory 2GaFrost's Admin Toolkit - My main competitor :)Virtual SystemsVMWAREMicrosoft Virtual PC 2007

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0