Primary key error Vs select Command

i am creating a application as URL Counter. i have created a table to store url and its count.

CREATE TABLE [dbo].[tblurlcounter](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [type] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [count] [bigint] NULL,
 CONSTRAINT [PK_tblurlcounter] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

i have created a stored procedure to insert/update url in table. means when ever a value "URL" will be inserted in that table using stored procedure then i check that if its exists then update its count and if not then insert it in table with count=1.

my stored procedure is like:

declare @count int

select @count= [count] from tblurlcounter where [type] = @type
if @count > 0
begin
update tblurlcounter set [count]=@count + 1 where [t开发者_开发百科ype] = @type
select @count + 1

end
else 
begin
INSERT INTO [dbcounter].[dbo].[tblurlcounter]
           ([type]
           ,[count])
     VALUES
           (@type
           ,1)
end

this application will get around 80,000 to 100,000 hits in a minutes. so i want that my stored procedure should perform operation with good speed. i mean my solution should be optimized.

some one suggested me to change in my table and create its filed named "type" (which i am using to store url) as a primary key and in stored procedure i should first try to insert record and if its throw an error then check for error in next line and perform update operation.

so i am confused that which one will be faster, the primary key error apporach or i should go with select query and base on select's result i should perform insert/update operation

Now i need experts advice that, which approach is correct and if any other good approach is available then please suggest me.

thanks

For that load you'll need to be a bit clever. I've posted before on this

Basically, don't test first: try the INSERT. If it fails, run an update

https://stackoverflow.com/search?q=user%3A27535+JFDI

You can just try and do the update, if it doesn't exist no rows will be updated which you can check with @@rowcount. If none are then you can add it otherwise the value is already incremented. You don't need the @count variable as then you would have to lock the row so nothing can change the value after you'd assigned it but before you updated the table.

update tblurlcounter set [count] = [count] + 1 where [type] = @type

if @@rowcount = 0 
begin
  insert into tblurlcounter 
  ([type],[count])
  values
  (@type, 1)
end

Your approach will not work properly as you're executing multiple statements. Ie, between this line happening :

select @count = [count] from tblurlcounter where [type] = @type

and either your INSERT or UPDATE actually executing, a separate execution of the stored procedure could also be adding a row, so you could end up with two INSERTS occurring almost simultaneously.

Instead, try this :

INSERT INTO [dbcounter].[dbo].[tblurlcounter]
           ([type]
           ,[count])
     VALUES
           (@type
           ,0)
WHERE NOT EXISTS(select 1 from tblurlcounter where [type] = @type)

UPDATE tblurlcounter SET [count]=[count] + 1 where [type] = @type

This will add a new row if a matching one isn't already present, combining the INSERT with the existence check. The update statement can be run secure in the knowledge that there is already a row to be updated.

You also really need an index on your Type column.

This article explains very well the if exists update vs the update if not exists insert approach.

Apparently because of table locks, it's less expensive to do a select to check if it exists first. That way we avoid using an update, which would lock far more data than a select.

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

also,

you can use:

if exists (select * from tblurlcounter where [type] = @type)