Coldfusion Lots of loops vs query of queries

I am deleting login records in my database that don't have a corresponding logout record, but right now it's very slow It does this:

First it gets the queries to loop over to check to delete Next it needs to find out if the n开发者_如何学Goext record for that user is a login or logout, if it's a login, I delete it.

To get the next record of that type it does this query of query:

<cfquery dbtype="query" name="getnext" maxrows="1">
SELECT * FROM getlogs WHERE id > #id# AND logType = 'login'
</cfquery>

But it's slow, doing it thousands of times makes it take about 56 seconds.

What would be a faster way to do this? Would another cfloop inside my loop (basicly a loop until I get to the row I want) be faster? Is there another way?

---

This sounds like something that can be done entirely in one query -- perhaps something like this:

delete from login_table t
where exists (
  select id
  from   login_table
  where  id > t.id
  and    logtype = 'login'
)

This has nothing to do with ColdFusion per se; the same approach would apply in any environment. If this is a maintenance function that has no synchronous dependence on your application, you could even stick it into a stored procedure invoked automatically by a recurring "cleanup" task in the database itself.

---

Your best bet is to do it all in sql, using cursors or a temp table. That saves the roundtrips between the CF and sql servers.