Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: only first query is reaaaal slow

    DB-guru`s

    i`m running db2 v8.1 on a win2k server and my database is populating over 900.000 records. When I execute the first query DB2 takes almost 2 minutes to return the results, the next time i run the same query the results return in 2 secs. But after a few different queries, the same problem occurs.

    it`s probably a cache thing or not?

    maybe you guys can give me a pointer how to fix this anoying issue.

    best regards

    Stephan

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: only first query is reaaaal slow

    Yes ... it is a cache thing ..
    In DB2's case, the data cache is called a bufferpool ...

    Search for the term bufferpool in this forum for more info ..

    HTH

    Sathyaram


    Originally posted by soudmaijer
    DB-guru`s

    i`m running db2 v8.1 on a win2k server and my database is populating over 900.000 records. When I execute the first query DB2 takes almost 2 minutes to return the results, the next time i run the same query the results return in 2 secs. But after a few different queries, the same problem occurs.

    it`s probably a cache thing or not?

    maybe you guys can give me a pointer how to fix this anoying issue.

    best regards

    Stephan
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: only first query is reaaaal slow

    BTW, I assume you are using the default 250 page bufferpool ...

    Increase it by using the command

    alter bufferpool ibmdefaultbp size <numpages>

    each page is 4K ... This should help you to estimate the number of pages you should allocate based on you system's memory .. In any case, the bufferpool size cannot go more than 1.75 gigs (roughly) ...

    You should also increase the size of the dbheap ... I would suggest that you set it to, say 2048 or so , and increase further if it complains ...

    HTH

    Sathyaram


    Originally posted by soudmaijer
    DB-guru`s

    i`m running db2 v8.1 on a win2k server and my database is populating over 900.000 records. When I execute the first query DB2 takes almost 2 minutes to return the results, the next time i run the same query the results return in 2 secs. But after a few different queries, the same problem occurs.

    it`s probably a cache thing or not?

    maybe you guys can give me a pointer how to fix this anoying issue.

    best regards

    Stephan
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2004
    Posts
    2
    i tried the suggested but no results.

    when executing a query i can hear my harddisk performing a lot of IO.

    more suggestions?

  5. #5
    Join Date
    Sep 2002
    Posts
    456
    You might be using other bufferpools than default. If so and the tablespace your table resides in is assigned to that bufferpool then try changing the size of that bufferpool as well.

    In case you don't know the relationship...

    Bufferpool gets assigned to tablespace which in turn contains the tables.

    HTH

    dollar


    Originally posted by soudmaijer
    i tried the suggested but no results.

    when executing a query i can hear my harddisk performing a lot of IO.

    more suggestions?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by soudmaijer
    i tried the suggested but no results.

    when executing a query i can hear my harddisk performing a lot of IO.

    more suggestions?
    Well, since your data is actually stored on that disk you'll have to read it at some point, right?

    You might try using OPTIMIZE FOR ... ROWS clause in your SELECT - may be it will return first results faster.

    May be your query is worth looking at in terms of optimization. E.g. adding an index, reorganizing the table...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •