"BareFeetWare", on Tuesday, May 24, 2011 8:31 AM wrote...
On 23/05/2011, at 11:13 PM, jose isaias cabrera wrote:
SharedDB file is about 600 megs shared over a network drive and it's
getting
slow, but if I get the specific record ID only with the select that I
want,
it's a lot faster than getting the select with all the items in one shot.
SQLite probably does something in the back ground to get things faster
when
addressed specifically to an ID.
600MB and slow seems like a good candidate for better structuring your
database. If you post your full schema, we can suggest a better way to
structure it for optimal speed and efficiency.
Tom
BareFeetWare
I know that I have to re-structure the whole thing, and I know I have to do
it soon, but, this is now live data and I have over 100K lines of code,
which about 10K has to do with sqlite, and I am scared to break anything.
When I started this, I was very new to the SQL environment and so I thought
of SQL as a huge data as a worksheet, and that is how I created the
databases. Now I know better, so I have to break the various tables to
normalize them, but it is a lot of data. Some of our fellow SQLiters (P
Kishor, yourself, Kees Nuyt, Igor, plus others) have been very good to me.
I need to read and learn how to normalize, plus more. But, anyway, for the
fun of it I have attached the current schema for your laughing pleasure. :-)
By the way, if you feel like suggesting anything, go ahead and make my day
some more. .-)
thanks for all the help.
josé
11:22:20.86>sqlite3 "K:\Data\OpenJobsTool\AllOpenProjs.db"
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE "Alignment" (
keyName primary key, data
);
CREATE TABLE Contact (
myKey primary key
);
CREATE TABLE "Create-SRC" (
keyName primary key, data
);
CREATE TABLE CustDivision (
keyName primary key, data
);
CREATE TABLE CustomerInfo (Program PRIMARY KEY, Contact, Terminologist,
TechPM,PM, PMBkup, BizType, BizNature, UKPM, Margin, UKXchange, CanXchange,
Xtra0, Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9);
CREATE TABLE "DOC-Trans" (
keyName primary key, data
);
CREATE TABLE "DOC-Valid" (
keyName primary key, data
);
CREATE TABLE "DTP" (
keyName primary key, data
);
CREATE TABLE DTPSoftware (
keyName primary key, data
);
CREATE TABLE "Delivery" (
keyName primary key, data
);
CREATE TABLE "File-Proc" (
keyName primary key, data
);
CREATE TABLE "GraphEditing" (
keyName primary key, data
);
CREATE TABLE "In-Context-Proof" (
keyName primary key, data
);
CREATE TABLE "Interpretation" (
keyName primary key, data
);
CREATE TABLE JobTranslationWordCount (JobID integer primary key, SubProjID integer, ProjID integer, " Context TM ", " Repetitions ", " 100%
", " 95% - 99% ", "85% - 94% ", " 75% - 84% ", " 50% - 74% ", " No Match ", " Total ");
CREATE TABLE LSOpenJobs
(
id integer primary key, ProjID integer, subProjID integer, parent,
children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk,
lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir,
Delivery
Dir, paid, paidDate, notes, status, pages, ta, fromLang, techPM, termPM,
Xtra0,Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9, XtraA,
XtraB, XtraC, XtraD, XtraE, XtraF
);
CREATE TABLE LSOpenProjects
(
id integer primary key, ProjID integer, subProjID integer, parent,
children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk,
lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir,
Delivery
Dir, paid, paidDate, notes, status, pages, ta, fromLang, techPM, termPM,
Xtra0,Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9, XtraA,
XtraB, XtraC, XtraD, XtraE, XtraF
);
CREATE TABLE LSOpenSubProjects
(
id integer primary key, ProjID integer, subProjID integer, parent,
children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk,
lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir,
Delivery
Dir, paid, paidDate, notes, status, pages, ta, fromLang, techPM, termPM,
Xtra0,Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9, XtraA,
XtraB, XtraC, XtraD, XtraE, XtraF
);
CREATE TABLE LogAnalysis (
id integer primary key,
subProjID integer,
ProjID integer,
filename,
" Context TM ",
" Repetitions ",
" 100% ",
" 95% - 99% ",
" 85% - 94% ",
" 75% - 84% ",
" 50% - 74% ",
" No Match ",
Xtra0,
Xtra1,
Xtra2,
Xtra3,
Xtra4
);
CREATE TABLE "PM" (
keyName primary key, data
);
CREATE TABLE PMData
(
login primary key,
email,
FirstName,
LastName,
Address,
City,
State,
Zip,
MailStop,
WorkPhone,
Intelnet,
HomePhone,
Password,
Lang,
ProjOwned,
PCWorkPath,
ServerWorkPath,
ComputerName,
UserProfilePath,
ext0,
ext1,
ext2,
ext3,
ext4,
ext5,
ext6,
ext7,
ext8,
ext9,
extA,
extB,
extC,
extD,
extE,
extF
);
CREATE TABLE PMTime (id integer primary key, rec integer, date, secs integer);
CREATE TABLE PMUserData
(
login primary key,
Name,
Password,
email,
phone,
homephone,
Lang,
ProjOwned
);
CREATE TABLE "Portal-Fee" (
keyName primary key, data
);
CREATE TABLE "Post-Proc" (
keyName primary key, data
);
CREATE TABLE "Pre-Proc" (
keyName primary key, data
);
CREATE TABLE Program (
myKey primary key
);
CREATE TABLE ProgramInfo
(
Program,
Contact,
Terminologist,
TechPM,
PM,
PMBkup,
BizType,
BizNature,
UKPM,
Languages
);
CREATE TABLE Program_A_No (
Program primary key,
year integer,
A_NoDesc,
text0,
text1
);
CREATE TABLE ProjectFiles (ProjID integer primary key, Filenames, FileLocs,
X0,X1, X2, X3, X4, X5, X6, X7, X8, X9);
CREATE TABLE "Publish-DITA" (
keyName primary key, data
);
CREATE TABLE "Publishing" (
keyName primary key, data
);
CREATE TABLE "Q-Notes" (
keyName primary key, data
);
CREATE TABLE "QA" (
keyName primary key, data
);
CREATE TABLE "QC" (
keyName primary key, data
);
CREATE TABLE "Revision" (
keyName primary key, data
);
CREATE TABLE "Rush-Job" (
keyName primary key, data
);
CREATE TABLE "SW-Trans" (
keyName primary key, data
);
CREATE TABLE "SW-Valid" (
keyName primary key, data
);
CREATE TABLE "ScreenCap" (
keyName primary key, data
);
CREATE TABLE SimplePrice (cust TEXT,
class TEXT,
slang TEXT,
tlang TEXT,
TransferCost,
Price,
PRIMARY KEY (cust, class, slang, tlang));
CREATE TABLE "TM-Maint" (
keyName primary key, data
);
CREATE TABLE "TM-Translation" (
keyName primary key, data
);
CREATE TABLE TMSubjectMatter (
keyName primary key, data
);
CREATE TABLE "TTX-Update" (
keyName primary key, data
);
CREATE TABLE TranslationPrice (cust TEXT,
class TEXT,
slang TEXT,
tlang TEXT,
BizType,
percent,
NewPrice,
MinCharge,
Hourly,
Daily, " Context TM ", " Repetitions ", " 100% ", " 95% - 99% ", " 85
% - 94% ", " 75% - 84% ", " 50% - 74% ", " No Match ", " Total ", PRIMARY KEY
(cust, class, slang, tlang)
);
CREATE TABLE TranslationPrices
(
Program,
Lang,
Class,
App,
Daily,
Hourly,
PerPage,
PerWord,
PerJob,
HrInDay,
Margin
);
CREATE TABLE "Validation" (
keyName primary key, data
);
CREATE TABLE VendorCoName (
myKey primary key
);
CREATE TABLE VendorEmails (
myKey primary key
);
CREATE TABLE VendorsData
(
vEmail primary key,
FirstName,
LastName,
Address,
City,
State,
Zip,
Country,
WorkPhone,
CellPhone,
HomePhone,
CompanyName,
Languages,
Services,
Prices,
ProgramsWorked,
Website,
ext0,
ext1,
ext2,
ext3,
ext4,
ext5,
ext6,
ext7,
ext8,
ext9,
extA,
extB,
extC,
extD,
extE,
extF
);
CREATE TABLE "VideoEditing" (
keyName primary key, data
);
CREATE TABLE "VoiceOver" (
keyName primary key, data
);
CREATE TABLE "Web-Trans" (
keyName primary key, data
);
CREATE TABLE "Web-Valid" (
keyName primary key, data
);
sqlite>_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
