Ok so we bought a company that performs what my company does in another country. All of our data is USA data right now and the powers that be are about to start stuffing this foreign data into my database and they have added country code to our address table with only 2 possible distinct values. The Address table is pretty big. I think it is every piece of commercial real estate in the USA.

So the requirement has come down to eliminate this foreign data from almost all reporting (~1000 stored procs). My main concerns are the performance impact and minimizing code and schema changes. Performance is going to be a serious issue. It is not just “address” oriented procs that will have to be changed but the procs that assume they are looking at US data that do not currently reference Address will have to now reference Address and filter on the country code.

I have been toying around with index adjustments and indexed views but I am feeling a little stuck on options here because the production server is SQL Server 2000. Otherwise I am aware that I would have other options like table partitioning, filtered indexes etc, but this is all going to Testing in 5 weeks and the likelihood of my DBA team finishing an upgrade in my testing and production environments in that time is 2 things. Slim and none.
I am thinking indexed views for my core base tables that join back to Address and filter on the country code unless anyone has any other bright ideas. That way I just swap out my table names for the views in the procs and be done with it but I am pretty sure this is still going to blow on the performance end.

Do any of you gurus have any brighter ideas?

Rename the original table.
Create an indexed view of the original data, and name the view the same as the original table name.

that was my first sugggestion, but no one bought it. the data entry end of this thing is going to have to be able to see the foriegn data and this does not solve my perf issues with reporting.

Does the address table have a surrogate or natural key? If natural, how many columns comprise the PK? If surrogate, what is the data type? How many foreign keys reference the address table?

Where I'm headed with this line of reasoning is to allow two different tables, one for the present data and one for new data. Create a view that is a UNION of the two tables as a "master address list" and feed that to the data entry folks. A bit of a kludge, but one that will probably get you by until you can upgrade to a new version of SQL Server so that you can address the problems properly.

-PatP

I have been hanging out with you guys too long. I tried selling the 2 tables and one partitioned view thing too. No love and I am coming in for the 4th quarter of this project, so I am larglely left with kludging around some questionable design decisions.

It is a surrogate key. Integer.

All these procs would want US data? They are not expected to be parametrised on country code?
If so, how can they reject views? Using them is standard practice to hide schema changes. Why is no one buying it? What are their suggestions?

pootle flump ur codings are working excelent.

Also, with Blindman's suggestion the reporting would be just as efficient - why do you think not? You'd only have to change the CRUD procs....

pootle flump ur codings are working excelent.

...which would need to be changed anyway to include the country code, or, throw an INSTEAD OF trigger onto the indexed view for inserts.

I guess my thought on this has to be that they're asking you to do a job that they've positively proved that they can't do (by the previous failure), then telling you that they want you to do the job the way they want it done... You might point out to them that they need to carefully exclude that kind of behavior from their resumes, since it will almost ensure that no one will hire them after the upcoming failure.

You're a smart guy, and you had a good idea. They shot it down, so you asked us (a disreputable group, but one that routinely does jobs that no one else can do) and we gave you the same idea you suggested in the first place. There is a learning opportunity here!

You need to dig in your heels and tell the "nay sayers" that they need a better reason why you shouldn't try the idea that pretty much all of the DBAs proposed. When you have a nearly unanimous suggestion from an informed group, there is a good chance that it is the right answer.

-PatP

they did not reject views. they rejected partitioned views because of the two table thing Pat was hinting at.

their suggestions? No one even percieved the performance problem until I said "that is going to suck wind" when they were explaining the project to me. I demonstrated it for them with a couple of simple queries and it became my problem to solve. Most of the OLAP\warehousing\reporting stuff is owned by my team.

Crud is not a problem because the foriegn data is coming in over a feed and it is only to be tweaked within our custom CRM fullfillment system. It will be flagged as foriegn upon load.

I have had the new guy running test loads with the indexed views today. I have took at the reads, writes and execution times against my baselines, but I am not hopeful.

I'd make sure you email those that are currently saying no to just confirm your task. Detail what you've already suggested and why. Put down why they said no. Then say what your reservations are including the time scale you have. If the shit hits the fan then that email might come in handy.

Mike

I am not too worried about it. I get bored when projects are not crazy. Dad taught me to swim by throwing me in the lake. At least I think that was his motive.

And they rejected an indexed view (a al blindman). So they accept views in theory, but not in practice? That reminds me of someone's sig....

pootle flump ur codings are working excelent.

they have not rejected the indexed view. the part of blindies solution they did not buy was the renaming of the original table and naming the view the same thing as the old table. this does not work, because OLTP does not need to be filtered and the outward facing product is built on a transformation of these base tables. We do not want to touch that the data entry or the product build. So as usual I get the short end.

anywho. I am off to looking at my testing numbers for indexed views.

I would fall on the floor and throw a tantrum for sql server upgrade right now if I thought it would do any good.

We're the "'A' Team"?
I pity the fool that takes our advice.
(But I love it when a query plan comes together....)