Query/proc optimization - 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

Transact-SQL (2000)

Query/proc optimization

Author

Topic

gmerideth
Starting Member

10 Posts

Posted - 2005-01-08 : 23:40:32

After spending an hour to get this to work, I would like to know if what I've developed as a procedure is a good approach or can it be improved.

I had a need to check how often, in minutes, a particular ip connection was being made. The code was originally in asp.net and it just made sense to move this to sql. The procedure works, I just want to know if it can be optimized.

The query looks for an IP already in the table ftIPAccess, if the record does not yet exist, it adds it in and sets the timespan to 99 minutes. If the record is found, it will update the last access time with getdate() and return the time between the *last* time it was accessed and the getdate() time.

PROCEDURE ftTimestampIPAddress
( @pIPAddress varchar(16) ) AS
SET NOCOUNT ON
DECLARE @timespan numeric
IF (SELECT @pIPAddress from ftIPAccess WHERE IPAddress=@pIPAddress) = @pIPAddress
BEGIN
SET @timespan = DateDiff(minute,(select lastaccess from ftipaccess where
IPAddress=@pIPAddress), getdate())
UPDATE ftIPAccess SET LastAccess=getdate() WHERE ipaddress=@pIPAddress
SELECT @timespan AS '@@timespan'
RETURN 1
END
ELSE
BEGIN
INSERT INTO ftipaccess (ipaddress,ipstatus,lastaccess) VALUES (@pIPAddress,1,getdate())
SET @timespan = 99
SELECT @timespan AS '@@timespan'
RETURN 0
END

Can this be improved or are there some t-sql features that would stream this?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-09 : 02:56:28

An output parameter will be faster than a resultset and you can reduce the number of statements.

create PROCEDURE ftTimestampIPAddress
@pIPAddress varchar(16) ,
@timespan int output
AS
set nocount on

UPDATE ftIPAccess
SET LastAccess=getdate() ,
@timespan = DateDiff(mi,lastaccess, getdate())
WHERE ipaddress=@pIPAddress

if @@rowcount = 0
begin
INSERT INTO ftipaccess (ipaddress,ipstatus,lastaccess)
select @pIPAddress,1,getdate()
SET @timespan = 99
return 0
end
return 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-09 : 09:45:03

One more suggestion: Personally, I avoid setting the RETURN value, as SQL Server uses it for errors and I want to know that every value in or out of a module has exactly one meaning... So I would use an output parameter for that, as well:

create PROCEDURE ftTimestampIPAddress
@pIPAddress varchar(16) ,
@timespan int output,
@RowsUpdated int output
AS
set nocount on

UPDATE ftIPAccess
SET LastAccess=getdate() ,
@timespan = DateDiff(mi,lastaccess, getdate())
WHERE ipaddress=@pIPAddress

SET @RowsUpdated = @@ROWCOUNT

if @RowsUpdated = 0
begin
INSERT INTO ftipaccess (ipaddress,ipstatus,lastaccess)
select @pIPAddress,1,getdate()
SET @timespan = 99
end

gmerideth
Starting Member

10 Posts

Posted - 2005-01-09 : 14:10:45

Thank you. I thought performing an update with a missing record would have caused an error in the procedure. Always good to learn something new.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources