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 cute little 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:
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 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.
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail Beta. __20060125_______________________This posting was submitted with HTML in it___
