Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (hermes.apache.org: local policy)
User-Agent: Microsoft-Entourage/11.1.0.040913
Date: Fri, 17 Jun 2005 11:46:35 +1000
Subject: OT: COUNT query speed issue
From: Zoran Avtarovski <zoran@sparecreative.com>
To: "user-java@ibatis.apache.org" <user-java@ibatis.apache.org>
Message-ID: <BED8689B.100A6%zoran@sparecreative.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit

I have a quick query regarding the speed of count query I use to generate a
chart, which is too slow at present and I'm looking for suggestions to speed
it up.

I pass a collection of the last 30 days data usage to a simple Flash
charting app and I use the ibatis query below  in a loop thirty times to get
the data for each successive day. The table has about half a million entries
and will only be getting bigger.

SELECT COUNT(*) AS count FROM log  WHERE remote_id = #remoteId# and log_time
BETWEEN #lowDate# AND #highDate#


The complete query (all thirty days) takes over a minute to complete, which
causes timeouts. I did some profiling and the bottle neck is with the above
query. Clearly, I'm doing something wrong. Is there a better formed query
that will improve speed or should I be  looking at a different approach to
this issue?

I know this sounds stupid but I'm looking for pointers for best practice so
I don't have to revisit this issue down the track.


Zoran