Query performance difference between Sql Server 2005 and 2000

Hi,

I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.

The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).

I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.

Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.

Cheers,

Chris

Chris,

You may find Umachandar's response in this thread informative.

|||

Hi Arnie,

Thanks for your reply. I'd already tried most of the things Umachandar suggests, and the performance for each query remains fairly consistent.

Thanks anyway, though.

Chris

|||

Chris,

Have you tried to compare the SQL Server 2000 and the SQL Server 2005 execution plans?

Given that the performance difference is so dramatic (1s --> 2-3min) I would expect to see some differences in the use of indices or join types. If you know what those are,you could influence the plan choice using a hint or we could look into why plan choice is different in SQL 2005. Please make sure that you are using the same database and same set of parameter values when you do this experiment.

Regards,

Leo

|||

Hi Leo,

I have not yet compared plans on the two servers. My main interest was on why the results varied so dramatically when just using sql server 2005 and adding a more stringent filter in the where clause, so I focussed on the difference between these plans (and to be honest they actually made very little sense to me!). I will try to rerun the queries and summarise the difference in plans, and if you could cast an eye over it I'd be very grateful.

Cheers,

Chris