2
\$\begingroup\$

I have this view vw_RFQPartVendor that's based on these two views vw_requestVendor and vw_vendorEmail and various other tables in SQL 2005.

I find that view vw_RFQPartVendor is a little slow, anyway to improve it?

vw_RFQPartVendor 83250 rows.

SELECT TOP (100) PERCENT
    dbo.SubconJobQ.Regno,
    CASE
        WHEN userName.userName IS NULL THEN UserAcc
        ELSE userName.userName
    END AS requesterName,
    CASE dbo.SubconJobQ.IsPara
        WHEN '1' THEN 'yes'
        ELSE 'no'
    END AS IsPara,
    CASE dbo.SubconJobQ.IsDxf
        WHEN '1' THEN 'yes'
        ELSE 'no'
    END AS IsDxf,
    CASE dbo.SubconJobQ.IsPdf
        WHEN '1' THEN 'yes'
        ELSE 'no'
    END AS IsPdf,
    dbo.SubconJobQ.Rec_date,
    dbo.SubconJobQ.Cov_date,
    dbo.SubconJobQ.Status,
    dbo.SubconJobQ.Remark,
    dbo.SubconJobQ.plant,
    dbo.SubconJobQ.doc_support,
    CASE
        WHEN approverName.userName IS NULL THEN approver
        ELSE approverName.userName
    END AS approverName,
    dbo.SubconJobQ.Comment,
    dbo.SubconJobQ.sync_status,
    dbo.SubconJobQ.ModuleNo,
    CASE dbo.SubconJobQ.req_type
        WHEN 'sq' THEN 'SubContracting Part'
        WHEN 'sd' THEN 'Resend Drawing'
        WHEN 'pq' THEN 'New Part'
        ELSE ''
    END AS req_type,
    dbo.SubconJobQ.projectNo,
    dbo.Reg_parts.Partno,
    dbo.Reg_parts.Quantity,
    dbo.Reg_parts.Part_desc,
    dbo.vw_requestVendor.vendorCode,
    dbo.vw_vendorEmail.vendorEmails
FROM dbo.SubconJobQ
LEFT OUTER JOIN dbo.Reg_parts
    ON dbo.SubconJobQ.Regno = dbo.Reg_parts.Regno
LEFT OUTER JOIN dbo.vw_requestVendor
    ON dbo.SubconJobQ.Regno = dbo.vw_requestVendor.Regno
LEFT OUTER JOIN dbo.vw_vendorEmail
    ON dbo.vw_requestVendor.vendorCode = dbo.vw_vendorEmail.vendor
    AND dbo.SubconJobQ.plant = dbo.vw_vendorEmail.plant
LEFT OUTER JOIN 
    (
        SELECT
        user_acc,
        userName
        FROM dbo.t_user_auth
        GROUP BY    user_acc, userName
    ) AS userName
    ON userName.user_acc = dbo.SubconJobQ.UserAcc
LEFT OUTER JOIN 
    (
        SELECT
        user_acc,
        userName
        FROM dbo.t_user_auth AS t_user_auth_1
        GROUP BY    user_acc, userName
    ) AS approverName
    ON approverName.user_acc = dbo.SubconJobQ.approver
ORDER BY dbo.SubconJobQ.Regno, dbo.Reg_parts.Partno, dbo.vw_requestVendor.vendorCode

vw_requestVendor

WITH temp(Regno, vendorCode, vendor) AS 
    ( 
        SELECT
            Regno,
            LEFT(CAST(vendor AS NVARCHAR(MAX)), CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';') - 1) AS Expr1,
            STUFF(CAST(vendor AS NVARCHAR(MAX)), 1, CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';'), '') AS Expr2
        FROM dbo.SubconJobQ 
        UNION ALL 
        SELECT
            Regno,
            LEFT(CAST(vendor AS NVARCHAR(MAX)), CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';') - 1) AS Expr1,
            STUFF(CAST(vendor AS NVARCHAR(MAX)), 1, CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';'), '') AS Expr2
        FROM temp AS temp_2
        WHERE (vendor > '')
    )
SELECT TOP (100) PERCENT
    Regno,
    vendorCode
FROM temp AS temp_1
ORDER BY Regno, vendorCode

vw_vendorEmail

SELECT TOP 100 PERCENT
    *
FROM 
(
    SELECT
    outerVendorEmail.comp_code,
    outerVendorEmail.plant,
    outerVendorEmail.vendor,
    STUFF
    (
        (
            SELECT ';' + LTRIM(RTRIM(innerVendorEmail.email))
            FROM t_vendor_email AS innerVendorEmail
            WHERE innerVendorEmail.status = 1
            AND innerVendorEmail.comp_code = outerVendorEmail.comp_code
            AND innerVendorEmail.plant = outerVendorEmail.plant
            AND innerVendorEmail.vendor = outerVendorEmail.vendor
            FOR XML PATH ('')
        ), 1, 1, ''
    ) AS [vendorEmails]
    FROM t_vendor_email AS outerVendorEmail
) AS vendorEmails
GROUP BY    vendorEmails.comp_code,
            vendorEmails.plant,
            vendorEmails.vendor,
            vendorEmails.vendorEmails
ORDER BY vendorEmails.comp_code, vendorEmails.plant, vendorEmails.vendor

Execution plan for vw_RFQPartVendor

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.