By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

Massive sql via WAN

P: n/a

Hello all

I have quite well working network connection. It's about 4 Mbit/s.
When I do transfer files I have the transfer like 400Kb/s.

The problem is that, if I'm doing massive , short sql statements to
remote db2 server I have very very poor performance.

Lets say I have file sql.sql which contains one thousand select
customer_numer from customer where customer_id = xxx;
then I'm connecting to remote server.
and running db2 -tf sql.sql

Whole process takes almost forewer. And te resulting datas are very
small! It's only thousand of integers. On the other hand, if I'm doing
one select which returns hundrets times more datas - it's fast - just
like the physical network limit.
What can I do to improve these small sql statements performance? Any
ideas are highly welcome!

Thanks a lot for any hint!
Kacper.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
How about use this?
select customer_numer from customer
where customer_id
IN (xxx, xxx,xxx,xxx,xxx, .....,xxx);

Repeat you are requesting thousand numbers at IN predicate.

Nov 12 '05 #2

P: n/a
Tonkuma wrote:
How about use this?
select customer_numer from customer
where customer_id
IN (xxx, xxx,xxx,xxx,xxx, .....,xxx);

Repeat you are requesting thousand numbers at IN predicate.

problem is that I have already legacy application - which was designed
to work in LAN environment. Nobody took care for much slower WAN
environment. There is no chance to change the application logic .
Thanks anyway!
Nov 12 '05 #3

P: n/a
Hi !

pawel <ka****@janczak.biz> schrieb:
Whole process takes almost forewer. And te resulting datas are very
small! It's only thousand of integers. On the other hand, if I'm doing
one select which returns hundrets times more datas - it's fast - just
like the physical network limit.
What can I do to improve these small sql statements performance? Any
ideas are highly welcome!


I would try to find out if the results of queries could be cached in any way.
You should monitor the sql-statements which are used by the
application - maybe you can use something like a "jdbc cache/proxy".
(Maybe you can find something like that at www.freshmeat.net)

mkdir /tmp/db2monitor
db2 connect to dbname user username using password
db2 update monitor switches using statement on
db2 create event monitor rkmon for statements write to file '/tmp/db2monitor'
db2 set event monitor rkmon state=1

* tracing the requests *

db2 set event monitor rkmon state=0
db2 terminate

db2 drop event monitor rkmon

Maybe another way is to set up a local db2-server which does
multi-master-replication to the remote site. I have no experience
with that - but maybe this is a option.

Best regards

Marc Schoechlin

--
I prefer non-proprietary document-exchange.
http://sector7g.wurzel6.de/pdfcreator/
http://www.prooo-box.org/
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.