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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Subquery performance question
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 11/24/2012 :  02:57:23  Show Profile  Reply with Quote
Hello everyone,

I have a large transaction table and a parameter table with one record. I want to select the transactions from a specific ID specified in the parameter tabel. I have two options:

Hard coded (not a real option, dus for testing):
FROM transactiontable
WHERE ID > 123456789

Or based on the parametertable:
FROM transactiontable
WHERE ID > (SELECT lastID FROM parametertable)

ID is indexed
The parametertable has just 1 record which doesn't change during the proces.

I want to use the parametertable but the first query is much much faster. It seems SQL Server runs the subquery for every record of the transaction table. Not very usefull since the result will always be the same.

So how can I use the parameter version with the performance of the hardcoded version? Can I tell the server to run the subquery just once for all the records?

I've read some articles about joining in stead of a subquery, is that the solution?

FROM transactiontable, parametertable


Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/24/2012 :  08:48:31  Show Profile  Reply with Quote
Could you perhaps do the following?

DECLARE @lastid INT;
SELECT TOP 1 @lastid = lastid FROM parametertable;

SELECT t.* FROM transactionstable t
WHERE > @lastid;
You could join the tables like shown below, but (my untested and unsubstantiated opinion is that) that may not perform any better than what you already tried:
FROM transactionstable t
	INNER JOIN parametertable p ON > p.lastid;

-- Yes, I am indeed a fictional character.
Go to Top of Page

Flowing Fount of Yak Knowledge

2875 Posts

Posted - 11/25/2012 :  17:53:10  Show Profile  Reply with Quote
If there is just one record in the paramtertable
WHERE ID > (SELECT top 1 lastID FROM parametertable)
or WHERE ID > (SELECT max(lastID) FROM parametertable)

whichever tests out better. The optimizer now knows that it just needs one value from the parameter table


Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000