Query optimization ..

sokando · #1 (**permalink**) 02-25-10, 12:51

may someone help me with this ?! ...
i want to know how can i calculate the cost for processing a query ..
i mean how can i calculate the number of disc accesses to process the query ..
in order to optimize the query processing ... thanks

n_i · #2 (**permalink**) 02-25-10, 13:10

The [estimated] I/O and CPU load figures can be found in the query explain plan. Use Visual Explain, db2expln or db2exfmt to obtain the plan in readable form.

Marcus_A · #3 (**permalink**) 02-25-10, 13:10

You can do an Explain, which will estimate the cost and number of rows based on the statistics as of the last runstats. You can run the explain via command line, or use the Visual Explain from the Control Center.

sokando · #4 (**permalink**) 02-25-10, 13:40

i meant it simple ..
an example ...my selection will select 400 records out of 1000 record and then select another 200 records out of 1000 records and cartisian product them (Selection 1 × Selection 2).. and every block carries 4 records (blocking factor = 4 records) .. how many hard accesses then ? ..

dav1mo · #5 (**permalink**) 02-25-10, 14:30

each time will be different due to what is in bufferpool at the time, so there is never anyway to determine how many times you will actually go to disk. The way to optimize your query is to use visual explain and ensure proper index access with matching columns to all of your tables. Also, stay away from cartesian products.
Dave

n_i · #6 (**permalink**) 02-25-10, 14:30

Take a look at the dynamic SQL snapshot:

Code:

 Rows read                          = 23640
 Internal rows updated              = 0
 Rows written                       = 0
 Statement sorts                    = 3
 Statement sort overflows           = 0
 Total sort time                    = 0
 Buffer pool data logical reads     = 192
 Buffer pool data physical reads    = 1
 Buffer pool temporary data logical reads   = 0
 Buffer pool temporary data physical reads  = 0
 Buffer pool index logical reads    = 1
 Buffer pool index physical reads   = 1
 Buffer pool temporary index logical reads  = 0
 Buffer pool temporary index physical reads = 0

Internet Services

Web Development

Digital Marketing

Consumer Tech