Sql-Server

建議創建表或設計儲存過程以刪除 while 循環

  • October 21, 2021

我目前工作的公司有一個場景，我們必須在 SQL 中使用 while 循環或游標。在我們的案例中，這會對性能產生巨大影響，因此，我想對其進行優化。

場景是我們有一個名為 的表DocType，將其視為主表。此表中的一列Name包含類似的數據PO_Invoice, NON-PO_Invoice, GRN。現在，還有其他表，例如XXX_PO_Invoices, XXX-PO_Invoices, XXX_GRN. 所以基本上，在DocType表上執行查詢，獲取名稱，然後我們必須將XXX部分添加到這些名稱中，並在表中查詢XXX_PO_Invoices, XXX-PO_Invoices, XXX_GRN。在表上執行的查詢DocType會根據要求給出不同的結果，因此我們無法對其進行硬編碼。為了查詢XXX表，我們使用 While 循環。有沒有更好的方法來實現這一目標？就像創建一個新表一樣？或者可以在查詢本身中完成一些事情。

DocType 的表定義 -

   [Id] [int] IDENTITY(1,1) NOT NULL,
   [IsActive] [bit] NOT NULL,
   [CreatedOn] [datetime] NOT NULL,
   [LastUpdated] [datetime] NOT NULL,
   [Name] [nvarchar](50) NOT NULL,
   [IsAP] [bit] NOT NULL,
   [ImportType] [int] NULL,
   [ImportTypeConfig] [int] NULL,
   [IndexingType] [nvarchar](50) NOT NULL,
   [ExtractionType] [nvarchar](50) NOT NULL,
   [OutgoingEmailAddress] [nvarchar](max) NULL,
   [MonitorEmail] [bit] NOT NULL,
   [InputFolder] [nvarchar](200) NULL,
   [ArchiveFolder] [nvarchar](200) NULL,
   [OutputFolder] [nvarchar](200) NULL,
   [StorageFolder] [nvarchar](200) NULL,
   [ExportFolder] [nvarchar](200) NULL,
   [FTP_URL] [nvarchar](200) NULL,
   [ExternalValidationEnabled] [bit] NOT NULL,
   [ApprovalEnabled] [bit] NOT NULL,
   [CodingEnabled] [bit] NOT NULL,
   [ManualVerificationEnabled] [bit] NOT NULL,
   [PostApprovalEnabled] [bit] NOT NULL,
   [PostEnabled] [bit] NOT NULL,
   [InputFolder_Conversion] [nvarchar](256) NULL,
   [IsRealIndexExtraction] [bit] NOT NULL,
   [StoreSupplierTemplate] [bit] NOT NULL,
   [AcceptHardCopy] [bit] NOT NULL,
   [DestructionDate] [datetime] NULL,
   [DisplayName] [nvarchar](550) NULL,
   [RenameFile] [bit] NOT NULL,
   [CreateDocumentRecord] [bit] NOT NULL,
   [SendInvalidAttachmentNotification] [bit] NOT NULL,
   [SendReassignNotification] [bit] NOT NULL,
   [AddEmailMetadataToComment] [bit] NOT NULL,
   [AddEmailAttachmentToDocument] [bit] NOT NULL,
   [SendCodingNotification] [bit] NOT NULL,
   [SendApprovalNotification] [bit] NOT NULL,
   [InputFolder_HTML_Conversion] [nvarchar](200) NULL,
   [ManualSeperationEnabled] [bit] NOT NULL,
   [WizardStatus] [int] NOT NULL,
   [CreateDefaultLine] [bit] NOT NULL,
   [IsGRN] [bit] NOT NULL,
   [IsPO] [bit] NOT NULL,
   [POMatchingEnabled] [bit] NOT NULL,
   [RequestParkReason] [bit] NOT NULL,
   [RequestDeleteReason] [bit] NOT NULL,
   [RequestReverifyReason] [bit] NOT NULL,
   [IsManualMonitor] [bit] NOT NULL,
   [ManualMonitorFolder] [nvarchar](200) NULL,
   [SplitFolder] [nvarchar](200) NULL,
   [BaseCurrency] [nvarchar](50) NULL,
   [AutomaticCodingEnabled] [bit] NOT NULL,
   [DisplayLineTotals] [bit] NOT NULL,
   [hasRelatedDocuments] [bit] NOT NULL,
   [HardDeleteInMonths] [int] NULL,
   [CopyPOLinesInVerification] [bit] NOT NULL,
   [IsBudget] [bit] NOT NULL,
   [HasBudget] [bit] NOT NULL,
   [POLineMatching] [bit] NOT NULL,
   [IsSupplierReconciliation] [bit] NOT NULL,
   [SendDocumentRejectedNotification] [bit] NOT NULL,
   [GenerateOnTheFly] [bit] NOT NULL,
   [LogoFile] [varbinary](max) NULL,
   [RequestRecreateReason] [bit] NOT NULL,
   [DocumentTypeGroup] [int] NULL,
   [POOriginatorAccessEnabled] [bit] NOT NULL,
CONSTRAINT [PK_DocumentType] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [PostApprovalEnabled]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [IsRealIndexExtraction]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [StoreSupplierTemplate]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [AcceptHardCopy]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((1)) FOR [RenameFile]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((1)) FOR [CreateDocumentRecord]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((1)) FOR [SendInvalidAttachmentNotification]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((1)) FOR [SendReassignNotification]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [AddEmailMetadataToComment]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [AddEmailAttachmentToDocument]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((1)) FOR [SendCodingNotification]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((1)) FOR [SendApprovalNotification]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [ManualSeperationEnabled]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((2)) FOR [WizardStatus]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [CreateDefaultLine]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [IsGRN]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [IsPO]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [POMatchingEnabled]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [RequestParkReason]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [RequestDeleteReason]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [RequestReverifyReason]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [IsManualMonitor]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [AutomaticCodingEnabled]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [DisplayLineTotals]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [hasRelatedDocuments]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [CopyPOLinesInVerification]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [IsBudget]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [HasBudget]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [POLineMatching]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [IsSupplierReconciliation]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [SendDocumentRejectedNotification]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [GenerateOnTheFly]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [RequestRecreateReason]
GO

ALTER TABLE [dbo].[DocumentType] ADD  DEFAULT ((0)) FOR [POOriginatorAccessEnabled]
GO

XXX_table 之一的表定義

CREATE TABLE [dbo].[Doc_IE10_POInvoices](
   [Id] [INT] IDENTITY(1,1) NOT NULL,
   [IsActive] [BIT] NOT NULL,
   [CreatedOn] [DATETIME] NOT NULL,
   [LastUpdated] [DATETIME] NOT NULL,
   [Supplier_Name] [NVARCHAR](MAX) NULL,
   [Supplier_Code] [NVARCHAR](MAX) NULL,
   [Invoice_Number] [NVARCHAR](MAX) NULL,
   [Debit_Credit] [NVARCHAR](MAX) NULL,
   [PO_NUMBER] [NVARCHAR](MAX) NULL,
   [Invoice_Date] [DATETIME] NULL,
   [Currency] [NVARCHAR](MAX) NULL,
   [Net_Total] [DECIMAL](18, 4) NULL,
   [Tax_Total] [DECIMAL](18, 4) NULL,
   [Invoice_Total] [DECIMAL](18, 4) NULL,
   [Template_Type] [NVARCHAR](MAX) NULL,
   [SAP_Reference] [NVARCHAR](MAX) NULL,
   [SAP_PaymentDueDate] [DATETIME] NULL,
   [SAP_Status] [NVARCHAR](MAX) NULL,
   [Description] [NVARCHAR](MAX) NULL,
   [Company_Code] [NVARCHAR](MAX) NULL,
   [Debit_Credit_Type] [NVARCHAR](MAX) NULL,
   [ParkReason] [NVARCHAR](MAX) NULL,
   [PaymentTermsDate] [DATETIME] NULL,
   [Invoice_Comment] [NVARCHAR](MAX) NULL,
   [Original_PO_NUMBER] [NVARCHAR](MAX) NULL,
CONSTRAINT [PK_Doc_IE10_POInvoices_636945689932953689] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

XXX_實際上'Doc_'只是。我不好讓它更混亂。以下是使用循環的查詢之一。

ALTER PROCEDURE [dbo].[GetAllPONumberInInvoices_OLD] @invoice_table   NVARCHAR(MAX) = NULL
AS
    BEGIN
        
       IF OBJECT_ID('tempdb..#PONumbersInInvoices') IS NOT NULL
           DROP TABLE #PONumbersInInvoices;

       CREATE TABLE #PONumbersInInvoices
       (
           ID INT,
           PO_NUMBER NVARCHAR(MAX),
           Supplier_Name       NVARCHAR(MAX),
           Supplier_Code       NVARCHAR(MAX),
           Net_Total           decimal(18,4),
           Tax_Total           decimal(18,4)
       )

       IF OBJECT_ID('tempdb..#MultiPONumbersInInvoices') IS NOT NULL
           DROP TABLE #MultiPONumbersInInvoices;

       CREATE TABLE #MultiPONumbersInInvoices
       (
           ID INT,
           PO_NUMBER NVARCHAR(MAX),
           Supplier_Name       NVARCHAR(MAX),
           Supplier_Code       NVARCHAR(MAX),
           Net_Total           decimal(18,4),
           Tax_Total           decimal(18,4)
       )

       DECLARE @QueryToBeExecuted NVARCHAR(MAX)

       SET @QueryToBeExecuted = '
           INSERT INTO #PONumbersInInvoices
           SELECT  [Id],[PO_NUMBER],[Supplier_Name], [Supplier_Code], [Net_Total], [Tax_Total]
           FROM ' + @invoice_table + '
           WHERE [PO_NUMBER] NOT LIKE ''%,%'' AND IsActive = 1'

       EXECUTE sp_executesql @QueryToBeExecuted
           
       SET @QueryToBeExecuted = '
           INSERT INTO #MultiPONumbersInInvoices
           SELECT  [Id],[PO_NUMBER], [Supplier_Name], [Supplier_Code], [Net_Total], [Tax_Total]
           FROM ' + @invoice_table + '
           WHERE [PO_NUMBER] LIKE ''%,%'' AND IsActive = 1'

       EXECUTE sp_executesql @QueryToBeExecuted

       DECLARE @MultiPOIndex INT= 0;
       DECLARE @MultiPOCount INT;

       SELECT @MultiPOCount = Count(*) FROM #MultiPONumbersInInvoices;

       WHILE(@MultiPOIndex < @MultiPOCount)
       BEGIN
           DECLARE @InvoiceId Int;
           DECLARE @PONumber NVARCHAR(MAX)
           DECLARE @Supplier_Name NVARCHAR(MAX)
           DECLARE @Supplier_Code NVARCHAR(MAX)
           DECLARE @Net_Total decimal(18,4)
           DECLARE @Tax_Total decimal(18,4)

           SELECT TOP(1) @InvoiceId = Id, @PONumber = PO_NUMBER, @Supplier_Name = Supplier_Name, @Supplier_Code = Supplier_Code, @Net_Total = Net_Total, @Tax_Total = Tax_Total FROM #MultiPONumbersInInvoices
   
           INSERT INTO #PONumbersInInvoices
           SELECT @InvoiceId AS Id, [Value] AS PO_NUMBER, @Supplier_Name AS Supplier_Name, @Supplier_Code AS Supplier_Code, @Net_Total AS Net_Total, @Tax_Total AS Tax_Total
           FROM dbo.Split(@PONumber,',')

           DELETE FROM #MultiPONumbersInInvoices
           WHERE Id = @InvoiceId;

           SET @MultiPOIndex += 1;
       END


       SELECT * FROM #PONumbersInInvoices

       IF OBJECT_ID('tempdb..#PONumbersInInvoices') IS NOT NULL
           DROP TABLE #PONumbersInInvoices;
       IF OBJECT_ID('tempdb..#MultiPONumbersInInvoices') IS NOT NULL
           DROP TABLE #MultiPONumbersInInvoices;
    END;

然後@invoice_table 由另一個過程提供，該過程執行以下操作

SELECT Id,
               'Doc_'+Name AS TableName
        INTO ##DocumentTypes_with_POMatchingEnabled
        FROM DocType
        WHERE(POMatchingEnabled = 1)
             AND IsActive = 1 and IsAP=1;

WHILE(@Index < @DocumentTypes_with_POMatchingEnabledCount)
            BEGIN
        
                DECLARE @TableName NVARCHAR(150), @PoTable NVARCHAR(MAX), @GrnTable NVARCHAR(MAX), @PO_NUMBER NVARCHAR(MAX);
                DECLARE @DocumentTypeId INT, @DocumentRecordId INT, @DocumentTypePoId INT, @DocumentTypeGrnId INT, @DocumentId INT;
                SELECT TOP (1) @TableName = TableName,
                               @DocumentTypeId = Id
                FROM ##DocumentTypes_with_POMatchingEnabled;

                -- GET PO NUMBERS IN INVOICES
                IF OBJECT_ID('tempdb..#PONumbersInInvoicesTemp') IS NOT NULL
                    DROP TABLE #PONumbersInInvoicesTemp;
                CREATE TABLE #PONumbersInInvoicesTemp
                (ID            INT,
                 PO_NUMBER     NVARCHAR(MAX),
                 Supplier_Name NVARCHAR(MAX),
                 Supplier_Code NVARCHAR(MAX),
                 Net_Total     DECIMAL(18, 4),
                 Tax_Total     DECIMAL(18, 4)
                );
                INSERT INTO #PONumbersInInvoicesTemp
                EXEC GetAllPONumberInInvoices
                     @invoice_table = @TableName;

                -- GET DOCUMENT TYPE ID OF PO 
                SELECT @DocumentTypePoId = dt.Id,
                       @PoTable = 'Doc_'+dt.Name
                FROM DocumentTypeAssociation dta
                     INNER JOIN DocumentType dt ON dt.Id = dta.DocumentTypeId2
                WHERE DocumentTypeId1 = @DocumentTypeId
                      AND dt.IsPO = 1;

                -- GET DOCUMENT TYPE ID OF GRN
                SELECT @DocumentTypeGrnId = dt.Id,
                       @GrnTable = 'Doc_'+dt.Name
                FROM DocumentTypeAssociation dta
                     INNER JOIN DocumentType dt ON dt.Id = dta.DocumentTypeId2
                WHERE DocumentTypeId1 = @DocumentTypeId
                      AND dt.IsGRN = 1;
                DECLARE @SqlStatement NVARCHAR(MAX);
            
                SELECT @SqlStatement = ('
           INSERT INTO ##InvoicesWithPoAndWithoutGrnReportResults 
           SELECT docPO.Id AS DocumentId, 
           docInvoice.PO_NUMBER AS PO_NUMBER, 
           '''+@TableName+''' AS INVOICE_TABLE, 
           '''+@PoTable+''' AS PO_TABLE, 
           '''+@GrnTable+''' AS GRN_TABLE, 
           docInvoice.Supplier_Name, 
           docInvoice.Supplier_Code,
           CASE
                   WHEN docPO.Originator LIKE ''%_@__%.__%'' 
                   THEN docPO.Originator 
                   WHEN '+CONVERT(NVARCHAR, @userMappingTableId)+' > 0 
                   THEN ( SELECT TOP 1 [Value] 
                       FROM LookupDataCustom 
                       WHERE DocumentTypeId = '+CONVERT(NVARCHAR, @DocumentTypeId)+' 
                           AND IsActive = 1 
                           AND LookupDataTableId = '+CONVERT(NVARCHAR, @userMappingTableId)+' 
                           AND [Value] LIKE ''%_@__%.__%'' 
                           AND [Name] = docPO.Originator) 
                   ELSE '''' END AS Originator,
           case when DocumentRecord.Status =5
           then
           '''+@loginUrl+'Document/Verify/''+CONVERT(NVARCHAR, DocumentRecord.Id) else
           '''+@loginUrl+'Document/View/''+CONVERT(NVARCHAR, DocumentRecord.Id)
           end,
           docInvoice.Net_Total, 
           docInvoice.Tax_Total
           FROM #PONumbersInInvoicesTemp docInvoice 
           inner join DocumentRecord on DocumentRecord.DocId=docInvoice.Id and DocumentRecord.DocumentTypeId='+CONVERT(NVARCHAR, @DocumentTypeId)+'
           INNER JOIN '+@PoTable+' docPO ON docPO.PO_NUMBER = docInvoice.PO_NUMBER and docPO.Supplier_Code = docInvoice.Supplier_Code and docPO.IsActive = 1
           LEFT JOIN '+@GrnTable+' docGRN ON docGRN.PO_NUMBER = docInvoice.PO_NUMBER and docGRN.Supplier_Code = docInvoice.Supplier_Code and docGRN.IsActive = 1
           WHERE  docGRN.Id IS NULL
           and DocumentRecord.Status in (5,24,28)      
           ' + @grnNotificationDelay + '
           Order by docPO.Originator,docInvoice.Supplier_Name;
           ');
                EXEC sp_executesql
                     @SqlStatement;
                DELETE FROM ##DocumentTypes_with_POMatchingEnabled
                WHERE Id = @DocumentTypeId;
                SELECT @Index = (@Index + 1);
            END;

沒有分享整個查詢，因為它可能更令人困惑。

請在您的文章中提供您目前使用的查詢範例以及您的表定義。就目前而言，您的問題不是很清楚，尤其是關於循環在此處甚至是如何相關的。

話雖如此，根據您提供的目前資訊，聽起來你們可能會通過利用系統儲存過程從動態 SQLsp_executesql中受益。使用動態 SQL，您可以根據儲存在表中的表名建構要執行的查詢的 SQL 字元串DocType。然後你可以用sp_executesql.

您還可以使用系統視圖sys.tables作為獲取“XXX”前綴表的實際表名列表的一種方式。從技術上講，您可以在它和您的DocType表之間進行萬用字元連接，以幫助建構動態 SQL，如下所示：

SELECT T.[name]
FROM sys.tables AS T
INNER JOIN DocType AS D
   ON T.[name] LIKE '%' + D.[Name]

這是假設當您說“XXX”時，您在問題中使用它作為佔位符，但實際上它是一組不同的值。但是，如果您的意思是它們確實都以三個 X 開頭，那麼您甚至不需要上述查詢。DocType相反，您可以在從表中選擇時自己添加“XXX”，如下所示：

SELECT 'XXX_' + [Name]
FROM DocType

如果確實有兩個名稱都帶有下劃線前綴而其他名稱帶有破折號，則可以將上述兩個查詢組合起來，以確保僅使用 a 選擇有效的表名，UNION ALL如下所示：

SELECT 'XXX_' + D.[Name]
FROM DocType AS D
INNER JOIN sys.tables AS T
   ON 'XXX_' + D.[Name] = T.[name]

UNION ALL

SELECT 'XXX-' + D.[Name]
FROM DocType AS D
INNER JOIN sys.tables AS T
   ON 'XXX-' + D.[Name] = T.[name]

如果您像我最初提到的那樣更新您的文章，我們可能會為您提供更具體的建議。

引用自：https://dba.stackexchange.com/questions/301446

相關問答

Sql-Server

如何編寫選擇語句，根據partid 從最後一個日期到目前日期獲取數據並填補空白？

  • March 30, 2022
檢視問答
Sql-Server

即使使用非聚集索引和全文索引，SE LECT 查詢也很慢

  • February 21, 2022
檢視問答
Sql-Server

如果文件位於 RAID 上，我應該對數據庫文件進行分區以提高性能嗎？

  • January 22, 2022
檢視問答
Sql-Server

儲存過程中的局部變數

  • December 9, 2021
檢視問答
Sql-Server

SQL案例問題

  • October 4, 2021
檢視問答
Sql-Server

保留每個表中每個值的完整歷史記錄

  • September 12, 2021
檢視問答