| Optimized Query [message #374251] |
Sat, 02 June 2001 03:27 |
Siddharth Bahri
Messages: 18
Registered: March 2001
|
Junior Member |
|
|
Hi friends,
I want to optimize a query. It takes about 5 minutes to execute this query on my local database.
The database structure is like this
Group Group Table
GroupID PK
GroupName
Cat Category Table
CatID PK
CatName
Group_To_Cat Mapping Table
GroupID FK
CatID FK
Main Main Table
ID PK
Name
CatID FK
Type
I want to get the total count, count of records with type 'B' and count of records with type 'S' for all the groups by adding up all the records in the main table having the categories which are mapped to the particular group.
My Query is
select distinct G1.GroupName, G1.GroupID,
(select count(*) from GROUP G2, CAT, GRP_TO_CAT, MAIN WHERE G2.GroupID=GRP_TO_CAT.GroupID AND CAT.CatID=GRP_TO_CAT.CatID AND MAIN.CatID=CAT.CatID
AND G2.GroupID=G1.GroupID) CNT,
(select count(*) from GROUP G3, CAT, GRP_TO_CAT, MAIN WHERE G3.GroupID=GRP_TO_CAT.GroupID AND CAT.CatID=GRP_TO_CAT.CatID AND MAIN.CatID=CAT.CatID
AND G3.GroupID=G1.GroupID
AND Type='B') BCNT,
(select count(*) from GROUP G4, CAT, GRP_TO_CAT, MAIN WHERE G4.GroupID=GRP_TO_CAT.GroupID AND
CAT.CatID=GRP_TO_CAT.CatID AND Main.CatID=CAT.CatID AND G4.GroupID=G1.GroupID
AND ETO_OFR_TYP='S') SCNT,
FROM GROUP G1, CAT, GRP_TO_CAT, MAIN WHERE G1.GroupID=GRP_TO_CAT.GroupID AND
CAT.CatID=GRP_TO_CAT.CatID AND
MAIN.CatID=CAT.CatID
I tried using snapshots but it says that subqueries are not supported.
Can anyone suggest a faster query to do this job.
-Siddharth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
