Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835

    Going Global real soon

    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?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Rename the original table.
    Create an indexed view of the original data, and name the view the same as the original table name.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    ...which would need to be changed anyway to include the country code, or, throw an INSTEAD OF trigger onto the indexed view for inserts.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Quote Originally Posted by pootle flump
    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?
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    they did not reject views. they rejected partitioned views because of the two table thing Pat was hinting at.
    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....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Pat Phelan
    They shot it down, so you asked us (a disreputable group, but one that routinely does jobs that no one else can do)
    We're the "'A' Team"?
    I pity the fool that takes our advice.
    (But I love it when a query plan comes together....)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •