Solved

sql view over application file

Posted on 2011-03-12
3
470 Views
Last Modified: 2012-05-11
For reporting purposes I have to summarize an inventory table in our ERP system by item. It is a simple SELECT SUM and GROUP BY. I would like to create an SQL view over that table to perform the grouping. Is that considered bad practice to create a custom SQL view over an application file? Is it possible that the SQL view will create dependencies or cause performance problems with our ERP system? I only want the view so it can be easily referenced when developing reports. I know it is considered bad practice to create a custom INDEX over an application file, but this would just be an SQL view.

This is an iseries database (db2).
0
Comment
Question by:grog53444
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 35117987
creating a view should not cause any problems
the only dependency is of the view on the base objects so no bad here
the performance impact will be the same if the report access the view or the base objects

i think a view is a good solution here since if the product tables will change in the future, you will just have to change the view and your report will keep working as it did
0
 
LVL 18

Assisted Solution

by:daveslash
daveslash earned 250 total points
ID: 35129877

I agree with Momi that creating a view should not cause any problems.

My only addition to this discussion is to point out that it is not "bad practice" to create an index over an application file if it helps performance (as well-thought-out indexes often do).

I suppose creating a UNIQUE index could cause problems if the column isn't really unique, but creating a normal, generic index is almost never a bad idea.

The only other time an index could be considered "bad practice" would be in some type of a high-volume table where the cost of maintaining one more index out-weighs its advantages. But, to be honest, this is VERY rarely the case.

HTH,
DaveSlash
0
 

Author Closing Comment

by:grog53444
ID: 35236736
Verified that it is ok to add a view. I understand what you are saying about adding an index being ok too. I think we are typically apprehensive about it because the underlying database was built by someone else, and we worry that it will decrease performance on inserts or conflict with their own index addition in a future release.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question