深圳智汇创想科技有限公司

1.问题描述

慢SQL   大表查询RB_Ebay_Account_Fee,条件查询多次in 查询，导致查询慢

2.问题分析

in 查询  使索引失效，导致慢

3.解决方案

CustomerID IN ( 1 ) 改为：CustomerID =1

  OrderSourceID，ItemID  若是传单个参数进来用=，若是多个少量用in 换exists，若是大量则用表值参数或零时表join

4  慢脚本：

   SELECT [ID],
       [CustomerID],
       [OrderSourceID],
       [AccountDetailsEntryType],
       [BalanceCurrencyID],
       [BalanceValue],
       [ConversionRateCurrencyID],
       [ConversionRateValue],
       [date],
       [Description],
       [GrossDetailAmountCurrencyID],
       [GrossDetailAmountValue],
       [ItemID],
       [Memo],
       [NetDetailAmountCurrencyID],
       [NetDetailAmountValue],
       [OrderLineItemID],
       [ReceivedTopRatedDiscount],
       [RefNumber],
       [Title],
       [TransactionID],
       [VATPercent],
       [AccountState],
       [AmountPastDueCurrencyID],
       [AmountPastDueValue],
       [BankAccountInfo],
       [BankModifyDate],
       [BillingCycleDate],
       [CreditCardExpiration],
       [CreditCardInfo],
       [CreditCardModifyDate],
       [CurrentBalanceCurrencyID],
       [CurrentBalanceValue],
       [InvoiceBalanceCurrencyID],
       [InvoiceBalanceValue],
       [InvoiceCreditCurrencyID],
       [InvoiceCreditValue],
       [InvoiceDate],
       [InvoiceNewFeeCurrencyID],
       [InvoiceNewFeeValue],
       [InvoicePaymentCurrencyID],
       [InvoicePaymentValue],
       [LastAmountPaidCurrencyID],
       [LastAmountPaidValue],
       [LastPaymentDate],
       [PastDue],
       [PaymentMethod],
       [AddTime],
       [IsBuildFee],
       [BuildFeeTime],
       [AccountID],
       [ComeSource],
       [OrderId],
       [PayoutId],
       [BookingEntry]
FROM   RB_Ebay_Account_Fee (nolock)
WHERE  1 = 1
       AND CustomerID IN ( 1 )
       AND OrderSourceID IN ( 6768 )
       AND ItemID IN ( '273406654354' )
       AND Isnull(TransactionID, '') != ''