HI
I am experiencing something funny here where I have two equivalent
queries: If I run them through query analyzer, the first runs slower
than the second, but if run through C# application, the first runs
faster than the second.
I have the following two equivalent versions of a query which I will
present in psuedo code:
-------------------------
(1) Simple JOIN of two SELECT statements
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
-------------------------
(2) I create temporary tables, JOIN them and then DROP them.
-- Create Temp tables
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
-- JOIN the temp tables
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
-- DROP the tamp tables
DROP Table #temp_table1
DROP Table #temp_table2
-------------------------
If I run (1) from the query analyzer in MS SQL Server Management
Studio, it runs slower than (2). I clear the SQL server cache before
each call.
If I run (1) from the C# test application, it runs faster the (2) from
the same C# test app. I use .Net 2.0 using the System.Data.SqlClient
calls.
The way I can see the different in execution times is through the SQL
server profiler.
Can anybody explain that?
Any help would be much appreciated
Thank you in advance
Jeeji