[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

"Inserted Scan" is costing 99% of my query!

In trying to optimize a rather large query I discovered that one seemingly small part of the query is actually costing 99.19% of the total batch.  It seems so simple, yet its so expensive.  

I'm calling a stored procedure which does an insert into another, which in turn fires a trigger.  Within this query there are three components, a select statement (0%),   computer svalar (0%), and an inserted scan (100%).  It says "scanning the pseudo-table "inserted" within a trigger".  

So whats the deal with this?  Why is this so expensive?  Is there any way to optimize this?
0
collages
Asked:
collages
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
without knowing the relevant table & index structure and the trigger code it will be impossible to tell anything concrete.

Note that 100% of 0.1 second is only 0.1 second, and is not alot of time (you might post the amount of time it takes), so no optimization might be needed.

"scanning pseudo table" means that you use the inserted and/or deleted table.
0
 
collagesAuthor Commented:
When I run the query in SQL Query Analyzer it takes anywhere between 2 and 15 seconds.  However, this stored procedure is called via a web page.  When it is run over the web it can take as long as 90 seconds.  

The trigger is on the following table:

CREATE TABLE [TBL_WEB_ReprintSummary] (
      [UniqueReprintOrder] [int] IDENTITY (1, 1) NOT NULL ,
      [PhotogCode] [int] NOT NULL ,
      [EventCode] [int] NOT NULL ,
      [username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ConsumerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ConsumerInfo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TimeIn] [smalldatetime] NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_TimeIn] DEFAULT (getdate()),
      [TimeOut] [smalldatetime] NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_TimeOut] DEFAULT (getdate()),
      [OrderNumber] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OrderDate] [smalldatetime] NULL ,
      [SalesTax] [decimal](10, 2) NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_SalesTax] DEFAULT (0),
      [Shipping] [decimal](10, 2) NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_Shipping] DEFAULT (0),
      [SubTotal] [decimal](10, 2) NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_SubTotal] DEFAULT (0),
      [Total] [decimal](10, 2) NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_Total] DEFAULT (0),
      [Unsubscribe] [bit] NOT NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_Unsubscribe] DEFAULT (0),
      [ReminderDate] [datetime] NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_ReminderDate] DEFAULT (dateadd(day,1,getdate())),
      [ReminderCount] [tinyint] NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_ReminderCount] DEFAULT (0),
      [FrameTotal] [decimal](10, 2) NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_FrameTotal] DEFAULT (0),
      [PackageTotal] [decimal](10, 2) NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_PackageTotal] DEFAULT (0),
      [PackageYearbookImage] [int] NULL ,
      [AdditionalCharges] [decimal](10, 2) NULL CONSTRAINT [DF_TBL_WEB_ReprintSummary_AdditionalCharges] DEFAULT (0),
      [GiftCertificateTotal] [decimal](10, 2) NULL ,
      [DiscountTotal] [decimal](10, 2) NULL ,
      CONSTRAINT [PK_TBL_WEB_ReprintSummary] PRIMARY KEY  CLUSTERED
      (
            [UniqueReprintOrder]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO


Heres the trigger:

ALTER  TRIGGER TRG_CalcReprintSummaryTotalOnInsert ON TBL_WEB_ReprintSummary
FOR UPDATE
AS

IF @@ROWCOUNT > 1 RETURN
IF UPDATE(Total) RETURN

IF UPDATE(SalesTax) OR UPDATE(Shipping) OR UPDATE(SubTotal) OR UPDATE(PackageTotal) OR UPDATE(AdditionalCharges)
BEGIN
   DECLARE @UniqueReprintOrder int
   DECLARE @Total decimal(10,2), @SalesTax decimal(10,2), @HJT decimal(10,2), @Shipping decimal(10,2), @PackageTotal decimal(10,2), @AdditionalCharges decimal(10,2)

   --SELECT @UniqueReprintOrder = UniqueReprintOrder, @Total = (ISNULL(SalesTax,0) + ISNULL(Shipping,0) + ISNULL(SubTotal,0)) FROM Inserted
   SELECT @UniqueReprintOrder = UniqueReprintOrder, @Shipping = Shipping, @PackageTotal = ISNULL(PackageTotal,0), @AdditionalCharges = ISNULL(AdditionalCharges,0), @SalesTax = SalesTax, @Total = (ISNULL(Shipping,0) + ISNULL(SubTotal,0) + ISNULL(PackageTotal,0) + ISNULL(AdditionalCharges,0)), @HJT = (ISNULL(PackageTotal,0)+ISNULL(AdditionalCharges,0)) FROM Inserted

   IF @HJT= 0 AND @SalesTax > 0
   BEGIN
      --SELECT @SalesTax = @Total * 0.06, @Total = @Total * 1.06
      SELECT @SalesTax = SUM(CAST(Total*0.06 AS decimal(10,2))) + (@Shipping*.06) + (@PackageTotal*.06) + (@AdditionalCharges*.06) FROM [Consumer Site]..TBL_WEB_ReprintDetails WHERE UniqueReprintOrder = @UniqueReprintOrder
      SET @Total = @Total + @SalesTax
   END

   IF @HerffTotal > 0
      SET @Total = @Total + @SalesTax

   UPDATE TBL_WEB_ReprintSummary SET SalesTax = @SalesTax, Total = @Total WHERE UniqueReprintOrder = @UniqueReprintOrder
END


I think the statement thats causing the problem is:

SELECT @UniqueReprintOrder = UniqueReprintOrder, @Shipping = Shipping, @PackageTotal = ISNULL(PackageTotal,0), @AdditionalCharges = ISNULL(AdditionalCharges,0), @SalesTax = SalesTax, @Total = (ISNULL(Shipping,0) + ISNULL(SubTotal,0) + ISNULL(PackageTotal,0) + ISNULL(AdditionalCharges,0)), @HJT = (ISNULL(PackageTotal,0)+ISNULL(AdditionalCharges,0)) FROM Inserted


But like i said before, this query is not a problem when I run it in query analyzer, only when it is run over the web.  I guess that adds tons of other variables as well, but I'm trying to eliminate this trigger first before I start looking elsewhere.

Thanks again.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have an index on the field UniqueReprintOrder of table TBL_WEB_ReprintSummary?


0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
collagesAuthor Commented:
I have the following indes on TBL_WEB_ReprintSummary:

 CREATE  INDEX [IDX_OrderNumber_TBL_WEB_ReprintSummary] ON [dbo].[TBL_WEB_ReprintSummary]([OrderNumber]) ON [PRIMARY]
GO

 CREATE  INDEX [IDX_EventCode_TBL_WEB_ReprintSummary] ON [dbo].[TBL_WEB_ReprintSummary]([EventCode]) ON [PRIMARY]
GO

 CREATE  INDEX [IDX_ConsumerEmail_TBL_WEB_ReprintSummary] ON [dbo].[TBL_WEB_ReprintSummary]([ConsumerEmail]) ON [PRIMARY]
GO

 CREATE  INDEX [IDX_ReminderDate_TBL_WEB_ReprintSummary] ON [dbo].[TBL_WEB_ReprintSummary]([ReminderDate]) ON [PRIMARY]
GO

 CREATE  INDEX [TBL_WEB_ReprintSummary5] ON [dbo].[TBL_WEB_ReprintSummary]([OrderNumber], [UniqueReprintOrder], [PhotogCode], [EventCode], [username], [ConsumerEmail], [ReminderDate], [ReminderCount]) ON [PRIMARY]
GO

 CREATE  INDEX [IDX_TimeIn_TBL_WEB_ReprintSummary] ON [dbo].[TBL_WEB_ReprintSummary]([TimeIn]) ON [PRIMARY]
GO

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
looking at the following 2 indexes:
CREATE  INDEX [IDX_OrderNumber_TBL_WEB_ReprintSummary] ON [dbo].[TBL_WEB_ReprintSummary]([OrderNumber]) ON [PRIMARY]

 CREATE  INDEX [TBL_WEB_ReprintSummary5] ON [dbo].[TBL_WEB_ReprintSummary]([OrderNumber], [UniqueReprintOrder], [PhotogCode], [EventCode], [username], [ConsumerEmail], [ReminderDate], [ReminderCount]) ON [PRIMARY]

the first one does not make any sense, at it is covered by the second one.
now, in the second one, the field UniqueReprintOrder is part of the index, but only second in the list.

please change the second index to this:

DROP INDEX [TBL_WEB_ReprintSummary5]

CREATE  INDEX [TBL_WEB_ReprintSummary5] ON [dbo].[TBL_WEB_ReprintSummary]( [UniqueReprintOrder], [OrderNumber],[PhotogCode], [EventCode], [username], [ConsumerEmail], [ReminderDate], [ReminderCount]) ON [PRIMARY]

and try to run the query again, it should run faster now.
0
 
collagesAuthor Commented:
Ok, I'll make that change and post back with my results.  

Would you recommend dropping the first index that "does not make sense"?

We also have one of our developers looking into the SQL connection pool, which we think could be another source of this problem.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Would you recommend dropping the first index that "does not make sense"?
no. by implementing the index change, the first one will get meaningful.
0
 
collagesAuthor Commented:
Ok, i made that change to the index and the Query is running faster (in SQL query analyzer at least).  I ran it 20 times and it takes between 1 and 4 seconds...before it was taking between 2 and 15 seconds.  Its still slow when called through a webpage, so were looking into that.

I'm really just starting to learn about index optimization....why did this change make such a big improvement?

I may be posting a follow to this question once we get some additional information as the problem is still going on (although at this point I think we've ruled out the query itself).


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
because the field we put now first in the index is the only one in the where condition, and for the index to be usable, the fields in the where condition need to be first in the index for optimal performance.

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now