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)