Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql view over application file

Posted on 2011-03-12
3
Medium Priority
?
506 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 1000 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 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

636 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