1

I have a query that updates a temp table. There are 2 Filtered indexes (by date) used in this query. However, the update take 8-10 mins to run. I am struggling to find out why it is so slow. There is no blocking or locking when the query is being run.

I have also rebuilt the indexes.

Anything else I should check? Thanks.

DECLARE @PDate DateTime = '2014/12/16'  

CREATE TABLE #UserData
(
    UniqueID int,
    Broker nvarchar(80),
    Account nvarchar(50),
    Currency nvarchar(5),
    InvestmentID nvarchar(50),
    Description nvarchar(300),
    Quantity numeric(38,2),
    TFactor numeric(38,2),
    Price numeric(38,7),
    CurrentPrice numeric(38,7),
    PFactor numeric(38,2),
    Cost numeric(38,2),
    TradeAction nchar(10),
    TradeID nvarchar(50),
    Commission numeric(38,2)
)

UPDATE #UserData
    SET CurrentPrice = OtherUser.Price
FROM #UserData BD
INNER JOIN UserDetailsTbl OtherUser WITH(INDEX (IX_UserDetailsTbl_FX1))
       ON OtherUser.InvestmentID = BD.InvestmentID
       AND OtherUser.PDate = @PDate
       AND OtherUser.Factor = BD.TFactor
INNER JOIN UserTransactionsTbl Trans WITH(INDEX ([IX_UserTransactionsTbl_FX1]))
       ON Trans.Account = OtherUser.Account 
       AND Trans.Broker = OtherUser.Broker
       AND Trans.TDate = OtherUser.PDate
       AND Trans.TFactor = BD.TFactor
WHERE BD.CurrentPrice is null
OPTION(RECOMPILE);

INDEXES:

CREATE NONCLUSTERED INDEX [IX_UserDetailsTbl_FX1] ON [dbo].[UserDetailsTbl] 
(
[InvestmentID] ASC,
[PDate] ASC,
[Account] ASC,
[Broker] ASC,
[Factor] ASC
)
INCLUDE ( [Price]) 
WHERE ([PDate]>='2014-12-03' AND [PDate]<='2014-12-27')


CREATE NONCLUSTERED INDEX [IX_UserTransactionsTbl_FX1] ON [dbo].[UserTransactionsTbl] 
(
[Broker] ASC,
[Account] ASC,
[InvestmentID] ASC,
[TDate] ASC,
[TFactor] ASC
)
INCLUDE ( [UniqueCounterPartyID],
[Currency],
[Description],
[Quantity],
[AdjTFactor],
[AdjTradePrice],
[Price],
[PricingFactor],
[Cost],
[TradeAction],
[TradeID],
[Commission]) 
WHERE ([TDate]>='2014-12-03' AND [TDate]<='2014-12-27')
  • Are you forcing indexes the indexes to be used or are you just showing us which one should be used in each case? – Mark Sinkinson Dec 17 '14 at 12:12
  • I m forcing the indexes to be used. – K09 Dec 17 '14 at 12:31
  • 1
    What happens if you do not? Does SQL Server still choose them? If not, what does it use? Does it run any faster without the hints? Can you post the Execution Plan? – Mark Sinkinson Dec 17 '14 at 12:38
  • 1
    Are there any indexes on the #temp table? How many rows are in the #temp table and in the source tables? 5-10 minutes seems excessively long and may not be due to a poor index choice (perhaps there is blocking too). Note that you're forcing your filtered indexes to be used but they may not be the optimal choice (especially since your where clauses don't match the filter definition - I suspect at least part of the reason you need to force them to be used). – Aaron Bertrand Dec 17 '14 at 14:14
  • I removed the forcing of the indexes and now runs in 3 seconds! Why would this be? Without the forcing, there are no Index seeks but clustered index scans. I would have thought the filtered indexes would be quick!? – K09 Dec 17 '14 at 15:19

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.