Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Performance issue in fetching Data

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
prav_06
Warnings : 1

Active User


Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands

PostPosted: Tue Jul 17, 2007 5:13 pm    Post subject: Performance issue in fetching Data
Reply with quote

Hi All,
I have two DB2 tables say TAB1 and TAB2 with some common fields and I do have a view build for these tables thro the fields which are common in TAB1 and TAB2, say View1. I can pick up data from these tables directly by writing a join query for TAB1 and TAB2 or by quering the View, which of the above would take less time, fetching data from view or fetching data directly from tables.

Thanks in advance

Thamilzan.
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Tue Jul 17, 2007 5:17 pm    Post subject:
Reply with quote

Is the "join query" the same as the view definition?
Back to top
View user's profile Send private message
prav_06
Warnings : 1

Active User


Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands

PostPosted: Tue Jul 17, 2007 5:20 pm    Post subject:
Reply with quote

Hey Bill,

Thanks for a quick reply

Quote:
Is the "join query" the same as the view definition?


Yups , its the same....


Thamilzan.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Tue Jul 17, 2007 5:30 pm    Post subject:
Reply with quote

I would think that once the cursor was opened, they would be the same....
If the newer fetch was dynamic, I would think the view would open quicker...
Back to top
View user's profile Send private message
prav_06
Warnings : 1

Active User


Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands

PostPosted: Tue Jul 17, 2007 6:14 pm    Post subject:
Reply with quote

Bill,

I believe that View would be dynamically built when reffered to, so if you are quering a view there would be two queries executing at the background 1. the Query which builds the view 2. the query which the user gives to fetch data, considering this and the time taken for executing these two queries can't we say that quering the tables directly would be much more efficient, please correct me if i am wrong.

Thamilzan.
Back to top
View user's profile Send private message
Nimesh.Srivastava

New User


Joined: 30 Nov 2006
Posts: 78
Location: SINGAPORE

PostPosted: Wed Jul 18, 2007 8:04 am    Post subject:
Reply with quote

prav_06,
I would say using cursor when involving more than one table is faster than view as the number of records grows in the database. This is based more on what I have seen in my production system than logic I know of, but we had to remove the views involving multiple tables with cursors which were much faster and required programmer permissions rather than a DBA to create / modify the view.

Hope this helps
Nimesh
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 72
Location: bangalore

PostPosted: Mon Aug 13, 2007 2:09 pm    Post subject: Re: Performance issue in fetching Data
Reply with quote

Hi all,
The main reason for creating views is for faster access of data so,I guess fetching from views would be faster

Correct me if i am wrong
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Aug 13, 2007 2:45 pm    Post subject:
Reply with quote

using a view is not faster because DB2 has to deal with object management, since a created view is an object.

Views should be used mainly for security - retrieval of certain columns is excluded.

If you have access to the complete row, you should use an inline view because it is 1) faster and 2) the sql is there and to debug you don't have to go find it elsewhere.

DB2 Developer's Guide, Fourth Edition by Craig S. Mullins wrote:
Why would you want to use an inline view instead of simply creating an actual view prior to issuing the SELECT statement? The first potential benefit is that an inline view expression can be easier to understand. Instead of attempting to query the DB2 Catalog to extract the SQL definition of a view, the SQL is clearly displayed in the body of the SELECT statement. Second, inline views do not require object management because no DB2 object is created. Finally, inline views provide direct SQL support for certain complex queries that required a view prior to DB2 V4.


The same quote is obtainable in Craig's 5th edition.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Aug 13, 2007 6:37 pm    Post subject:
Reply with quote

Does anyone used Materialized Query Tables?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Aug 13, 2007 6:59 pm    Post subject:
Reply with quote

stodalas,

you posts are normally coherent and contribute to the thread. Is this a 'new' question by you or a possible solution to the OP's question?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Aug 13, 2007 7:05 pm    Post subject:
Reply with quote

Well as an alternative to views, don't they offer better performance if the underlying data doesn't change often?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Aug 13, 2007 8:58 pm    Post subject:
Reply with quote

Quote:
if the underlying data doesn't change often?
but that is the important caveat.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Aug 13, 2007 9:02 pm    Post subject:
Reply with quote

to add on to my and your comment:

if the DBs are read-only and there is no need for update during the normal business hours (e.g. marketing info which can be aggregated for tomorrow)
you are correct - that would be the way to go.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Aug 13, 2007 9:15 pm    Post subject:
Reply with quote

Yeah, I just created 5 new tables and asked for a MQT. My DBAs said no, execute the joins everytime on your query. It is a 5 table join and the data will change less than 100 times a year.

At least the tables are small, all under 5000 rows.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 13, 2007 9:28 pm    Post subject:
Reply with quote

Hello,

Quote:
The main reason for creating views is for faster access of data so,I guess fetching from views would be faster

Maybe not. . .

A big reason for using views is ease of use rather than performance. This may be especially true if there are expected database changes. If the view is substantially changed, but returns the same values as the original, the code/queries that use the view will most often not need to be changed.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts SORT JSON type of data maxsubrat DFSORT/ICETOOL 8 Wed Apr 19, 2017 6:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us