audux
04-20-2006, 09:50 PM
a nice day to every one, this is my first time to post here, i was informed about this community and i hope that i could share here
i am using sql 2000
i have a problem with retrieving records in tables, i would like to ask if there are ways on how could i optimized my query for faster returns.
the concept is summation of fields in a table which applies to 2 different tables, joining the tables together for other data.
this is how the content of my procedure look like:
SELECT A.pname, A.pid, B.case, C.cfldsum, D.dfldsum, E.efldsum
FROM a INNER JOIN
b ON A.pid = B.pid INNER JOIN
( -- defining 1st alias
SELECT case, sum(fld1 * fld2) as cfldsum
FROM xC
WHERE tbl = 'OLD'
GROUP BY case
) c ON B.case = C.case LEFT JOIN
( -- defining 2nd alias
SELECT case, sum(fld1 * fld2) as dfldsum
FROM xC
WHERE tbl = 'NEW'
GROUP BY case
) d ON B.case = D.case LEFT JOIN
( -- defining 3rd alias
SELECT case, sum(fld1 + fld2 + fld3 + fld4 + fld5) AS efdlsum
FROM xE
GROUP BY case
) e ON B.case = E.case
WHERE C.cfldsum - (D.dfldsum + E.efldsum) IS NOT NULL AND C.cfldsum - (D.dfldsum + E.efldsum) > 0
do i have to modify my query, or there are functions that i have to set.
anyway table xC contains around 700,000 records.
i am using sql 2000
i have a problem with retrieving records in tables, i would like to ask if there are ways on how could i optimized my query for faster returns.
the concept is summation of fields in a table which applies to 2 different tables, joining the tables together for other data.
this is how the content of my procedure look like:
SELECT A.pname, A.pid, B.case, C.cfldsum, D.dfldsum, E.efldsum
FROM a INNER JOIN
b ON A.pid = B.pid INNER JOIN
( -- defining 1st alias
SELECT case, sum(fld1 * fld2) as cfldsum
FROM xC
WHERE tbl = 'OLD'
GROUP BY case
) c ON B.case = C.case LEFT JOIN
( -- defining 2nd alias
SELECT case, sum(fld1 * fld2) as dfldsum
FROM xC
WHERE tbl = 'NEW'
GROUP BY case
) d ON B.case = D.case LEFT JOIN
( -- defining 3rd alias
SELECT case, sum(fld1 + fld2 + fld3 + fld4 + fld5) AS efdlsum
FROM xE
GROUP BY case
) e ON B.case = E.case
WHERE C.cfldsum - (D.dfldsum + E.efldsum) IS NOT NULL AND C.cfldsum - (D.dfldsum + E.efldsum) > 0
do i have to modify my query, or there are functions that i have to set.
anyway table xC contains around 700,000 records.