Mr Moose

@MikaelEriksson - By the way, I added the index you suggested, and the query now executes in 5 seconds. Like you say, that helps with this query but doesn't answer the overall question. Prior to the table change, the optimiser chose a query plan that used an index from tc (maybe not the best index...but at least an index). Now after changing the structure of the person table, it doesn't seem to want to use an index from tc. Even after we rebuilt them all.

@mendosi, yeah as I mentioned the queries are not great. These are old legacy queries and in fact this one is only run once a quarter for reporting so the time taken to execute doesn't really concern me. What does concern me is that prior to the structure change to the primary key, the query ran quickly, and now it is very poor. I expect there will be many similar poorly performing queries throughout the code that lead me to question whether my approach is sound. I have to head off now, but thanks for all your help and suggestions.

@MikaelEriksson - Thanks for the suggestions. The option (maxdop 1) has been running for 35 mins now, so it doesn't seem to help. There are a lot of different indexes (most indexs are poorly defined) on the tc table too, but it seems none of them are used in this query like they are in the good query plan. There are foreign key's between tc and person (person has the new table structure), but I don't still see why the new structure causes the bad plan. Do you think it is due to the use of a persisted computed column?

@mendosi - The bad plan for the SELECT alone is here and here is the same SELECT against the new table schema (ie the COALESCE'd computed column) but with the USE PLAN hint forcing the use of the good plan that SQL Server chose against the old table structure.

@MikaelEriksson, thanks for the suggestion. That is how it was to begin with. I adjusted it to remove the ISNULL in the hope that it wasn't confusing SQL Servers ability to create an optimal plan. Do you happen to think that the table structure change is the cause of the bad plan creation?

Hi Mikael. Yes all indexes are recreated and present. We've tried cleaning out the plan cache and updating statistics too. Still has no effect.

While the plan is from an INSERT query, the INSERT is of little consequense. The INSERT takes no time at all. The good plan shows only the SELECT portion. The testing we did yesterday (after capturing the plan) was all based on SELECTs. Sorry, I don't know you either. I am in Perth.

Plan for poor performing query is here and the plan for the reasonable performing query is here. I know the query is poor, but that isn't the point. Good plan runs in < 15 seconds. Poor plan runs in > 20 mins. If we force the new table structure to run using the good plan using the USE PLAN query hint, then it also runs in < 15 seconds. This is why I find it puzzling.

No. The queries are simply SELECT statements, and none of them refer explicitly to the old_id, new_id or external_id fields. The idea was to allow existing queries to run without the need for modification.

Hi All. I am looking for a stackexchange site where I could get an application design reviewed. It would be by no means a detailed design, but a brief description of what I currently have along with it's flaws. I was thinking codereview, but that seems simply related to code and not design. Is this a correct assumption?