Calling SQL from code has different behaviour than calling it from MS SQL Server Management Studio

[jeeji]

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.Co l1 = #temp_table2.Co l1
)

-- 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.Sql Client
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

[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.Co l1 = #temp_table2.Co l1
)

-- 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.Sql Client
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