November 14, 2007 at 8:44 am
So then you are back to doing a manual adjustment of a script file.
However I would like to see the performance on the view assuming that you have an identity column on the doctor table thats referenced in doctor_address.
100k is really not that many rows in the grand scheme of things. Now if you were talking about 100 mil rows or something I might have more of a concern. But even with 2 tables of 100k or so in each joined together properly I don't think you are going to see a noticeable performance difference between pulling a few rows from the view or a few rows from just the 1 table.
Anyone else have an opinion on that?
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 14, 2007 at 9:09 am
Yes I confirm, I have an identity column on doctors table referred in the doctors_address table. We have always performances problems, because of about 100 users using a program, is a callcenter, and during a single phone process, we have at least 3/4 query to the doctor table, each operator makes about 20/30 phones in an hour, in the most optomistics ipotesys we have 20*3*100=6000 queryes in an hour, ie 1.6 query a second. I think that if the query job , due of the use of the view instead of the table, increase is >0 and i suppose it is, is better not to use the view.
November 14, 2007 at 9:19 am
True enough. Although I have used views within SPs that get called more on the order of 100-1000 times a second without any real problems.
I'm still thinking the rename is your best bet then go back and change anything that is only calling the doctors name information. You gain 2 benefits this way.
1) Your code will work initially with no changes .. If you do it the other way and happen to miss something then whatever it is will probably break.
2) You are probably going to be making less changes by just changing the cases where the doctors name or email is pulled than where some other information is pulled also.
On the other hand I'm looking at a very small piece of your puzzle so I could certainly be missing something.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 14, 2007 at 10:18 am
It's a geat puzzle, also because i didn't manage the db from it's start and there are a lot of things not planned correctly.
However I admit there your suggestion is a very nice and esasy way to make this changes, let me think a bit more on it to find if there is any hidden implication in our actual structure
Thks again
November 15, 2007 at 1:20 am
Going back to the original question, if the original table is to be dropped, you could build a view that has the same name as the original table and does a select * from newtable. Then you needn't change any dependent code.
Main risk here that I can see might be performance, but that can be tested. Developers down the line might get a surprise if you have a view called tblOriginal (if that's your naming convention) but they shouldn't fall off their chairs in horror.
Another possibility if you want to get around the dependency issue would be to use Redgate SQL Compare and compare your database with model. This will give you a well ordered script which you could then do the search and replace on.
HTH
Bill.
November 15, 2007 at 11:12 am
Do you have a test database to try this out on? If not, restore a full backup to another database and try out the conversion safely against it.
Steve
December 10, 2007 at 7:06 am
Well finally had time to try this:
1) rename doctors original table (or drop it after copying alla data in a new table)
2) build up a query with same original table's fields and named doctors
But point 1 doesn't work:
It always says: Object '%s' cannot be renamed because the object participates in enforced dependencies
I dropped all of triggers and FKs and the table is in any db diagram
No way to original drop the table as well because od dependecies
Any suggestion
Tks
Ruggiero
December 10, 2007 at 1:26 pm
Try running the following query looking for either Parent_Table or Child_Table is your table name. Just to confirm that you don't have any foreign keys. You may also have to drop any constraints. Just don't forget in dropping these things to keep a script to add them back once you have renamed your table.
Just out of curiosity are you doing the rename by using sp_rename or are you going through the interface? If you are using sp_rename you might want to try the interface. It may go ahead and handle dropping recreating any dependencys for you.
SELECT
ForeignKeys.name AS Key_Name,
Parent.name AS Parent_Table,
Parent_Columns.name AS Parent_Column,
Child.name AS Child_Table,
Child_Columns.name AS Child_Column,
OBJECTPROPERTY (sysforeignkeys.constid , 'CnstIsDisabled') AS Is_Disabled
FROM sysforeignkeys
INNER JOIN sysobjects ForeignKeys
ON sysforeignkeys.constid = ForeignKeys.id
INNER JOIN sysobjects Parent
ON sysforeignkeys.rkeyid = Parent.id
INNER JOIN sysobjects Child
ON sysforeignkeys.fkeyid = Child.id
INNER JOIN syscolumns Child_Columns
ON sysforeignkeys.fkey = Child_Columns.colid
AND Child.id = Child_Columns.id
INNER JOIN syscolumns Parent_Columns
ON sysforeignkeys.rkey = Parent_Columns.colid
AND Parent.id = Parent_Columns.id
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
December 10, 2007 at 2:24 pm
Solved it was a query with the schemabinding option.
Everything worked fine.
Thks again
Ruggiero
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply