Solved

sql view over application file

Posted on 2011-03-12
3
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Dave Ford
Dave Ford 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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