 | |
Organization Chart
microsoft.public.sqlserver.programming microsoft.public.sqlserver.programming
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Organization Chart -
12-16-2004
, 11:08 AM
#2
| |||
| |||
|
|
Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
#3
| |||
| |||
|
|
Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
#4
| |||
| |||
|
|
Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
#5
| |||
| |||
|
|
There is only one universal rule in this world: "Don't use XML". Contrary to popular belief XML is a poor tool for handling hierarchies. "Leila" <Leilas (AT) hotpop (DOT) com> wrote in message news:eWHPSH54EHA.1188 (AT) tk2msftngp13 (DOT) phx.gbl... Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
#6
| |||
| |||
|
|
Thanks Mikito, Could you tell me more about disadvantages of XML for handling hierarchies so that I can convince my boss!! "Mikito Harakiri" <mikharakiri (AT) iahu (DOT) com> wrote in message news:e9mwd.24$8Q3.132 (AT) news (DOT) oracle.com... There is only one universal rule in this world: "Don't use XML". Contrary to popular belief XML is a poor tool for handling hierarchies. "Leila" <Leilas (AT) hotpop (DOT) com> wrote in message news:eWHPSH54EHA.1188 (AT) tk2msftngp13 (DOT) phx.gbl... Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
#7
| |||
| |||
|
|
Your concern was performance, but for starters there is no such thing as optimization in XML. Well, some folks claim that latest recearch in XML optimization "looks promising", but what can you really expect from the implementation if even research is not mature yet? Next this ugly verbose "data model" hardly impresses anyone. You have so much baggage for achieve what? To transfer some customer record from point A to point B? Check out how hierarchies and trees are represented in SQL before venturing into XML voyage. Troel's links is a good starting point http://troels.arvin.dk/db/rdbms/links/ "Leila" <Leilas (AT) hotpop (DOT) com> wrote in message news:e$vxS164EHA.2600 (AT) TK2MSFTNGP09 (DOT) phx.gbl... Thanks Mikito, Could you tell me more about disadvantages of XML for handling hierarchies so that I can convince my boss!! "Mikito Harakiri" <mikharakiri (AT) iahu (DOT) com> wrote in message news:e9mwd.24$8Q3.132 (AT) news (DOT) oracle.com... There is only one universal rule in this world: "Don't use XML". Contrary to popular belief XML is a poor tool for handling hierarchies. "Leila" <Leilas (AT) hotpop (DOT) com> wrote in message news:eWHPSH54EHA.1188 (AT) tk2msftngp13 (DOT) phx.gbl... Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
#8
| |||
| |||
|
|
Maintaining this XML chart can be a nightmare if you have lots of changes and you will have to do it in a trigger if you are to ensure it always gets updated. Have you tried using a temp table instead of a table variable? For larger amounts of data a temp table can often be faster and it can be indexed any way you need it to. By the way a table variable does not always use memory, it often spills to tempdb as well. http://www.support.microsoft.com/?id=305977 -- Andrew J. Kelly SQL MVP "Leila" <Leilas (AT) hotpop (DOT) com> wrote in message news:eWHPSH54EHA.1188 (AT) tk2msftngp13 (DOT) phx.gbl... Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
#9
| |||
| |||
|
|
Thanks Andrew, What if I store the whole tree of the organization (like a snapshot) in one table that has one field and one record in XML format? Then updating the chart will be easy if it is done on client and sent back to table. "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:Ory7Fp54EHA.1976 (AT) TK2MSFTNGP09 (DOT) phx.gbl... Maintaining this XML chart can be a nightmare if you have lots of changes and you will have to do it in a trigger if you are to ensure it always gets updated. Have you tried using a temp table instead of a table variable? For larger amounts of data a temp table can often be faster and it can be indexed any way you need it to. By the way a table variable does not always use memory, it often spills to tempdb as well. http://www.support.microsoft.com/?id=305977 -- Andrew J. Kelly SQL MVP "Leila" <Leilas (AT) hotpop (DOT) com> wrote in message news:eWHPSH54EHA.1188 (AT) tk2msftngp13 (DOT) phx.gbl... Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find faster solution for their project because using variable table consumes memory and nested loops(joins) can slow performance specially when the chart is deep and we are beginning to query from somewhere on top of the tree. Their only concern is the speed and it doesn't matter if any solution takes more space in DB but provides better performance. They suggested to add a varchar field to Employees table that stores an XML document, putting the EmployeeID for the top element and all its sub employees beneath(actually demoralizing). It will take up more space and when the organization grows, even the varchar may not be enough and must use text type. Advantages of this solution: - retrieving the chart is fast (only by reading the XML document from the field). This is the most important issue! - the application on client can work easily with XML to add or remove the nodes using something like DOM. - we can use XML type later in SQL Server 2005 and even create indexes on values Still I'm sticking to EmployeeID - ReportsTo solution because: - it needs reasonable space for storing - later in SQL Server 2005 we will be able to use CTE to retrieve the chart - still we are able to send XML data to client by FOR XML clause in SELECT statement. I would be most grateful if somebody could compare advantages and disadvantages of each solution and help me to make best decision. Certainly there could be other problems with each one that I'm not aware of that. Thanks in advance, Leila |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.