Sorry I use a GUI to feed the Proc.

I use GUI to run the wizard...

Yes I ran the Index Tuning Wizard which recommended a clustered Index on ATTRIBUTE_ID

My PROC is...

CREATE PROC USP_CL_ATTRIB_U

(@PROD_ATTRIBUTE_IDBIGINT,

@AttributeidSMALLINT,

@CrnIDVARCHAR(40),

@dataVARCHAR(250),

@RetValTINYINT = 0 OUT)

AS

DECLARE

--@CustProdIDBIGINT,

@CountSMALLINT

@isduplallowchar(1)

BEGIN

SET @RetVal = 0

SET @isdupallow ='N'

select @isdupallow = IS_DUP_ALLOW

FROM SU_ATTRIBUTE_DEFINITION

WHERE ATTRIBUTE_ID = @Attributeid

IF @isdupallow = 'N'

BEGIN

SELECT...

We are indeed checking for duplicates one row at a time though it is conditional. I'm using it at the time of Updates only. The actual code is called through...

John,

We are using this select statement each time we insert a attribute for which data duplicates are not allowed. We have a Attribute Definition table which has the rules...

John,

I do a select something like this

select count(1)

from CL_PRODUCT_ATTRIBUTE

where ATRRIBUTE_ID = @attrib_id

and data = @data;

to check the duplicates. Having indexes on ATRRIBUTE_ID didn't help boost performance.

John,

The only combination which is unique is CUST_PRODUCT_ID, ATTRIBUTE_ID and DATA

Creating an Index(we cannot have a Unqiue Key) on ATTRIBUTE_ID didn't help us boost the performance. Neither did it creating...

John,

The Table is

CREATE TABLE [CL_PRODUCT_ATTRIBUTE] (

[PROD_ATTRIBUTE_ID] [bigint] IDENTITY (1, 1) NOT NULL ,

[ITEM_ATTRIBUTE_ID] [bigint] NOT NULL ,

[CUST_PRODUCT_ID] [bigint] NOT NULL ,

[ATTRIBUTE_ID] [bigint] NOT NULL ,

[DATA] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS...

This is precisely what I was looking in for.

thanks

Rajesh

Hi,

Thanks, I tried it and it worked as desired

thanks for the quick help

Rajesh