Solved

sql view over application file

Posted on 2011-03-12
3
464 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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now