Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is this good practice for using a view? Expand / Collapse
Author
Message
Posted Tuesday, December 03, 2013 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 14, 2014 10:17 AM
Points: 4, Visits: 24
We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.

The idea has come to use a view that takes the place of the table. The view would make a decision based on a boolean value which actual table to point to. This would allow us to incorporate a second table with updated information. Then, switch the boolean value that tells the view to point to the new table.

Does that sound like a good idea? Why or why not?

Any help is much appreciated.
Post #1519287
Posted Tuesday, December 03, 2013 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
raysteve43 (12/3/2013)
We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.

The idea has come to use a view that takes the place of the table. The view would make a decision based on a boolean value which actual table to point to. This would allow us to incorporate a second table with updated information. Then, switch the boolean value that tells the view to point to the new table.

Does that sound like a good idea? Why or why not?

Any help is much appreciated.


That sounds like a very unfeasible idea. You can't have if statement or any other kind of control logic in a view.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1519298
Posted Wednesday, December 04, 2013 9:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
How about some code before the view that uses SYNONYM for the variable tables, then select from the view using the synonym name.


Post #1519855
Posted Thursday, December 05, 2013 5:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:38 AM
Points: 128, Visits: 486
Why not update your sproc with the logic needed? Is there a reason it can't go there? For that matter, why not in the app? Does it have to be on the DB side?

Mark



Post #1519978
Posted Friday, December 06, 2013 11:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
raysteve43 (12/3/2013)
We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.

The idea has come to use a view that takes the place of the table. The view would make a decision based on a boolean value which actual table to point to. This would allow us to incorporate a second table with updated information. Then, switch the boolean value that tells the view to point to the new table.

Does that sound like a good idea? Why or why not?

Any help is much appreciated.


Is this just a one-time operation? Do you not have a maintenance window in which to do this? Adding columns will block all concurrent access to the table since it requires a Sch-M (schema modification) lock. Even so, adding columns and updating 200K rows should probably take a few seconds at most (unless there's some seriously convoluted logic and/or calculations required). Honestly, changing the schema of a production database while it's in operation seems a little risky.

If you have no other choice, here's one suggestion that suffers from a notable drawback. At one point in the process, there will be a very brief interval when an object that is referenced by queries will not exist and if those queries run during that time, they'll return errors. Also, I'm pretty sure this method will cause recompilation of all query plans that reference this lookup table, which could be a noticeable drag on performance until new plans are cached for all affected queries. You should test this in a non-production environment before trying it, of course.

Suggestion: Create a new version of the table with the additional columns (and a different name, of course) and new data. Execute the following:

EXECUTE sp_rename @object_name = 'oldTableName', @new_name = 'someOtherName'
GO
-- Here's the interval where oldTableName will not exist and queries looking for it will throw errors.
EXECUTE sp_rename @object_name = 'newTableName, @new_name = 'oldTableName'
GO



Good luck with this!

Jason



Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1520698
Posted Friday, December 06, 2013 5:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
raysteve43 (12/3/2013)
We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.


Stop guessing. Test it. Make a copy of the table and the indexes and do some performance testing.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1520780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse