Re: [sqlite] .expert output help

2019-04-26 Thread Dan Kennedy 


On 26/4/62 23:56, Jose Isaias Cabrera wrote:

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)
...

Maybe Candidates should say something else other than (null).


That means it couldn't even come up with anything to try - you already 
have indexes for all WHERE constraints an ORDER/GROUP BY terms in the 
query. It should probably say "(no candidates found)", or something 
along those lines.


Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .expert output help

2019-04-26 Thread Jose Isaias Cabrera 
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,

Re: [sqlite] .expert output help

2019-04-26 Thread Dan Kennedy 


On 26/4/62 21:30, Jose Isaias Cabrera wrote:

Greetings.

sqlite> .expert
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 '%jic%'
...>  ) 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_Project_ABT_Budget_1 (ProjID=?)
CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE Project_ABT_Budget USING COVERING INDEX 
PAB_ProjIDInsertDateProjIDInsertDate (ProjID=?)

I have a few questions for the .experts :-),



Hey! Somebody tried it out! Thanks! :)


-- On line 5 of the resulted output ,

SCAN TABLE Project_List USING COVERING INDEX Project_Name

Why is it scanning the table using that INDEX if there is no "Project_Name" 
referenced in the query? That is one of the fields of Project_List, but it is not being 
used now.


There's either an error or a strange construction created by a program 
in this part of the query:


 WHERE a.ProjID IN
   ...>  (
   ...>  SELECT a.ProjID FROM Project_List WHERE 1=1
   ...>  AND lower(a.Manager) LIKE '%jic%'
   ...>  )


The sub-query scans table "Project_List", but doesn't use any columns 
from it (both a.ProjID and a.Manager are from the outer query). So, to 
do the scan, SQLite is choosing the index it thinks will require the 
minimum IO. i.e. one on very few fields.



-- How can I create the INDEX on line 8 that sqlite_autoindex created?


You have already done so. Index "sqlite_autoindex_Project_ABT_Budget_1" 
is actually a PRIMARY KEY or UNIQUE constraint within the definition of 
table "Project_ABT_Budget".


In this case, the key piece of output is "(no new indexes)". This means 
that ".expert" thinks you have already created the optimal indexes for 
this query. If you try ".expert -verbose", it will tell you the other 
indexes it considered. Or, if you run it on a version of your db that 
has no indexes at all, you can see that it will (hopefully!) recommend 
indexes equivalent to those you already have.


For posts like these, unless you're constrained by company rules or some 
other similar consideration, it's good to include the entire database 
schema (use the shell tool's ".schema" command). It makes it easier to 
answer questions regarding specific queries and allows people to 
recreate your experiment themselves if they wish to investigate further.


Dan.




Thanks for your knowledge sharing.

josé

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .expert output help

2019-04-26 Thread Jose Isaias Cabrera 

Simon Slavin on Friday, April 26, 2019 10:50 AM wrote...
>On 26 Apr 2019, at 3:30pm, Jose Isaias Cabrera  wrote:

>Once you have defined those indices, execute the ANALZE command, then try
>the .expert again.
Thanks.

>What led to you using WHERE 1=1 ?  An apparent bug ?
Long story. The app has many textboxes where the user can insert a string and 
hit ENTER and so a search is added to the query based on the textboxes that 
have data in them.  So, having WHERE 1=1 makes it easy to have other search 
additions by just doing,

AND (a.field_A LIKE %jic%)

and adding another one,

AND (b.field_B LIKE %something%)

etc., etc. So, in the end the query ends up being something like this,

 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.field_A LIKE %jic%)
 AND (a.field_B LIKE %something%)
 ) AND a.InsertDate =
(SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)

 GROUP BY a.ProjID;

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .expert output help

2019-04-26 Thread Simon Slavin 
On 26 Apr 2019, at 3:30pm, Jose Isaias Cabrera  wrote:

> -- On line 5 of the resulted output ,
> 
> SCAN TABLE Project_List USING COVERING INDEX Project_Name
> 
> Why is it scanning the table using that INDEX if there is no "Project_Name" 
> referenced in the query? That is one of the fields of Project_List, but it is 
> not being used now.

Since there is no ideal index it needs to scan the list using something.  That 
index seems to be fairly short and covers all the data needed, because all data 
can be retrieved directly from the index rather than needing an additional 
lookup in the table itself.

> -- How can I create the INDEX on line 8 that sqlite_autoindex created?

I think you mean

SEARCH TABLE Project_ABT_Budget AS b USING COVERING INDEX 
sqlite_autoindex_Project_ABT_Budget_1 (ProjID=?)

in which case the command is

CREATE INDEX myName1 ON Project_ABT_Budget (ProjID)

You should also do something like

CREATE INDEX myName2 ON Project_List (InsertDate)
CREATE INDEX myName3 ON Project_List (ProjID,InsertDate)

Once you have defined those indices, execute the ANALZE command, then try the 
.expert again.

What led to you using WHERE 1=1 ?  An apparent bug ?


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .expert output help

2019-04-26 Thread Jose Isaias Cabrera 

Greetings.

I want to search the DB for all projects owned by "jic" and I have queried the 
DB with the following:
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> .expert
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 '%jic%'
   ...>  ) 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_Project_ABT_Budget_1 (ProjID=?)
CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE Project_ABT_Budget USING COVERING INDEX 
PAB_ProjIDInsertDateProjIDInsertDate (ProjID=?)

I have a few questions for the .experts :-),

-- On line 5 of the resulted output ,

SCAN TABLE Project_List USING COVERING INDEX Project_Name

Why is it scanning the table using that INDEX if there is no "Project_Name" 
referenced in the query? That is one of the fields of Project_List, but it is 
not being used now.

-- How can I create the INDEX on line 8 that sqlite_autoindex created?

Thanks for your knowledge sharing.

josé

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users