Archives of the TeradataForum

Message Posted: Sun, 20 Apr 2008 @ 09:33:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Bill,

I bet this rate was pretty disappointing when you first fired the process up.

At this low rate (4 records per second * 15 queries per record = 60 queries per second) it seems like you are processing in a serial fashion. There could be many reasons for this. Here are a couple:

1) Part of your process is getting a table level lock

2) You are using a single session and one query at a time. Or your load process is being overwhelmed by other system activity and not getting "a look in".

There are many different approaches you could take, but remember TD is an MPP database if any of the above is occurring you are not leveraging the MPP capabilities.

Here are a couple of ideas - without more knowledge as to what is going on, it is difficult to suggest any particular solution, so these are all generic thoughts.

1) Use multistatement requests - this gets multiple queries happening at one time. You could use a traditional multistatment request e.g. "query 1; query 2; query 3;..." etc. a macro and/or a stored procedure. The goal here is to minimise the comms between your client and TD.

2) Use prepared statements (a.k.a. query cache or "Using"). Since your queries are unchanged (except for parameter values) prepare your query. If you don't you have the overhead of parsing, etc each time you submit it. This overhead typically isn't great, but it adds up when you do it over and over.

3) Submit multiple requests per query (e.g. if you use the macro approach include multiple execs per request). If you are using prepared queries you will be using batches. Look at java.sql.PreparedStatement.addBatch() and java.sql.Statement.executeBatch ().

4) Use multiple sessions. This gets even more throughput - you will need to understand how to work with threads for this one.

5) Ensure all your write operations are getting row hash locks and any read operations are row hash and/or locking for access.

6) Ensure that your load process isn't being overwhelmed by other system activity. Look at priority scheduler settings. Also if the system is heavily loaded, you might need to look at other system utilisation settings.

There are lots of other things that could be examined, but the above list represents a good starting point.

Hope this helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>