If the two queries are basically identical, then I suspect it's parameter sniffing (you can look that up). First, make sure that the queries are identical (I know you said...

There are basically three ways you can do it; MAX, TOP, and ROW_NUMBER. They all work. They all don't work equally well. I wrote an article, with code examples, comparing...

GilaMonster (4/15/2009)

---

Well, SQL does support the ASCII standard for storing character data (char and varchar) and it supports some of the ANSI SQL standards (of multiple different years), so you...

Yes.

SQL Server is compliant with most of the ANSI standard for SQL.

SQL Server supports and works with ASCI text.

This just...

No worries. Try the stuff Lynn is suggesting. He's looking over the queries. I just glanced at them. I'm interested in how the execution plans vary.

OK. That's very complete.

Get the execution plans. Especially the one where the 2005 ran well, but the others too. That way we can tell what's happening between the two servers.

Yeah, I thought I clicked on the PM button on a message of Gail's but I guess not. Poor Lynn. Time for me to go home. I have to run...

Nah, you teasing us with the "mind boggling" thing. Not fair 'tall.

Processes normally block and lock resources as they work. If you're hitting excessive blocking & locking than you should address the code and the structure, not using query & table...

I don't know anything about iBase either. I went and read through their literature. I suspect you would have a great deal of trouble using the iBase database as is...

It's not the same query if you've changed the code.

However, yes, with the same code, same data, same statistics distribution (you won't get the same statistics) you could get different...

Is it a table valued UDF or inline scalar? If it's table valued, that might the core issue righ there. And no, that's not unique to 2005.

Even if it's inline,...

If you want to get some idea as to why it works differently between 2000 & 2005, get the actual execution plan and see what SQL Server is doing with...

It's relational. I don't know enough to say it was poorly designed, but eliminating FK's...

If you're running a query through a linked server, most of the time, all the data is moved across the wire first, then filters & joins are done to extract...