need alternative - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need alternative

Author

Topic

querybest
Starting Member

22 Posts

Posted - 2005-08-13 : 02:56:56

Hi.

I use a procedure like below

if not exists (select * from tbl where date=@getdate)
begin
	insert tbl (date,click)
	values (@getdate, 1,)
end 

	update	tbl 
	set	views=views+1
	where	date=@getdate

now, in a day this procedure is called more than 70.000 times.

"if not exists (select * from tbl where date=@getdate)"
this is deathly..

it is needed to add new one only one time in a day, it is checked 70.000 time in a day..

whay can I do ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-13 : 08:20:57

How about, just perform the update then check @@rowcount. If it's zero, perform the insert.

EDIT:
btw, with proper indexing this query should be ultra fast. You may be having contention issues. if you're already logging usage in some more detailed way, you could use that table to count views/day. If you don't require any usage statistics like that then perhaps instead of updating views for a "day" row, you just insert a datetime value to a table. Then either use that table to aggregate by day (using nolock hint when reading), or periodically, update a summary table based on the aggregate count by day and read from the summary table.

Be One with the Optimizer
TG

querybest
Starting Member

22 Posts

Posted - 2005-08-13 : 09:21:57

I did a temp db for it.

in it, I only insert..

once 2 hours, I aggregate them automatickly by using "group by"

by there were some problems about it.. my application is so complex ..in this respect I am not using this way for now..

I must re-code it..

until it this code is fine ;)

if not exists (select * from tbl where date=@getdate)
begin
insert tbl (date,click,views)
values (@getdate, 1,1)
end
else
begin
update tbl
set views=views+1
where date=@getdate
end

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-13 : 09:40:23

Well, at the very least my original suggestion should be an improvement:


update	tbl 
set	views=views+1
where	date=@getdate

if @@rowcount = 0
begin
	insert tbl (date,click)
	values (@getdate, 1,)
end

Be One with the Optimizer
TG

querybest
Starting Member

22 Posts

Posted - 2005-08-13 : 10:13:33

hmmm, thank you very much..

if rowcount is 0, doesnt "update tbl set ...." cause any error, it does ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-13 : 12:24:09

nope, it won't error. Try a little test to see for yourself.

Be One with the Optimizer
TG

querybest
Starting Member

22 Posts

Posted - 2005-08-16 : 06:40:29

TG thank you very very much..

it works :)
I am felling power :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources