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

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.

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.

do you have an index on the field UniqueReprintOrder of table TBL_WEB_ReprintSummary?

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

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.

>Would you recommend dropping the first index that "does not make sense"?
no. by implementing the index change, the first one will get meaningful.

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).

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.