Re: Performance concern about table field off a database table view..
** I may be completely off, and if so I would appreciate it if someone simply slaps me, but a View is nothing more than a pre-defined query. It is true at the DB that you can grant permissions to views and such but for the purposes of running a remedy table field off of a view that is just performing a select from another view (remedy built) or directly off of the table itself it won't matter at all. Since your table field is a pre-defined search I think you may be adding a layer of complexity to the process that is not needed. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe DeSouzaSent: Thursday, July 13, 2006 6:29 PMTo: arslist@ARSLIST.ORGSubject: Performance concern about table field off a database table view.. ** This has been done before so I'm inviting comments from those who have done it and are having a heavily used application that might have a significantly large number of records in their main data table.. This is what I am in the process of designing.. I am building a completely custom trouble ticketing application for a cable company. Due to the nature of their business where they are going to use this trouble ticketing application to ticket network outages reported both manually as well as automatically from network monitoring system this table is expected to grow rapidly. On the control panel on their home page I need to have a table field with a 'thin' list of their open tickets (much the same way as the SHR:ConsolidatedList in the ITSM application). The design is that instead of having a place holder Remedy data table to push new tickets on creation with the functionality to modify and delete enteries in that table, I have elected to create a database table view of the main data table with the constraint that it would display only tickets that are in an open status eliminating the overhead that might be caused by creating an additional table, and the push fields that happens on every transaction on the main data table. Then on the control panel I simply build a table fieldthat points to that database view of the table. My question is - would this impact performance in anyway.. I think this might have a positive impact on the performance.. If any of you think otherwise please do write back with reasons why you think it might not be the best design to adopt. If any of you think the traditional approach of a thinner Remedy form to hold the list information is better please do write about that too and your reasons supporting that approach. I hope to hear from you guys.. So far my protype right now on my development boxrocks, but since I have never used this approach in a thick data table that has the potential of eventually housing thousands - maybe a few hundred thousand tickets eventually I would love comments from those using this approach and their experiences with it... Note that I will index the necessary columns that are used in the construction of these database views of the data tables in question so as to optimize the view creation. Cheers Joe D'Souza, Remedy Consultant / Developer, Shyle Networks, New Jersey. Do you Yahoo!?Next-gen email? Have it all with the all-new Yahoo! Mail Beta. __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___
Re: Performance concern about table field off a database table view..
** No you can create a remedy View form out of a database view of a table.. So I have done thatbefore, but was not quite sure whatthe performance impact thatwould have over larger table and hence had put this question out to the list... Apparently I got a responseoffline from one member here who has over 600K records in his main data table that he/she has created a view out of, andthen created a view form in Remedy and built a table field out of that view.. and that works for them much better than the SHR:ConsolidatedList in terms of performance.. I think creating a view as such from data tables for building a thin view of your main table is agreat idea and reduces the amount of workflow you have to define to build a parallel form as such andincreasing data redundancy instead of reducinging it... Joe D'Souza Remedy Developer / Consultant, BearingPoint, Time Warner Cable Project, Virginia. - Original Message From: L. J. Head [EMAIL PROTECTED]To: arslist@ARSLIST.ORGSent: Tuesday, July 18, 2006 12:33:53 PMSubject: Re: Performance concern about table field off a database table view..** I may be completely off, and if so I would appreciate it if someone simply slaps me, but a View is nothing more than a pre-defined query. It is true at the DB that you can grant permissions to views and such but for the purposes of running a remedy table field off of a view that is just performing a select from another view (remedy built) or directly off of the table itself it won't matter at all. Since your table field is a pre-defined search I think you may be adding a layer of complexity to the process that is not needed. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe DeSouzaSent: Thursday, July 13, 2006 6:29 PMTo: arslist@ARSLIST.ORGSubject: Performance concern about table field off a database table view.. ** This has been done before so I'm inviting comments from those who have done it and are having a heavily used application that might have a significantly large number of records in their main data table.. This is what I am in the process of designing.. I am building a completely custom trouble ticketing application for a cable company. Due to the nature of their business where they are going to use this trouble ticketing application to ticket network outages reported both manually as well as automatically from network monitoring system this table is expected to grow rapidly. On the control panel on their home page I need to have a table field with a 'thin' list of their open tickets (much the same way as the SHR:ConsolidatedList in the ITSM application). The design is that instead of having a place holder Remedy data table to push new tickets on creation with the functionality to modify and delete enteries in that table, I have elected to create a database table view of the main data table with the constraint that it would display only tickets that are in an open status eliminating the overhead that might be caused by creating an additional table, and the push fields that happens on every transaction on the main data table. Then on the control panel I simply build a table fieldthat points to that database view of the table. My question is - would this impact performance in anyway.. I think this might have a positive impact on the performance.. If any of you think otherwise please do write back with reasons why you think it might not be the best design to adopt. If any of you think the traditional approach of a thinner Remedy form to hold the list information is better please do write about that too and your reasons supporting that approach. I hope to hear from you guys.. So far my protype right now on my development boxrocks, but since I have never used this approach in a thick data table that has the potential of eventually housing thousands - maybe a few hundred thousand tickets eventually I would love comments from those using this approach and their experiences with it... Note that I will index the necessary columns that are used in the construction of these database views of the data tables in question so as to optimize the view creation. Cheers Joe D'Souza, Remedy Consultant / Developer, Shyle Networks, New Jersey. Do you Yahoo!?Next-gen email? Have it all with the all-new Yahoo! Mail Beta. __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___
Re: Performance concern about table field off a database table view..
Joe,
As you know everything we do on/with the RDBMS affects is performance.
(For the better or worst.) The basic design here should be fine.
My only inquiry is to make sure that the searches your doing are
as efficient as possible. For example you indicated that you only want
to show Open ticktes in the table/view. Good. However, I want to
make a simple suggestion. Add a field that positively identifies a
ticket as Open and a bit of data driven workflow to maintain that
field in the tickets. I would suggest that a simply table that lists
Form, 'Status' value and the corresponding 'Open?' field value with
a set field action that is run anytime 'Status' changes would be a
good starting point for the data driven logic. (unless there are
other rules that the business uses to decide what Open means.) This
way you can query for records that are 'Open?' = Yes instead of
('Status' = 0 OR 'Status' = 1 OR) or ('Status' = 5 OR 'Status'
=6). This design also allows you to later add (or ignore) 'Status'
values, and change their mind about pending or Fixed later and
decide if they are Open or not without changing your DB view objects
too. (Because the view would be based on the 'Open?' field instead of
the 'Status' field values.) Sure they would need to adjust existing
values for records on the system too. However if you wanted too you
could even build direct SQL that would do that for them too from the
'Status' -- 'Open?' value mapping form.
Just a few thoughts. HTH.
--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)
Solution = People + Process + Tools
Fast, Accurate, Cheap Pick two.
Never ascribe to malice, that which can be explained by incompetence.
On 7/13/06, Joe DeSouza [EMAIL PROTECTED] wrote:
**
This has been done before so I'm inviting comments from those who have done it
and are having a heavily used application that might have a significantly large
number of records in their main data table..
This is what I am in the process of designing.. I am building a completely
custom trouble ticketing application for a cable company. Due to the nature of
their business where they are going to use this trouble ticketing application
to ticket network outages reported both manually as well as automatically from
network monitoring system this table is expected to grow rapidly.
On the control panel on their home page I need to have a table field with a
'thin' list of their open tickets (much the same way as the
SHR:ConsolidatedList in the ITSM application). The design is that instead of
having a place holder Remedy data table to push new tickets on creation with
the functionality to modify and delete enteries in that table, I have elected
to create a database table view of the main data table with the constraint that
it would display only tickets that are in an open status eliminating the
overhead that might be caused by creating an additional table, and the push
fields that happens on every transaction on the main data table.
Then on the control panel I simply build a table field that points to that
database view of the table.
My question is - would this impact performance in anyway.. I think this might
have a positive impact on the performance.. If any of you think otherwise
please do write back with reasons why you think it might not be the best design
to adopt. If any of you think the traditional approach of a thinner Remedy form
to hold the list information is better please do write about that too and your
reasons supporting that approach.
I hope to hear from you guys..
So far my protype right now on my development box rocks, but since I have never
used this approach in a thick data table that has the potential of eventually
housing thousands - maybe a few hundred thousand tickets eventually I would
love comments from those using this approach and their experiences with it...
Note that I will index the necessary columns that are used in the construction
of these database views of the data tables in question so as to optimize the
view creation.
Cheers
Joe D'Souza,
Remedy Consultant / Developer,
Shyle Networks,
New Jersey.
___
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
Re: Performance concern about table field off a database table view..
**
Hello Mathew,Basically I am using the field Status to indicate the status of the ticket and considering that my only clause while building this view of the data table is the Status field as per the requirement I have here, considering that the Status field is an indexed field, I think at the DB level, the creation of the view is already optimized.Also after creating the view and finalizing what fields I need to show up on the columns (we are still in development mode) I intend recreating the view with a selective number of fields from that table, selecting only fields on the table that will be used by the table field while ignoring the rest of the fields.So that is my basic approach while creating this
particular view..There are a couple of other tables where I need this view to be created on forms that may have a clause on fields other than the status field, status field including so if these fields qualify to be indexed (such as CTI type of fields) I will have indexes on them.Stupid question to ask maybe but here it goes.. Do you need to index a view separately on its creation or does it inherit the indexes from the parent data table? I'm having a cutelittle foggy cloud here on my knowledge of table views so an opinion on this would help... :-)Joe D'Souza Remedy Consultant / Developer, Shyle Networks, New Jersey.Carey Matthew Black [EMAIL PROTECTED] wrote: Joe,As you know everything we do on/with the RDBMS affects is performance.(For the better or worst.) The basic design here should be fine.My only inquiry is to make sure that the searches your doing areas efficient as possible. For example you indicated that you only wantto show "Open ticktes" in the table/view. Good. However, I want tomake a simple suggestion. Add a field that positively identifies aticket as "Open" and a bit of data driven workflow to maintain thatfield in the tickets. I would suggest that a simply table that lists"Form", 'Status' value and the corresponding 'Open?' field value witha set field action that is run anytime 'Status' changes would be agood starting point for the data driven
logic. (unless there areother rules that the business uses to decide what "Open" means.) Thisway you can query for records that are 'Open?' = "Yes" instead of('Status' = 0 OR 'Status' = 1 OR) or ('Status' = 5 OR 'Status'=6). This design also allows you to later add (or ignore) 'Status'values, and change their mind about "pending" or "Fixed" later anddecide if they are "Open" or not without changing your DB view objectstoo. (Because the view would be based on the 'Open?' field instead ofthe 'Status' field values.) Sure they would need to adjust existingvalues for records on the system too. However if you wanted too youcould even build direct SQL that would do that for them too from the'Status' -- 'Open?' value mapping form.Just a few thoughts. HTH.-- Carey Matthew BlackRemedy Skilled Professional (RSP)ARS = Action Request System(Remedy)Solution = People + Process + ToolsFast,
Accurate, Cheap Pick two.Never ascribe to malice, that which can be explained by incompetence.On 7/13/06, Joe DeSouza <[EMAIL PROTECTED]>wrote: ** This has been done before so I'm inviting comments from those who have done it and are having a heavily used application that might have a significantly large number of records in their main data table.. This is what I am in the process of designing.. I am building a completely custom trouble ticketing application for a cable company. Due to the nature of their business where they are going to use this trouble ticketing application to ticket network outages reported both manually as well as automatically from network monitoring system this table is expected to grow rapidly. On the control panel on their home page I need to have a table field with a 'thin' list of their open tickets (much the same way as the SHR:ConsolidatedList in the ITSM application).
The design is that instead of having a place holder Remedy data table to push new tickets on creation with the functionality to modify and delete enteries in that table, I have elected to create a database table view of the main data table with the constraint that it would display only tickets that are in an open status eliminating the overhead that might be caused by creating an additional table, and the push fields that happens on every transaction on the main data table. Then on the control panel I simply build a table field that points to that database view of the table. My question is - would this impact performance in anyway.. I think this might have a positive impact on the performance.. If any of you think otherwise please do write back with reasons why you think it might not be the best design to adopt. If any of you think the traditional approach of a thinner Remedy form to hold the list information is better please do write about
that too and your reasons supporting that approach. I hope to hear from you guys.. So far my protype
Re: Performance concern about table field off a database table view..
**
Hello Mathew,By the way thank you for responding - appreciated...CheersJoe D'Souza Remedy Consultant / Developer, Shyle Networks, New Jersey.Carey Matthew Black [EMAIL PROTECTED] wrote: Joe,As you know everything we do on/with the RDBMS affects is performance.(For the better or worst.) The basic design here should be fine.My only inquiry is to make sure that the searches your doing areas efficient as possible. For example
you indicated that you only wantto show "Open ticktes" in the table/view. Good. However, I want tomake a simple suggestion. Add a field that positively identifies aticket as "Open" and a bit of data driven workflow to maintain thatfield in the tickets. I would suggest that a simply table that lists"Form", 'Status' value and the corresponding 'Open?' field value witha set field action that is run anytime 'Status' changes would be agood starting point for the data driven logic. (unless there areother rules that the business uses to decide what "Open" means.) Thisway you can query for records that are 'Open?' = "Yes" instead of('Status' = 0 OR 'Status' = 1 OR) or ('Status' = 5 OR 'Status'=6). This design also allows you to later add (or ignore) 'Status'values, and change their mind about "pending" or "Fixed" later anddecide if they are "Open" or not without changing your DB view objectstoo. (Because the view
would be based on the 'Open?' field instead ofthe 'Status' field values.) Sure they would need to adjust existingvalues for records on the system too. However if you wanted too youcould even build direct SQL that would do that for them too from the'Status' -- 'Open?' value mapping form.Just a few thoughts. HTH.-- Carey Matthew BlackRemedy Skilled Professional (RSP)ARS = Action Request System(Remedy)Solution = People + Process + ToolsFast, Accurate, Cheap Pick two.Never ascribe to malice, that which can be explained by incompetence.On 7/13/06, Joe DeSouza <[EMAIL PROTECTED]>wrote: ** This has been done before so I'm inviting comments from those who have done it and are having a heavily used application that might have a significantly large number of records in their main data table.. This is what I am in the process of designing.. I am building a completely
custom trouble ticketing application for a cable company. Due to the nature of their business where they are going to use this trouble ticketing application to ticket network outages reported both manually as well as automatically from network monitoring system this table is expected to grow rapidly. On the control panel on their home page I need to have a table field with a 'thin' list of their open tickets (much the same way as the SHR:ConsolidatedList in the ITSM application). The design is that instead of having a place holder Remedy data table to push new tickets on creation with the functionality to modify and delete enteries in that table, I have elected to create a database table view of the main data table with the constraint that it would display only tickets that are in an open status eliminating the overhead that might be caused by creating an additional table, and the push fields that happens on every transaction on the main data
table. Then on the control panel I simply build a table field that points to that database view of the table. My question is - would this impact performance in anyway.. I think this might have a positive impact on the performance.. If any of you think otherwise please do write back with reasons why you think it might not be the best design to adopt. If any of you think the traditional approach of a thinner Remedy form to hold the list information is better please do write about that too and your reasons supporting that approach. I hope to hear from you guys.. So far my protype right now on my development box rocks, but since I have never used this approach in a thick data table that has the potential of eventually housing thousands - maybe a few hundred thousand tickets eventually I would love comments from those using this approach and their experiences with it... Note that I will index the
necessary columns that are used in the construction of these database views of the data tables in question so as to optimize the view creation. Cheers Joe D'Souza, Remedy Consultant / Developer, Shyle Networks, New Jersey.
Do you Yahoo!? Next-gen email? Have it all with the all-new Yahoo! Mail Beta.
__20060125___This posting was submitted with HTML in it___
Re: Performance concern about table field off a database table view..
It depends upon the DBMS. SQL Server supports indexed views http://www.sqlteam.com/item.asp?ItemID=1015 Stephen -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black Sent: Friday, July 14, 2006 9:00 AM To: arslist@ARSLIST.ORG Subject: Re: Performance concern about table field off a database table view.. Joe, To my knowledge adding an index on a view is not possible. The view itself has no data in it to index. :) ( and your welcome. I am glad to try to help. :) -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Solution = People + Process + Tools Fast, Accurate, Cheap Pick two. Never ascribe to malice, that which can be explained by incompetence. ___ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org ___ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
Re: Performance concern about table field off a database table view..
** This is nice to know... I am on Oracle however so I guess I have one less consideration to think about..Joe"Heider, Stephen" [EMAIL PROTECTED] wrote: It depends upon the DBMS. SQL Server supports indexed views http://www.sqlteam.com/item.asp?ItemID=1015Stephen -Original Message-From: Action Request System discussion list(ARSList)[mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew BlackSent: Friday, July 14, 2006 9:00 AMTo: arslist@ARSLIST.ORGSubject: Re: Performance concern about table field off a database tableview..Joe,To my knowledge adding an index on a view is not possible. The viewitself has no data in it to index. :)( and your welcome. I am glad to try to help. :)--Carey Matthew BlackRemedy Skilled Professional (RSP)ARS = Action Request System(Remedy)Solution = People + Process + ToolsFast, Accurate, Cheap Pick two.Never ascribe to malice, that which can be explained by incompetence. Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta. __20060125___This posting was submitted with HTML in it___
Re: Performance concern about table field off a database table view..
Oracle's semi-equivalent is called 'materialized views'; though I've only seen them commonly used for batch replication purposes. Axton Grams Joe DeSouza wrote: ** This is nice to know... I am on Oracle however so I guess I have one less consideration to think about.. Joe */Heider, Stephen [EMAIL PROTECTED]/* wrote: It depends upon the DBMS. SQL Server supports indexed views http://www.sqlteam.com/item.asp?ItemID=1015 Stephen -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black Sent: Friday, July 14, 2006 9:00 AM To: arslist@ARSLIST.ORG Subject: Re: Performance concern about table field off a database table view.. Joe, To my knowledge adding an index on a view is not possible. The view itself has no data in it to index. :) ( and your welcome. I am glad to try to help. :) -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Solution = People + Process + Tools Fast, Accurate, Cheap Pick two. Never ascribe to malice, that which can be explained by incompetence. Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta. http://us.rd.yahoo.com/evt=42297/*http://advision.webevents.yahoo.com/handraisers __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
