[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Stored procedure

<msnews.microsoft.com>

3/26/2007 9:51:00 PM

I have stored procedure that is when executed , it does Clustered Index Scan
on he below table with alot of read . the Table JDEOrderSummary has over 3
Million record. Here is the DLL for the table . Can any one help me identify
what column should be indexed to be able do clusterd index seek and avoid
High I/O
Please help!


exec eTABgLinkedListCust_JDE 1, 'ENG ', 1859048



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
2 Answers

Erland Sommarskog

3/26/2007 10:21:00 PM

0

(msnews.microsoft.com) writes:
> I have stored procedure that is when executed , it does Clustered Index
> Scan on he below table with alot of read . the Table JDEOrderSummary has
> over 3 Million record. Here is the DLL for the table . Can any one help
> me identify what column should be indexed to be able do clusterd index
> seek and avoid High I/O

I think that's a pretty tough challenge you are giving us. All we have is a
table definition. How should be able to suggest an index for your procedure
when we don't see the code for it?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

Alejandro Mesa

3/28/2007 2:01:00 AM

0

- Can you post the query you are talking about and the execution plan info?
- Can you provide info about constraints and actual indexes?

AMB


"msnews.microsoft.com" wrote:

> I have stored procedure that is when executed , it does Clustered Index Scan
> on he below table with alot of read . the Table JDEOrderSummary has over 3
> Million record. Here is the DLL for the table . Can any one help me identify
> what column should be indexed to be able do clusterd index seek and avoid
> High I/O
> Please help!
>
>
> exec eTABgLinkedListCust_JDE 1, 'ENG ', 1859048
>
>
>
> 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
>
>
>