AlterEgo
3/26/2007 11:16:00 PM
iSiteID, iCustomerID, iIndividualID are all candidates for indexing.
-- Bill
<msnews.microsoft.com> wrote in message
news:uhd1ja$bHHA.3648@TK2MSFTNGP05.phx.gbl...
>I have stored procedure that is doing alot read high I/O, it is doing
>clustered index scan on the Order Summary table.
> here is the text of the SP. Can you please help identify what index I need
> to add or change the way the stored proceure are writen.
> Thanks in Advance!
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> create proc eTABgLinkedListCust_JDE
> @SiteId int,
> @LanguageCode varchar(10),
> @CustomerId int,
> @AssignedId varchar(20) = null
>
> as
> /*
> ** ObjectName: eTABgLinkedListCust_JDE
> **
> ** Description: Proc to bring back order summary data for customers
> (Individuals or compaies)
> ** and populates the JDE Data tab.
> **
> ** Table JDEOrderSummary
> **
> ** Revision History:
> ** --------------------------------------------------------------------------------------
> ** Date Name Description
> ** --------------------------------------------------------------------------------------
> ** 09/09/00 J. Aasheim Initial Creation.
> */
>
> BEGIN
>
> SET NOCOUNT ON
>
> Select
> iOrderNum,
> chRefOrderNum,
> iIncidentId,
> dtOrderDate,
> chOrderType,
> chOrderStatus,
> chCustType,
> chProjectTitle,
> vchSubject,
> chPlatform,
> iImageQuantity,
> iDigitalQuantity,
> iAnalogQuantity,
> vchCurrencyCode,
> vchRevenue,
> vchCredits,
> vchInvoiceNum,
> iInvoicedQuantity,
> iDigInvoicedQuantity,
> iAnaInvoicedQuantity,
> dtLastInvoiced,
> chOffice,
> chAcctExec,
> chResearcher
>
> From JDEOrderSummary
>
> Where @SiteId = iSiteId
> and @CustomerId in (iIndividualId, iCompanyId)
>
> Return -300201015
>
> End
> The table that the SP is going against is below
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[JDEOrderSummary]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[JDEOrderSummary]
> GO
>
> CREATE TABLE [dbo].[JDEOrderSummary] (
> [iOrderSumId] [int] NOT NULL ,
> [iSiteId] [int] NOT NULL ,
> [iCompanyId] [int] NULL ,
> [iIndividualId] [int] NULL ,
> [iOrderNum] [int] NULL ,
> [chRefOrderNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [iIncidentId] [int] NULL ,
> [dtOrderDate] [datetime] NULL ,
> [chOrderType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [chOrderStatus] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [chCustType] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [chProjectTitle] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [vchSubject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [chPlatform] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [iImageQuantity] [int] NULL ,
> [iDigitalQuantity] [int] NULL ,
> [iAnalogQuantity] [int] NULL ,
> [vchCurrencyCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
> ,
> [vchRevenue] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [vchCredits] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [vchInvoiceNum] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
> ,
> [iInvoicedQuantity] [int] NULL ,
> [iDigInvoicedQuantity] [int] NULL ,
> [iAnaInvoicedQuantity] [int] NULL ,
> [dtLastInvoiced] [datetime] NULL ,
> [chOffice] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [chAcctExec] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [chResearcher] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [dtInsertDate] [datetime] NULL ,
> [tiRecordstatus] [tinyint] NULL
> ) ON [PRIMARY]
> GO
>
>
>
>