Re: Poor performance after upgrading to sql server 2005

We have just upgraded our development server to sql sever 2005. Dual
processor, 4GB RAM, 64 bit etc.

We also noticed an incredible difference in the execution of stored
procedures that are using correlated subqueries. An interesing note is
that while rewriting one of these stored procedures I created an outer
join to a derived table (the replacement) and while verifying the
results, my performance was back, and the data was correct in both
instances. Comment out the derived table, performance deserts us.

Another interesting thing was pointed out to me today by a co-developer
(as I was going through and re-writing a stack of these) is that an ad
hoc query built and then executed with the EXEC command did not suffer
from the same performance problem.

Do these differences have to do with the statement level compilation
introduced in 2005?

Any thoughts would be appreciated.

.

Relevant Pages

  • Re: Poor performance after upgrading to sql server 2005
    ... The optimizer has changed between 2000 and 2005 so there will certainly be ... We also noticed an incredible difference in the execution of stored ... that while rewriting one of these stored procedures I created an outer ...
    (microsoft.public.sqlserver.setup)
  • Re: Complex Database Transactions
    ... means that a query or execution plan is created by the database ... This execution plan is then ... I would expect stored procedures to be a little faster under most circumstances. ...
    (microsoft.public.dotnet.framework.adonet)
  • How to ensure sequential command execution via SQL Query tables?
    ... sequential order, including the execution of several stored procedures on a ... by these stored procedures. ... this is a sample execution outline: ... I realize my code above is a hack since I'm really using Query Tables to ...
    (microsoft.public.excel.programming)
  • Re: Slow Query in PL/SQL Block
    ... I am facing a problem in one of my stored procedures. ... toDate:= to_date; ... 503 bytes received via SQL*Net from client ... I am not able to figure out why the SQL execution in this case is ...
    (comp.databases.oracle.server)
  • Getting values into VB from an Output parameter of a stored procedure
    ... I have a stored procedures on sql 2000 which has both input & output ... error occurs during execution of the any of tsql commands sp generates an ... Commit Tran ...
    (microsoft.public.vb.general.discussion)