问存储过程提高性能

EN

DECLARE @RelationCode nvarchar(50)
DECLARE @CompanyName nvarchar(256)
DECLARE @IncomingInvoice decimal(18, 2)
DECLARE @OutgoingInvoice decimal(18, 2)
DECLARE @Profit decimal(18, 2)
DECLARE @RelationTable as TABLE (RELATIONCODE nvarchar(10), COMPANY nvarhar(120))

INSERT INTO @RelationTable (RELATIONCODE, COMPANY) SELECT [fms].[dbo].[Relation].[RELATIONCODE], [fms].[dbo].[Relation].[COMPANYNAME] FROM [fms].[dbo].[Relation]

/* Result table */
DECLARE @RESULTTABLE TABLE (RelationCode nvarchar(50), Companyname nvarchar(256), IncomingInvoice nvarchar(50), OutgoingInvoice nvarchar(50), profit nvarchar(50))

WHILE EXISTS(SELECT * FROM @RelationTable)
BEGIN
SELECT TOP 1 @RelationCode = RELATIONCODE, @CompanyName = COMPANY FROM @RelationTable

    SELECT fmsTotalAmountIncoming INTO TempIncomingAmounts FROM (
            SELECT SUM(CASE WHEN fms1.currency != 'EUR'
                THEN fms1.amount * fms1.rate
            ELSE ISNULL(fms1.amount, 0) END) fmsTotalAmountIncoming
    FROM [fms].[dbo].[file] f
        INNER JOIN [fms].[dbo].[incominginvoiceline] fms1 ON 
       fms1.filenumber = CONVERT(NVARCHAR, f.filenumber)
    WHERE f.RELATIONCODE = @RelationCode
    ) a 

    SELECT fmsTotalAmountOutgoing INTO TempOutgoingAmounts FROM (
            SELECT SUM(CASE WHEN fms1.currency != 'EUR'
                THEN fms1.amount * fms1.rate
            ELSE ISNULL(fms1.amount, 0) END) fmsTotalAmountOutgoing
    FROM [fms].[dbo].[file] f
        INNER JOIN [fms].[dbo].[outgoinginvoiceline] fms1 ON 
       fms1.filenumber = CONVERT(NVARCHAR, f.filenumber)
    WHERE f.RELATIONCODE = @RelationCode
    ) a 
    SET @IncomingInvoice = (SELECT fmsTotalAmountIncoming FROM [fms].[dbo].[TempIncomingAmounts])
        SET @OutgoingInvoice = (SELECT fmsTotalAmountOutgoing FROM [fms].[dbo].[TempOutgoingAmounts])
        SET @Profit = ((@OutgoingInvoice - @IncomingInvoice) / @OutgoingInvoice)

        INSERT INTO @RESULTTABLE ([RELATIONCODE], [CompanyName], [IncomingInvoice], [OutgoingInvoice], [Profit])
                VALUES (@RelationCode, @CompanyName, @IncomingInvoice, @OutgoingInvoice, @Profit)

        DROP TABLE [fms].[dbo].[TempIncomingAmounts]
        DROP TABLE [fms].[dbo].[TempOutgoingAmounts]

        DELETE FROM @RelationTable WHERE RelationCode = @RelationCode
    END

    SELECT * FROM @RESULTTABLE

STORED PROCEDURE        VB.NET
6:54                    5:11
6:20                    5:11
6:19                    3:55
6:43                    4:01