optimizing query (trying not to use select top 1)

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)

optimizing query (trying not to use select top 1)

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2003-04-03 : 18:27:55

I'm doing a query on a log table that contains the action taken and where the action was taken. For instance, if a record is modified, it inserts into this log table. I have a view that is grabbing the latest update date, but it's quite slow because it's using top 1. Also, the select list is quite large, so doing a group by is even slower then using select top 1. Here is the select statement and the table definitions:



CREATE TABLE UserLog (
	[LogID] int identity,
	[UserID] int,
	[ActionID] int,
	[ActionDt] datetime,
	[LocationID] int
)

CREATE TABLE [User] (
	[UserID] int identity primary key,
	[FirstName] varchar(255),
	[LastName] varchar(255),
	[Deleted] bit
)

select	FirstName,
	LastName,
	UpdateSource = (select top 1 LocationID from UserLog where UserID= u.UserID and ActionID in (1,5) order by ActionDt desc),
	UpdateDt = (select max(ActionDt) from UserLog where UserID= u.UserID and ActionID in (1,5))
from	[User] u

any ideas on optimization?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-03 : 19:24:10

Give use some data (INSERT statements)
More info please, what is the ActionID in (1,5)
What about a Key (or at least an index) on the UserLog table

DavidM

"SQL-3 is an abomination.."

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-04 : 04:25:59

try

select FirstName,
LastName,
UpdateSource ,
UpdateDt
from [User] u, UserLog ul
where ul.ActionDt = (select max(ActionDt) from UserLog ul2 where ul2.UserID = u.UserID and ul2.ActionID in (1,5))
where ul.UserID = u.UserID and ul.ActionID in (1,5)

if ActionDt isn't unique then say something that is.
Also is there an index on UserLog (UserID,ActionDt)

==========================================
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.

danielhai
Yak Posting Veteran

50 Posts

Posted - 2003-04-04 : 17:27:44

ActionID is the type of Action, for instance 1 is a user being created through registration, 5 is a user being imported through an excel file.

The primary keys on the userlog table are userid, actionid, and actiondt.

nr, your query works quite well - got rid of the nested loops in the execution plan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources