asked on

Efficiency in Microsoft SQL 2008 QUERY

Hi

I have this monsterous "report-to" query that necessitates the use of the following

Select columns from tables where reports_to > ' '
Union
Select columns from tables where reports_to =' '.

I had to do this because the records with the blank values would be repeated a zillion times.

Do you see how this query could be designed differently. I have not used any of the optimizing tools in sql query Analyzer to see it the query is effecient yet. But I will do this as soon as I get the DBA to give me permission to these tools.

Thank you,
Nigluc

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brock

ASKER

Hi Raj

Here is the actual query. In the second union, you need to dummy up the values of F.Last_Name, F.First_Name and then do the Reports_to = " " to fix it. This is my solution. I tried what you said. The design of this field in the table POS_DATA , does not allow nulls.
So I gave you the 'ugly' query so you can see the details.

Thanks,
Nigluc

SELECT A.POSITION_NBR, A.DEPTID, A.JOBCODE, A.REPORTS_TO, A.LOCATION, B.EMPLID, B.COMPRATE, C.LAST_NAME, C.FIRST_NAME, A.DESCR, D.DESCR, E.POSITION_NBR,F.LAST_NAME, F.FIRST_NAME,(CONVERT(CHAR(10),A.EFFDT,121)),D.SETID,D.DEPTID,(CONVERT(CHAR(10),D.EFFDT,121))
FROM POS_DATA A, POSTN_SRCH_QRY A1, JOB B, EMPSRCQRY B1, PERSDATA C, PERALSEQRY C1, DEPTTBL D, SETCNTRLREC D2, JOB E, PS_EMPLSRCHQRY E1, PERSDATA F, PS_PERSECQRY F1
WHERE D.DEPTID = A.DEPTID
AND D2.SETCNTRLVALUE = A.BUSINESS_UNIT
AND D2.RECNAME = 'DEPTTBL'
AND D2.SETID = D.SETID
AND A.POSITION_NBR = A1.POSITION_NBR
AND A1.OPRID = 'FP'
AND B.EMPLID = B1.EMPLID
AND B.EMPL_RCD = B1.EMPL_RCD
AND B1.OPRID = 'FP'
AND C.EMPLID = C1.EMPLID
AND C1.OPRID = 'FP'
AND E.EMPLID = E1.EMPLID
AND E.EMPL_RCD = E1.EMPL_RCD
AND E1.OPRID = 'FP'
AND F.EMPLID = F1.EMPLID
AND F1.OPRID = 'FP'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM POS_DATA A_ED
WHERE A.POSITION_NBR = A_ED.POSITION_NBR
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.POSITION_NBR = B.POSITION_NBR
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.EMPLID = C.EMPLID
AND B.PAYGROUP = 'FTS'
AND B.EMPL_STATUS IN ('A','P')
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM DEPTTBL D_ED
WHERE D.SETID = D_ED.SETID
AND D.DEPTID = D_ED.DEPTID
AND D_ED.EFFDT <= A.EFFDT)
AND A.REPORTS_TO = E.POSITION_NBR
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM JOB E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.EMPL_RCD = E_ED.EMPL_RCD
AND E_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND E.EFFSEQ =
(SELECT MAX(E_ES.EFFSEQ) FROM JOB E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.EMPL_RCD = E_ES.EMPL_RCD
AND E.EFFDT = E_ES.EFFDT)
AND A.REPORTS_TO = E.POSITION_NBR
AND E.EMPLID = F.EMPLID )
AND A.REPORTS_TO > ' '

UNION

SELECT A.POSITION_NBR, A.DEPTID, A.JOBCODE, A.REPORTS_TO, A.LOCATION, B.EMPLID, B.COMPRATE, C.LAST_NAME, C.FIRST_NAME, A.DESCR, D.DESCR, E.POSITION_NBR,E.POSITION_NBR, E.POSITION_NBR,(CONVERT(CHAR(10),A.EFFDT,121)),D.SETID,D.DEPTID,(CONVERT(CHAR(10),D.EFFDT,121))
FROM POS_DATA A, POSTN_SRCH_QRY A1, JOB B, EMPSRCQRY B1, PERSDATA C, PERALSEQRY C1, DEPTTBL D, SETCNTRLREC D2, JOB E, PS_EMPLSRCHQRY E1, PERSDATA F, PS_PERSECQRY F1
WHERE D.DEPTID = A.DEPTID
AND D2.SETCNTRLVALUE = A.BUSINESS_UNIT
AND D2.RECNAME = 'DEPTTBL'
AND D2.SETID = D.SETID
AND A.POSITION_NBR = A1.POSITION_NBR
AND A1.OPRID = 'FP'
AND B.EMPLID = B1.EMPLID
AND B.EMPL_RCD = B1.EMPL_RCD
AND B1.OPRID = 'FP'
AND C.EMPLID = C1.EMPLID
AND C1.OPRID = 'FP'
AND E.EMPLID = E1.EMPLID
AND E.EMPL_RCD = E1.EMPL_RCD
AND E1.OPRID = 'FP'
AND F.EMPLID = F1.EMPLID
AND F1.OPRID = 'FP'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM POS_DATA A_ED
WHERE A.POSITION_NBR = A_ED.POSITION_NBR
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.POSITION_NBR = B.POSITION_NBR
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.EMPLID = C.EMPLID
AND B.PAYGROUP = 'FTS'
AND B.EMPL_STATUS IN ('A','P')
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM DEPTTBL D_ED
WHERE D.SETID = D_ED.SETID
AND D.DEPTID = D_ED.DEPTID
AND D_ED.EFFDT <= A.EFFDT)
AND A.REPORTS_TO = E.POSITION_NBR
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM JOB E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.EMPL_RCD = E_ED.EMPL_RCD
AND E_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND E.EFFSEQ =
(SELECT MAX(E_ES.EFFSEQ) FROM JOB E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.EMPL_RCD = E_ES.EMPL_RCD
AND E.EFFDT = E_ES.EFFDT)
AND A.REPORTS_TO = E.POSITION_NBR
AND E.EMPLID = F.EMPLID )
AND A.REPORTS_TO = ' '

Brock

ASKER

As I have not got another response to this query, I will close this. I am rewarding the points to you because you responded. I am keeping my solution as this is what I know to work.

Thanks,

Nigluc

Rajkumar Gs

Nigluc,
Sorry as I couldn't help you regarding this question clearly.

In your upcoming questions if you face this type of scenario, I suggest you to click on 'Request Attention' button below to the question - which invites more expert's attention to your question.

All the best
Raj

Brock

ASKER

Thank you Raj,

I appreciate it. Right now I am viewing a video on C.J.Date SQL and Relational Theory Master Class in hope to better master the modelling of a problem and also to be able to better express my issues :-)

Nigluc