Optimization: Subqueries and backreferences?

[Alex]

I wonder if somebody could give me advice on SQL Server7-specific handling of subqueries and backreferences...

What is a) more efficient, and b) faster:

1. IF (DATEDIFF(minute, (
SELECT tLastHitTime
FROM (
SELECT *
FROM tblSession
WHERE tSessionID = 'abcd1234'
) as sess), getDate()) < 30)
SELECT *
FROM sess

or:

2. IF (DATEDIFF(minute, (
SELECT tLastHitTime
FROM tblSession
WHERE tSessionID = &#39;abcd1234&#39;
) as sess), getDate()) < 30)
SELECT *
FROM tblSession
WHERE tSessionID = &#39;abcd1234&#39;


In the first example, 2 SELECT statements (line 2 & line 8) refer to derived table by its alias &#39;sess&#39;. In the 2nd example, both queries are independant, yet identical...

I wonder if SQLServer is &#34;smart&#34; enough to execute identical queries only once, and then reuse the resulset (e.g. derived table), or it requires explicit references (such as alias) in order to do so...

TIA,
Alex

[Testit]

Why don&#39;t you testit and see!


------------
Alex at 5/17/00 5:36:49 PM

I wonder if somebody could give me advice on SQL Server7-specific handling of subqueries and backreferences...

What is a) more efficient, and b) faster:

1. IF (DATEDIFF(minute, (
SELECT tLastHitTime
FROM (
SELECT *
FROM tblSession
WHERE tSessionID = &#39;abcd1234&#39;
) as sess), getDate()) < 30)
SELECT *
FROM sess

or:

2. IF (DATEDIFF(minute, (
SELECT tLastHitTime
FROM tblSession
WHERE tSessionID = &#39;abcd1234&#39;
) as sess), getDate()) < 30)
SELECT *
FROM tblSession
WHERE tSessionID = &#39;abcd1234&#39;


In the first example, 2 SELECT statements (line 2 & line 8) refer to derived table by its alias &#39;sess&#39;. In the 2nd example, both queries are independant, yet identical...

I wonder if SQLServer is &#34;smart&#34; enough to execute identical queries only once, and then reuse the resulset (e.g. derived table), or it requires explicit references (such as alias) in order to do so...

TIA,
Alex

[Alex]

------------
Testit at 5/18/00 9:00:46 AM

Why don&#39;t you testit and see!


------------
Because the machine where SQL server is running on is wickedly fast - the execution time for both queries is 0 ticks. I was wondering if anybody with extensive SQL Server7 knowledge/experience could answer this theoretical question...


TIA,
Alex