Performance issue in fetching Data

prav_06 · Posted: Tue Jul 17, 2007 5:13 pm

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.

William Thompson · Posted: Tue Jul 17, 2007 5:17 pm

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

prav_06 · Posted: Tue Jul 17, 2007 5:20 pm

Hey Bill,

Thanks for a quick reply

William Thompson · Posted: Tue Jul 17, 2007 5:30 pm

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...

prav_06 · Posted: Tue Jul 17, 2007 6:14 pm

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.

Nimesh.Srivastava · New User Joined: 30 Nov 2006 Posts: 78 Location: SINGAPORE

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

ashok_uddaraju · New User Joined: 21 Feb 2007 Posts: 72 Location: US

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

dbzTHEdinosauer · Posted: Mon Aug 13, 2007 2:45 pm

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.

stodolas · Posted: Mon Aug 13, 2007 6:37 pm

Does anyone used Materialized Query Tables?

dbzTHEdinosauer · Posted: Mon Aug 13, 2007 6:59 pm

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?

stodolas · Posted: Mon Aug 13, 2007 7:05 pm

Well as an alternative to views, don't they offer better performance if the underlying data doesn't change often?

dbzTHEdinosauer · Posted: Mon Aug 13, 2007 8:58 pm

dbzTHEdinosauer · Posted: Mon Aug 13, 2007 9:02 pm

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.

stodolas · Posted: Mon Aug 13, 2007 9:15 pm

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.

dick scherrer · Posted: Mon Aug 13, 2007 9:28 pm

Hello,