query performance in different OS

v10g · #1 (**permalink**) 07-24-11, 04:56

Hi,

I have a query which reads from syscat.columns for a particular table , to find out that table's column details.

select typename,length into i_typename,i_length from syscat.columns where tabname=<mytabname> and colname=<mycolname>
-- here mytabname and mycolname are variables.

This query is fast in Windows, but slow in AIX.
db2 version is 8.2 .

This query is in a loop , to find details for different tables and columns.

Later I changed the query , in such a way that , I fired the select qry from system table only once and copied those data into a temp table and then I am reading from the temp table.

This query is also fast in Windows, but slow in AIX.

1.My question is does query performance depend on OS?
2.Any OS configuration settings affect the query performance? If so what are they and how to read those settings?
3. Any database configurations affecting the performance ? If so what are they and how to read those settings ?
4.Or is this because of reading from system tables ?

thanks .

tonkuma · #2 (**permalink**) 07-24-11, 05:35

Almost every factors affect to performance.

It would be better to look this first.
(It is for DB2 9.7, but basics are common to other versions.)
Performance overview - IBM DB2 9.7 for Linux, UNIX, and Windows

For example:

Quote:

Performance information that users can provide

The first sign that your system requires tuning might be complaints from users. If you do not have enough time to set performance objectives and to monitor and tune in a comprehensive manner, you can address performance issues by listening to your users. Start by asking a few simple questions, such as the following:
•What do you mean by "slow response"? Is it 10% slower than you expect it to be, or tens of times slower?
...
...

tonkuma · #3 (**permalink**) 07-24-11, 05:43

Quote:

This query is in a loop , to find details for different tables and columns.

Why in a loop?

Any other ways?
For example:
1) Open a cursor for "SELECT tabname , colname , typename , length ... ORDER BY tabname , colno"
2) Issue fetch(es) in a loop.

Note: If you want to specify specific combinations of tabname and colname,
use like ...

Code:

SELECT tabname , colname , typename , length
 FROM  syscat.columns
 WHERE (tabname, colname)
       IN (VALUES (yourtabname1, yourcolname1), (yourtabname2, yourcolname2), ...)
 ORDER BY
       tabname , colno

v10g · #4 (**permalink**) 07-24-11, 06:13

Thanks.

But the loop one is the first approach, where I have a cursor which gets the column names, for which I need the type and length. so placed the syscat. columns qry inside that loop.

Later I changed the approach by reading all the columns outside the cursorloop . Now reading from syscat.columns for these columns and copying into temp table. Now inside the loop, I am reading the data from that temp table for each column.

Something like copying data from syscat.columns into a temp table first and then reading from this temp table inside the loop. replacing the call to syscat.columns with call to temp table.

tonkuma · #5 (**permalink**) 07-24-11, 09:35

Quote:

But the loop one is the first approach, where I have a cursor which gets the column names,
for which I need the type and length. so placed the syscat. columns qry inside that loop.

Later I changed the approach by reading all the columns outside the cursorloop .
Now reading from syscat.columns for these columns and copying into temp table.
Now inside the loop, I am reading the data from that temp table for each column.

Instead of that approach, I thought that you can try...
1) declare/open a cursor outside the loop for

Code:

SELECT colname , typename , length
 FROM  syscat.columns
 WHERE tabname = <yourtabname>
   AND colname
       IN ( SELECT statement for a cursor which gets the column names )
 ORDER BY
       /* your required sequence in reading the data from that temp table for each column. */

2) fetch the cursor inside the loop
instead of reading the data from that temp table for each column.

By this approach,
you can avoid a cursor which gets the column names,
and reading from syscat.columns for these columns and copying into temp table.

And you may be able to expect more optimized query for a cursor than using two cursors.

Internet Services

Web Development

Digital Marketing

Consumer Tech