Solved

sql view over application file

Posted on 2011-03-12
3
458 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

11 Experts available now in Live!

Get 1:1 Help Now