Dan Kennedy, on Friday, April 26, 2019 12:13 PM wrote...
>On 26/4/62 21:30, Jose Isaias Cabrera wrote:
>Hey! Somebody tried it out! Thanks! :)
I have been using it for a while. ;-) I have been adding INDEXes for queries
used a lot...
>this query. If you try ".expert -verbose", it will tell you the other
This is the output from --verbose
sqlite> .expert --verbose
sqlite> SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a
...> LEFT JOIN Project_ABT_Budget AS b ON a.ProjID = b.ProjID
...> AND
...> b.InsertDate =
...> (SELECT MAX(InsertDate) FROM Project_ABT_Budget WHERE b.ProjID =
ProjID)
...> WHERE a.ProjID IN
...> (
...> SELECT a.ProjID FROM Project_List WHERE 1=1
...> AND lower(a.Manager) LIKE '%diggs%'
...> ) AND a.InsertDate =
...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
...>
...> GROUP BY a.ProjID;
-- Candidates -----------------------------
(null)
-- Query 1 --------------------------------
SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a
LEFT JOIN Project_ABT_Budget AS b ON a.ProjID = b.ProjID
AND
b.InsertDate =
(SELECT MAX(InsertDate) FROM Project_ABT_Budget WHERE b.ProjID = ProjID)
WHERE a.ProjID IN
(
SELECT a.ProjID FROM Project_List WHERE 1=1
AND lower(a.Manager) LIKE '%diggs%'
) AND a.InsertDate =
(SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
GROUP BY a.ProjID;
(no new indexes)
SCAN TABLE Project_List AS a USING INDEX ProjID_InsertDate
CORRELATED LIST SUBQUERY 2
SCAN TABLE Project_List USING COVERING INDEX Project_Name
CORRELATED SCALAR SUBQUERY 3
SEARCH TABLE Project_List USING COVERING INDEX ProjID_InsertDate (ProjID=?)
SEARCH TABLE Project_ABT_Budget AS b USING COVERING INDEX sqlite_autoindex_Proje
ct_ABT_Budget_1 (ProjID=?)
CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE Project_ABT_Budget USING COVERING INDEX PAB_ProjIDInsertDateProjIDI
nsertDate (ProjID=?)
Maybe Candidates should say something else other than (null).
>schema (use the shell tool's ".schema" command). It makes it easier to
Yes, I know this should be broken down better, but this is a reporting tool
that I am creating to quickly and fast report factual project data by importing
exports from a system. Instead of Excel scripts, SQLite takes the imports,
keeps track of the changes, makes smart reporting to users and provide process
suggestions to prevent projects from getting outside their original baseline.
So, if here is the .schema, Dan. ;-)
sqlite> .schema
CREATE TABLE PMData
(
login primary key,
email,
FirstName,
LastName,
Address,
City,
State,
Zip,
WorkPhone,
Intelnet,
HomePhone,
Password,
ComputerName,
UserProfilePath
, Beep, PMOHome, UseMailTo, SoundOff, UseLocalSharedDB, LastClarityUpdate,
PMLocalDrive);
CREATE TABLE Master_Project_List_Extra
(
ProjID PRIMARY KEY,
PMONotes,
CurrYear,
PlanYear,
KeyProj,
PMOStatus
, BusApprDate, RepBoardDir, RepIberOpComm, Cost_Type, CompanyName,
CurAgreedDel, InitPortYr, Department, FinancialStatus, FinancialType,
Global_Local, WIP_Class, BusCaseVer, ITOpCo, InitAgreedEndDate, ChargeCode,
ResOBSUnit, DepName, ITStrategy, Program, ResCode, Sponsor, InBusPlan, InPDS,
InSubDept, LaunchRev, ALaborRes, CurAgrFinishDate, TimeIndiVal, TimeIndiColor,
CostIndiVal, CostIndiColor, EstiDeliDate, PlannedProgess, ActualProgess,
CommType, AnnualEACExt, BusCaseTotCost, AnnCostExt, EACTotCost, TimeMargin,
ProgressDevVal, ProgressDevColor, CostDevVal, CostDevColor, EACExt, ActualsExt,
EACvBusTotVal, EACvBusTotColor, EACvAnnPlanVal, EACvAnnPlanColor, EACvCBL,
PlannedCostPcent, ActualCostPcent, TimevCBLKPIVal, TimevCBLKPIColor,
AnnActualExt, AnnPlanCostPcent, ApprComDateTimeMargin, IncludedInPDS, IsActive,
ProjCBLExt);
CREATE TABLE `Bus_IT_Areas_ORGs` (
`IT_OBS` TEXT NOT NULL UNIQUE,
`Area` TEXT,
`Org` INTEGER,
PRIMARY KEY(`IT_OBS`)
);
CREATE TABLE Business_OBS_List
(
Bus_OBS TEXT NOT NULL UNIQUE,
Bus_Area,
Bus_Org TEXT,
PRIMARY KEY(Bus_Area)
);
CREATE TABLE PMOTitles
(
TitleKey PRIMARY KEY,
Titles
);
CREATE TABLE Project_List
(
ProjID,
CID,
Project_Name,
PMO_Board_Report,
Project_Type,
Start_date,
Target_Go_Live_Date,
Finish_Date,
BL_Start,
BL_Finish,
Tot_CapexP,
CapexP_Cur,
Tot_OpexP,
OpexP_Cur,
Manager,
Status_Indicator,
SI_Color,
Progress,
status_comment,
State,
Business_Owner,
BRD,
Business_priority,
BP_Color,
First_BL,
Updated_By,
Updated_Dt,
Status,
Baseline_Cost,
Baseline_Cur,
Baseline_Time,
Active,
PMO_Percentage,
PMO_Request,
Financial_Status,
Ann_CapexP,
Ann_OpexP,
YTD_Ann_Capex,
YTD_Ann_Opex,
YTD_Tot_Capex,
YTD_Tot_Opex,
Project_Delivered,
Project_Launching,
Real_Finish,
PPM,
Business_Case,
IT_OBS,
Business_OBS,
Dept_OBS,
Purpose,
Project_Manager,
Actual_Capex,
Capex_Cur,
Investment_Initiative,
InsertedBy,
InsertDate,
PRIMARY KEY (ProjID, InsertDate)
);
CREATE TABLE Project_Dashboard
(
ProjID,
CurAgrFinishDate,
StartDate,
FinishDate,
TimeIndicator,
CostIndicator,
EstDelDate,
PlannedProgress,
ActualProgress,
CommType,
AnnualEACExt,
BusCaseAppDate,
BusCaseTotCost,
AnnCostExt,
EACTotCost,
EACExt,
ActualsExt,
LaunchDate,
AnnActualExt,
IncludedInPDS,
ProjCBLExt,
InsertedBy,
InsertDate,
PRIMARY KEY (ProjID,InsertDate)
);
CREATE INDEX PL_ProjID ON Project_List (ProjID);
CREATE INDEX ProjID_InsertDate ON Project_List (ProjID, InsertDate);
CREATE INDEX Manager ON Project_List (Manager);
CREATE INDEX ProjID_Progress_PMOBR_TGLD_FD_IDate ON Project_List (ProjID,
Progress, PMO_Board_Report, Target_Go_Live_Date, Finish_Date, InsertDate);
CREATE INDEX IT_OBS ON Project_List (IT_OBS);
CREATE INDEX ProjID_Progress_PMOBR_TGLD_FD_IDate_ITOBS_BOBS ON Project_List
(ProjID, Progress, PMO_Board_Report, Target_Go_Live_Date, Finish_Date, IT_OBS,
Business_OBS, InsertDate);
CREATE INDEX InsertDate ON Project_List (InsertDate);
CREATE TABLE Project_List_Extra
(
ProjID,
PMONotes,
KeyProj,
FinCarryOver,
ITOpCo,
InsertedBy,
InsertDate,
PRIMARY KEY (ProjID, InsertDate)
);
CREATE TABLE Project_List_Extra2
(
ProjID,
PMOLongNotes,
ProjectFiles,
Path,
PRIMARY KEY (ProjID)
);
CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);
CREATE INDEX Project_Name ON Project_List (Project_Name);
CREATE INDEX PLE_ProjIDInsertDate ON Project_List_Extra (InsertDate);
CREATE INDEX PLE_ProjIDInsertDateProjIDInsertDate ON Project_List_Extra
(ProjID,InsertDate);
CREATE INDEX PLE2_ProjID ON Project_List_Extra2 (ProjID);
CREATE INDEX PDash_ProjID ON Project_Dashboard (ProjID);
CREATE INDEX PDash_ProjIDInsertDate ON Project_Dashboard (InsertDate);
CREATE INDEX PDash_ProjIDInsertDateProjIDInsertDate ON Project_Dashboard
(ProjID,InsertDate);
CREATE TABLE Project_ABT_Budget
(
ProjID,
Posting,
Role,
Rev1,
Rev2,
Rev3,
EAC,
Actuals,
AnnualDossier,
OpCo,
InsertedBy,
InsertDate,
PRIMARY KEY
(ProjID,Posting,Role,Rev1,Rev2,Rev3,EAC,Actuals,AnnualDossier,OpCo,InsertedBy,InsertDate)
);
CREATE INDEX PAB_ProjID ON Project_ABT_Budget (ProjID);
CREATE INDEX PAB_ProjIDInsertDate ON Project_ABT_Budget (InsertDate);
CREATE INDEX PAB_ProjIDInsertDateProjIDInsertDate ON Project_ABT_Budget
(ProjID,InsertDate);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
sqlite>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
