Subquery performance question - 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 2005 Forums

Transact-SQL (2005)

Subquery performance question

Author

Topic

Tinkerbell
Starting Member

1 Post

Posted - 2012-11-24 : 02:57:23

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):
SELECT *
FROM transactiontable
WHERE ID > 123456789

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

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

Issue:
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?

SELECT *
FROM transactiontable, parametertable
WHERE ID > lastID

Thanks,
Tinkerbell.

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-24 : 08:48:31

Could you perhaps do the following?


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

SELECT t.* FROM transactionstable t
WHERE t.id > @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:

SELECT t.*
FROM transactionstable t
	INNER JOIN parametertable p ON t.id > p.lastid;

________________________________________
-- Yes, I am indeed a fictional character.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-25 : 17:53:10

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

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources