Hello,
We are currently in the process of migrating our databases from a relic
of a server to a new 4 processor dual-core box with 4 gigs of RAM.
Overall, database performance is obviously dramatically improved.
However, I've noticed something odd with some of our larger tables
(which have between 1 and 1.5 million records). Performance can differ
greatly when performing simple LIKE queries such as the following:
select record_id from <tablenamewhere <fieldnamelike '%blah%'
Sometimes a result set will be returned very quickly (2 seconds or
almost immediately) while other times the exact same query will take 20
seconds to a minute. There does not appear to be any pattern to when
it is fast and when it is not. (I've done a re-org and runstats)
Now here's where it gets interesting. If I bring up task manager on
the server and monitor the cpu usage when doing such queries, on the
queries that return fast there is an expected large spike in the graph
across all the cpus. On the queries that take a long time, the cpu
usage appears to be very minimal (just little bumps).
Keep in mind that I am no DBA, just a java developer so maybe I'm
missing something really simple here or totally misinterpreting what I
see. Any suggestions on what is going on here would be greatly
appreciated, thanks! 4 3828
shawno wrote:
Hello,
We are currently in the process of migrating our databases from a relic
of a server to a new 4 processor dual-core box with 4 gigs of RAM.
Overall, database performance is obviously dramatically improved.
However, I've noticed something odd with some of our larger tables
(which have between 1 and 1.5 million records). Performance can differ
greatly when performing simple LIKE queries such as the following:
select record_id from <tablenamewhere <fieldnamelike '%blah%'
Sometimes a result set will be returned very quickly (2 seconds or
almost immediately) while other times the exact same query will take 20
seconds to a minute. There does not appear to be any pattern to when
it is fast and when it is not. (I've done a re-org and runstats)
Now here's where it gets interesting. If I bring up task manager on
the server and monitor the cpu usage when doing such queries, on the
queries that return fast there is an expected large spike in the graph
across all the cpus. On the queries that take a long time, the cpu
usage appears to be very minimal (just little bumps).
Sounds to me like the slower queries are IO bound ie are not finding the
data and/or index pages they need in cache and so are going to disk. The
faster ones are simply finding the data already in the cache so CPU time is
burning filtering rows for the LIKE clause.
Keep in mind that I am no DBA, just a java developer so maybe I'm
missing something really simple here or totally misinterpreting what I
see. Any suggestions on what is going on here would be greatly
appreciated, thanks!
Art S. Kagel
Hi,
That was my initial thought, but sometimes doing the exact same query
takes just a long as the first time. Other times if I try querying on
something I have not tried before, its blazing fast, and than the next
time (same query) its slow which is just the opposite of what you would
expect.
The columns I am querying are not indexed since I am performing a full
like ('%whatever%') and it is my understanding that DB2 will not use an
index for such a query since it would not help.
Art S. Kagel wrote:
Sounds to me like the slower queries are IO bound ie are not finding the
data and/or index pages they need in cache and so are going to disk. The
faster ones are simply finding the data already in the cache so CPU time is
burning filtering rows for the LIKE clause.
shawno wrote:
Hi,
That was my initial thought, but sometimes doing the exact same query
takes just a long as the first time. Other times if I try querying on
something I have not tried before, its blazing fast, and than the next
time (same query) its slow which is just the opposite of what you would
expect.
That can still happen, IF you are not the sole user of the system. So,
if another query from somewhere else does a full scan of the table, it
will put those rows into the db buffers, and when you run your first
new query, it will find all of the needed data in memory and run fast
(using lots of CPU). Then, some other query runs against other tables,
needs pages in the buffer, and so DB2 starts tossing data from you
table out of memory, and when you run the exact same query later, DB2
needs to pull that data back in off of disc, which is slow and doesn't
use as much CPU.
-Chris
Hi,
Actually, at this point I am the only user accessing the database as it
still in the development phase.
I did notice something else that maybe somebody can shed some light on
(maybe I'll start a new thread as well). In command editor, I tried
doing an 'execute and access plan' so that it created the little
flowchart diagram and there was a difference.
For the fast queries, the access plan looked like:
[database name] ---[tbscan(3) 531,908] ---[return(1) 531,908]
The slow queries were as follows:
[database name] ---[tbscan(5) 1,013,551] ---[tqueue(3)] --->
[return(1) 1,013,551]
The slower queries had a tqueue entry, whatever that is.
ChrisC wrote:
That can still happen, IF you are not the sole user of the system. So,
if another query from somewhere else does a full scan of the table, it
will put those rows into the db buffers, and when you run your first
new query, it will find all of the needed data in memory and run fast
(using lots of CPU). Then, some other query runs against other tables,
needs pages in the buffer, and so DB2 starts tossing data from you
table out of memory, and when you run the exact same query later, DB2
needs to pull that data back in off of disc, which is slow and doesn't
use as much CPU.
-Chris
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: |
last post by:
If I need to check if a certain value does exist in a field, and return
either "yes" or "not" which query would be the most effestive?
|
by: Harvey |
last post by:
Hi,
I try to write an asp query form that lets client search any text-string and
display all pages in my web server that contain the text. I have IIS 6.0 on a
server 2003. The MSDN site says...
|
by: Vito DeCarlo |
last post by:
Over the past week, I've been noticing that any websites (on this one
particular web server) built with ASP.NET have unusually slow (5 second)
response times when moving through the site. There...
|
by: Rob |
last post by:
I doubt this is the best way to do it, but what I came up
with was to hide the XML in an HTML Comment then edit the
file deleting the HTML stuff and keep the XML results. If
anyone has a better...
|
by: Buddy Ackerman |
last post by:
I'm trying to write files directly to the client so that it forces the client to open the Save As dialog box rather than
display the file. On some occasions the files are very large (100MB+). On...
|
by: MadMan2004 |
last post by:
Hello all!
I'm having a problem with a project I'm working on and I'd like to ask
for anyone's input that might be helpful. I'm building a rather large
front-end application connecting to an...
|
by: jim.clifford |
last post by:
Hello.
I have a slow response with a system that I am setting up. The OS is
Win 2000 Server with SQL Server 2000. My first execution of the SQL
procedure is slow (about 40 seconds), while the...
|
by: Kelii |
last post by:
I've been trying to get this piece to work for a few hours, but have
given up. I hope someone out there can help, I think the issue is
relatively straightforward, but being a novice, I'm stumped....
|
by: WuBin |
last post by:
Hi,
I have a GridVew and a sqlDtaSurce control in ASP.NET real-time page.
The sqlDtaSurce control use a complicated query to load data from a
SQL database.
This real-time page is reloaded by...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
| |