Strange behaviour of SQL Server - need some advice

I have some sophisticated query (just select with many nested joins and subqueries).

I've noticed in SQL Profiler that when it is e开发者_C百科xecuted from .NET application by ADO it takes 30 seconds to complete and does 129 writes (???).

When I've executed this same query (copied from Profiler) in SQL Server Management Studio it takes only 1.2 seconds and 0 writes (correct).

Do you know why there is so different performance depending on execution method?

And why select makes writes on DB?

---

The query can be optimised differently depending on where it is run from. I had the same when running a stored proc vs. the actual query inside the proc in SSMS. The difference was that the one query was being optimised with certain JOIN hints and the other query wasn't which had a massive impact on performance.

You may just need to manual add a join hint to the query. Without seeing the query I can' tell you what change you need to make but try running the query in SSMS and then having a look at the execution plan.

---

This timings shows that data were buffered, and that cause that sophisticated behavior.