[sqlite] SQLite Query Assistance

[Bharath Booshan L]

Hi All,

 Following are the table structures I am using in one of my application

 System_table

 | Name | SystemID PRIMARY KEY |

 Process_table
 | Name | SytemID REFERENCES  System_table | ProcessID  PRIMARY KEY |
ParentProcessID | IsParentProcess |

Each process represented by UNIQUE ProcessID may or may not have parent
process, which 
is inturn a process. Only one level of hierarchy is allowed i.e. A Parent
Process cannot does not have any parent and its ParentProcessID contains
NULL value.

IsParentProcess is either 1 if the associated process is parent process or 0
(Zero) if it is child process.

 
 Task1_table
 | Name | TaskID |

 Task1_info_table
 
 | TaskID REFERENCES Task1_info_table | ProcessID REFERENCES Process_table |
IsParentProcess |


Task2_table
 | Name | TaskID |

 Task2_info_table
 
 | TaskID REFERENCES Task2_info_table | ProcessID REFERENCES Process_table |
IsParentProcess |

.
.
.
.

TaskN_table
 | Name | TaskID |

 TaskN_info_table
 
 | TaskID REFERENCES TaskN_info_table | ProcessID REFERENCES Process_table |
IsParentProcess |



I have a UserInterface in my application where the user can select Tasks
under Task1, Task2, .... TaskN tables and following information can be
extracted from the User Interface

³Task1 Name CONTAINS  ŒXYZ¹ AND Taks2 Name CONTAINS ŒABC¹²
³Any Task Name DOES NOT CONTAIN ŒABC Œ²


I need create a query which retrieves all the Child ProcessID, with
following conditions, running under system identified by SystemID

1. Retrieve all ProcessIDs whose parent process is running which is not
currently associated with any Task with Name which contains 'XYZ'

2. Retrieve all the ProcessIDs, who do not have a parent process, and is not
currently associated with any Task with Name which contains ŒXYZ¹

The Result should contain ProcessIDs resulted from 1 & 2 and should not
contain any duplicates.



Query which I have implemented is


SELECT Process.ProcessID
FROM Process_table Process, System_table System
WHERE System.SystemID IN ( 1,2,3,4......,N) AND IsParentProcess = 0 AND
Process.ProcessID IN (

SELECT * FROM (
 SELECT Process.ProcessID
 FROM Process_table ChildProcess, ( SELECT ProcessID FROM Task1_table,
Task1_Info_table WHERE Task1_table.TaskID = Task1_Info_table.TaskID AND
Task1_table.Name NOT LIKE Œ%XYZ%¹ AND Task1_Info_table.IsParentProcess = 1)
AS ParentProcess
WHERE ChildProcess.ParentProcessID = ParentProcess.ProcessID

UNION

SELECT ProcessID FROM Task1_table, Task1_Info_table WHERE Task1_table.TaskID
= Task1_Info_table.TaskID AND Task1_table.Name NOT LIKE Œ%XYZ%¹ AND
Task1_Info_table.IsParentProcess = 0 )

INTERSECT

SELECT * FROM (
 SELECT Process.ProcessID
 FROM Process_table ChildProcess, ( SELECT ProcessID FROM Task2_table,
Task2_Info_table WHERE Task2_table.TaskID = Task2_Info_table.TaskID AND
Task2_table.Name NOT LIKE Œ%XYZ%¹ AND Task2_Info_table.IsParentProcess = 1)
AS ParentProcess
WHERE ChildProcess.ParentProcessID = ParentProcess.ProcessID

UNION

SELECT ProcessID FROM Task2_table, Task2_Info_table WHERE Task2_table.TaskID
= Task2_Info_table.TaskID AND Task2_table.Name NOT LIKE Œ%XYZ%¹ AND
Task2_Info_table.IsParentProcess = 0 )

.
.
.


INTERSECT

SELECT * FROM (
 SELECT Process.ProcessID
 FROM Process_table ChildProcess, ( SELECT ProcessID FROM TaskN_table,
TaskN_Info_table WHERE TaskN_table.TaskID = TaskN_Info_table.TaskID AND
TaskN_table.Name NOT LIKE Œ%XYZ%¹ AND Task1_Info_table.IsParentProcess = 1)
AS ParentProcess
WHERE ChildProcess.ParentProcessID = ParentProcess.ProcessID

UNION

SELECT ProcessID FROM TaskN_table, TaskN_Info_table WHERE TaskN_table.TaskID
= TaskN_Info_table.TaskID AND TaskN_table.Name NOT LIKE Œ%XYZ%¹ AND
TaskN_Info_table.IsParentProcess = 0 )

)


Note: Following Information are known only at run-time depending upon users
input
          *  SystemID of the System under which processes needs to be
searched
          *  Task table to be Queried


The above Query takes around 7 sec approx to retrieve around 8000 ProcessIDs
which is un-acceptable.

Please provide me any inputs on how can I optimize this query.


Thanks for any assistance

Bharath Booshan L 
         



-----------------------------------------------
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.