performance question

performance question

Post by xix » Sat, 28 Jun 2003 04:39:59



hello, i am using db2 udb v8.1 on NT, with db2jcc.jar as jdbc driver
(type 4)

1. i create a view with where clause , so when i create sql reading
the view, i don't need to build the where clause on the fly.  so based
on the performance, which is better? create a view with where clause
or create a view without where clause but build the where clause when
create the sql?

2. which one is better, open the result set once,  return a big result
set or frequently open , return a smaller result set

can you provide me some sql performance tuning tips or articles?
thanks

 
 
 

performance question

Post by Mark » Sat, 28 Jun 2003 05:11:25



Quote:> hello, i am using db2 udb v8.1 on NT, with db2jcc.jar as jdbc driver
> (type 4)

> 1. i create a view with where clause , so when i create sql reading
> the view, i don't need to build the where clause on the fly.  so based
> on the performance, which is better? create a view with where clause
> or create a view without where clause but build the where clause when
> create the sql?

There will be an extra access to some of the catalog tables to resolve the
view.  Even with the view, the difference may not be significant. If the
same statement is repeatedly submitted by the same thread, the access plan
will be cached and there will be no extra processing.

Quote:> 2. which one is better, open the result set once,  return a big result
> set or frequently open , return a smaller result set

This depends on whether there is any user wait time (to scroll to the next
set that fits on the screen, etc.). If there is a good chance that the user
will not look at all the rows, then it might be better to fetch them in
batches as the user requests them.

Quote:> can you provide me some sql performance tuning tips or articles?
> thanks

To improve performance, I would get off NT and onto Windows 2000 or higher.

 
 
 

1. SQL performance question

I would be grateful for any comments on SQL performance issues on the
following.

1. Is there any difference between:
 a)   term IN ('term1', 'term2')
 b)   term='term1' OR term='term2'

2. I have a large number of terms that I have to search against a
user's input string in order to find phrases thus
 'cherry blossom days' LIKE '*'&term&'*'
   Most of the terms are single words with just a few multi-word
phrases. Is there any performance gain by ANDing the above with
  name LIKE '* *'
   in order to reduce non-indexed string searching. Or would it be
better to include a boolean field indicating where terms are
multi-word phrases.

2. MDI Frame Resize

3. two newbie performance questions

4. Oracle 9i utl_tcp hangs entire db

5. Select statement performance question - DDL included

6. [WEBMASTER] 'www/html/docs awbook.gif awbook.html'

7. Newbie Performance Questions

8. BCP problems

9. Performance question

10. Performance questions (long)

11. MS SQL server performance question

12. Performance Question