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.