Nilesh Nilesh - 6 months ago 8
SQL Question

Optimizing SQL Server Query with #temp Table and group by Condition

I have below query it is working as expected but taking long time to execute.

To obtain result i have fetched the records in temp table and then applied groupby to obtain final result

SELECT *
INTO #temp From (

select A.*,B.*
from (
select e.Id,e.code,e.Fname,
(select ComboValue from dbo.comvals where Id = c.BUDHOL) as [BUDHOL],
(select ComboValue from dbo.comvals where Id = c.COMPANY) as [COMPANY],
(select ComboValue from dbo.comvals where Id = c.BENEFICIARY) as [BENEFICIARY],
(select ComboValue from dbo.comvals where Id = c.SPARE2) as [SPARE2],
(select ComboValue from dbo.comvals where Id = c.SPARE1) as [SPARE1],
(select ComboValue from dbo.comvals where Id = c.EXPORG) as [EXPORG],
(select ComboValue from dbo.comvals where Id = c.EXPTYPE) as [EXPTYPE],
(select ComboValue from dbo.comvals where Id = c.OBJECTIVE) as [OBJECTIVE],
(select ComboValue from dbo.comvals where Id = c.TASKNO) as [TASKNO],
(select ComboValue from dbo.comvals where Id = c.PROJECTNO) as [PROJECTNO]
from dbo.EmployeeDetail e
left join (
select *
from (
select CustomeFieldName,FieldValue,CompanyId,EmployeeId
from dbo.CustomeFieldDetail
where CustomeFieldName in ('BENEFICIARY','BUDHOL','COMPANY', 'EXPORG','EXPTYPE','OBJECTIVE','PROJECTNO','SPARE2','SPARE1','TASKNO','COMPANY') and CompanyId = 1
)as src
pivot (max(FieldValue) for CustomeFieldName in ([BENEFICIARY],[BUDHOL],[EXPORG],[EXPTYPE],[OBJECTIVE],[PROJECTNO],[SPARE2],[SPARE1],[TASKNO],[COMPANY])
) as src2
) c on e.Id = c.EmployeeId and e.CompanyId = c.CompanyId and e.DateOfJoining <= convert(datetime,'30/03/2016',103) and e.CompanyId = 1
where e.Companyid=1 --order by e.Code
) A
Full Outer join (
select
T.EmployeeId,
X.[FieldName],
X.[Value]
from dbo.EmpTransaction T
cross apply (
SELECT
T.c.value('(@Name)[1]', 'Varchar(max)') AS [FieldName],
convert(float,T.c.value('(@Value)[1]', 'Varchar(max)')) AS [Value]
FROM TransactionFieldDetails.nodes('//PayDetails/Column') T(c)
where CompanyId = 1 and ProcessDate = convert(datetime,'01/03/2016',103) and CategoryId in (1)
and T.c.value('(@Name)[1]', 'Varchar(max)') in('FB')
) X
) B
ON A.id= B.EmployeeId
) As x



Final GroupBy Condition on Temp Table


SELECT B.BENEFICIARY,
B.BUDHOL,
B.COMPANY,
B.FIELDNAME,
B.OBJECTIVE,
B.SPARE1,
B.SPARE2,
B.PROJECTNO,
B.TASKNO,
B.EXPORG,
B.EXPTYPE,
SUM(B.value)
FROM (
SELECT DISTINCT A.BENEFICIARY,
A.BUDHOL,
A.COMPANY,
A.FIELDNAME,
A.OBJECTIVE,
A.value,
A.SPARE2,
A.SPARE1,
A.PROJECTNO,
A.TASKNO,
A.EXPORG,
A.EXPTYPE
FROM (
SELECT t.BENEFICIARY,
t.BUDHOL,
t.COMPANY,
t.FIELDNAME,
t.OBJECTIVE,
t.SPARE1,
t.SPARE2,
t.PROJECTNO,
t.TASKNO,
t.EXPORG,
t.EXPTYPE,
t.value value
FROM #temp T, #temp TT
) A
LEFT JOIN #temp T1 ON A.BENEFICIARY = t1.BENEFICIARY
AND A.BUDHOL = t1.BUDHOL
AND A.COMPANY = t1.COMPANY
AND A.OBJECTIVE = t1.OBJECTIVE
AND A.SPARE1 = t1.SPARE1
AND A.SPARE2 = t1.SPARE2
AND A.PROJECTNO = t1.PROJECTNO
AND A.TASKNO = t1.TASKNO
AND A.EXPORG = t1.EXPORG
AND A.EXPTYPE = t1.EXPTYPE
) B
GROUP BY B.BENEFICIARY,
B.BUDHOL,
B.COMPANY,
B.OBJECTIVE,
B.FIELDNAME,
B.SPARE1,
B.SPARE2,
B.PROJECTNO,
B.TASKNO,
B.EXPORG,
B.EXPTYPE



Above Query is working fine and as expected.
need to know whether above query can be optimized so that i can take less time to execute currently it take more than 2 min to execute, also can we combine both queries i.e. i dont want to use #temp table

Answer
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
    DROP TABLE #temp

SELECT A.*, B.*
INTO #temp
FROM (
    SELECT
        e.Id,
        e.code,
        e.Fname,
        c.[BUDHOL],
        c.[COMPANY],
        c.[BENEFICIARY],
        c.[SPARE2],
        c.[SPARE1],
        c.[EXPORG],
        c.[EXPTYPE],
        c.[OBJECTIVE],
        c.[TASKNO],
        c.[PROJECTNO]
    FROM dbo.EmployeeDetail e  
    LEFT JOIN (
        SELECT *
        FROM (
            SELECT
                  d.CustomeFieldName
                , c.ComboValue
                , d.EmployeeId
            FROM dbo.CustomeFieldDetail d
            LEFT JOIN dbo.comvals c ON c.Id = d.FieldValue
            WHERE d.CustomeFieldName IN (
                    'BENEFICIARY', 'BUDHOL', 'COMPANY',
                    'EXPORG', 'EXPTYPE', 'OBJECTIVE',
                    'PROJECTNO', 'SPARE2', 'SPARE1',
                    'TASKNO', 'COMPANY'
                )
                AND d.CompanyId = 1
        ) src 
        PIVOT (
            MAX(ComboValue)
            FOR CustomeFieldName IN (
                [BENEFICIARY],[BUDHOL],[EXPORG],
                [EXPTYPE],[OBJECTIVE],[PROJECTNO],
                [SPARE2],[SPARE1],
                [TASKNO],[COMPANY]
           )
        ) src2
    ) c ON e.Id = c.EmployeeId
        AND e.DateOfJoining <= '20160330'
    WHERE e.Companyid = 1
) A
FULL JOIN (
    SELECT t.EmployeeId,
           x.[FieldName],
           x.[Value]
    FROM dbo.EmpTransaction t
    CROSS APPLY (
        SELECT t.c.value('(@Name)[1]', 'VARCHAR(1000)') AS [FieldName],
               t.c.value('(@Value)[1]', 'FLOAT') AS [Value]
        FROM TransactionFieldDetails.nodes('//PayDetails/Column') t(c)
    ) x
    WHERE [FieldName] = 'FB'
        AND CompanyId = 1
        AND ProcessDate = '20160301'
        AND CategoryId = 1
) B ON A.id = B.EmployeeId

SELECT B.BENEFICIARY,
       B.BUDHOL,
       B.COMPANY,
       B.FIELDNAME,
       B.OBJECTIVE,
       B.SPARE1,
       B.SPARE2,
       B.PROJECTNO,
       B.TASKNO,
       B.EXPORG,
       B.EXPTYPE,
       SUM(B.value)
FROM (
    SELECT DISTINCT A.BENEFICIARY,
                    A.BUDHOL,
                    A.COMPANY,
                    A.FIELDNAME,
                    A.OBJECTIVE,
                    A.value,
                    A.SPARE2,
                    A.SPARE1,
                    A.PROJECTNO,
                    A.TASKNO,
                    A.EXPORG,
                    A.EXPTYPE
    FROM #temp A
    LEFT JOIN #temp T1 ON A.BENEFICIARY = t1.BENEFICIARY
        AND A.BUDHOL = t1.BUDHOL
        AND A.COMPANY = t1.COMPANY
        AND A.OBJECTIVE = t1.OBJECTIVE
        AND A.SPARE1 = t1.SPARE1
        AND A.SPARE2 = t1.SPARE2
        AND A.PROJECTNO = t1.PROJECTNO
        AND A.TASKNO = t1.TASKNO
        AND A.EXPORG = t1.EXPORG
        AND A.EXPTYPE = t1.EXPTYPE
) B
GROUP BY B.BENEFICIARY,
         B.BUDHOL,
         B.COMPANY,
         B.OBJECTIVE,
         B.FIELDNAME,
         B.SPARE1,
         B.SPARE2,
         B.PROJECTNO,
         B.TASKNO,
         B.EXPORG,
         B.EXPTYPE