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

Hi again

I found out what was wrong. I did not clear the cache from the C# code,
which is obviously necessary .Sorry for the disturbance

Jeeji

jeeji skrev:

> 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