Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mohamedyousuff@yahoo.com
Starting Member
24 Posts |
 Posted - 2002-05-16 : 06:06:08
|
| Hi All, In my implementation, I have two tables having the master detail relationship. At runtime, I join them to produce the result. The detail records expected for a single master record is about 15 or 20 at the max. But at a given time, when the tables are joined, atmost one detail record is selected for a master record. Atmost 30 master records will be selected at a given time. I have some nvarchar fields in the detail table. Now a recommendation was given that the detail records be changed as an xml string and put in an ntext field in the master table itself to avoid the joins. Then to manipulate the xml in each record from the code to extract the relevant data only. The change was suggested assuming that it improves performance by avoiding the join. My problem is not a technical one. My project is nearing completion. Now all of a sudden implementating such a change would require a lot of work and time. The code to be changed is a generic Data Access Layer, which drives the entire application. I have solved lot of 'killing' problems during the duration (4 months) of its implementation. With this kind of change suggested, apart from the effort of work required, I fear for new 'bugging' problems by these changes which must be solved in a very very limited time available, may be within 1 week or so. In order to avoid the new changes, I put forward some problems like we are retrieving unnecessary data through the network which will result in congested network traffic and cause severe IO problems. I need general advice of using ntext column and also on the new approach suggested. I am using DataSet/SQLServer 2000. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-16 : 08:36:32
|
| I agree with your misgivings about changing it so late in the project. Don't change it.The biggest problem I see is that by putting details into an XML string, it is MUCH harder to modify individual details. It's a simple row update in SQL Server, but as XML you have to parse everything, make the change, then reconstruct the XML. And if you only wanted certain details for a master record, you have to get the whole XML string and prune away the ones you DON'T want.Considering that SQL 2000 can build XML output very easily, you should stick with the structure you have now. Take a look at the FOR XML clause in Books Online, you'll find a way to write queries to generate XML in the format you need without modifying the tables. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-16 : 10:08:30
|
The first thing, i would ask is what had prompted to go for Master/Child relationship if you change it to single table will all the data you where storing with child can be accomdodated in the master table without any problem to its consistency on insert/update/delete . i would suggest you to post your table schema so that team members can suggest a better design if at all there are short comings in it.quote:
In my implementation, I have two tables having the master detail relationship. At runtime, I join them to produce the result. The detail records expected for a single master record is about 15 or 20 at the max. But at a given time, when the tables are joined, atmost one detail record is selected for a master record. Atmost 30 master records will be selected at a given time. I have some nvarchar fields in the detail table. Now a recommendation was given that the detail records be changed as an xml string and put in an ntext field in the master table itself to avoid the joins.
Trust me , if you change the database schmea (that too so much that you have to change the code that drives the entire application then you can only dream of completing the project with no end in sight.quote:
My project is nearing completion.
you are absolutely right with your fear about new problems creaping up. Am sure with 4 months of coding and testing you have come up with a application and then suddenly THEY require a change. the first thing you should ask them is on what grouds do they think the change can improve performance (isnt there anyway you can do something with the current design to improve performance, like creating suitable indexes ....if the project is BIG , load balancing with multiple servers...) . AND how will the incorporate the change in the current application , do you have to start coding from the SCRATCH. if the change suggested would help in improving the performance dramatically without any problems on its consistencey , they you should go for it instead of shipping the project and later coming with complaints from the user and again having no other alternative then to rework on the whole project( if such a thing does existed and you have discovered it after 4 months...near completion of your project . it doesnt reflect good on the guy who was responsible for such a design).From where does your fears of increase n/w traffic creeps from?. can you put some more information .-------------------------------------------------------------- |
|
|
|
|
|
|
|
|
ntext and nvarchar