subqueries perform worse after database version upgrade

Question

We have been using several nested queries for reporting purposes since the days of SQL Server 2000.

select * from 
 (select * from t1) t1 
  inner join
 (select * from t2) t2
 on........

We recently upgraded our database to SQL Server 2008 Standard from SQL Server 2000 Enterprise. We are noticing some of those old queries are now running very slow or even timing out. Size of database has really not changed a lot.

We fixed above problem by replacing some of the nested queries with temporary tables.

However, we are still not sure that nested were working slow now temp table are working better. We have not made any change join or where clause.

Any insight or thoughts will be helpful.

Answer 1

In general, it would be unusual for a single query to be slower than using temp tables and multiple queries. So, it would seem that the upgraded SQL Server may not have current knowledge about the data.

After upgrading SQL Server you should ensure that the information about your databases is up to date. One thing you definitely should do is to update statistics on your database tables.

You may also find that there might be better ways to run your queries with the newer SQL Server version. So, after updating statistics, you may look into new features of the current SQL Server version.

Answer 2

Generally, query optimizers in MSSQL 2000 and 2008 are very different. So it is quite possible that some queries will run significantly faster on one of them - basically, you have to optimize all your heavy queries anew (possibly making use of some new features - CTEs, window functions, indexes with included data to name a few).

From my experience, MSSQL 2008 usually better "understands" nested queries, though it may be different in your case. If your temp table is identical to the result of the nested query, it would be very strange for the temp table variant to be faster (because the server has to do more work to obtain the same result).
So far, I'd guess you are wrong about the nested queries being the cause of performance problem, but I can't tell what actually is its cause.

Answer 3

if I were in your position, i'd start by evaluating the execution plan to see where the major cost overhead is. From there you can really determine the true cause.

Answer 4

I'm hoping you've over-simplified your sample query for the purposes of this post. Otherwise, I don't understand why you would select * from each and every table in sub-queries when you could simply do this instead:

select * 
    from t1 
        inner join t2
            on........