SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

Tinkerbell
Starting Member

Netherlands
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):
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

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 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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 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

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000