JDBC Connector Performance Tuning
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
JDBC Connector Performance Tuning
Hi All,
We are using JDBC Connector to connect to the remote Database using oracle thin client using Type 4 connection.
When we try to extract the data from the Database....the fetch or rows/sec is very low(800-1000 rows/sec).
Properties:
FetchSize:2000
HeapSize:256
Kindly let me know your thoughts to increase the performance of the data extraction.
Thanks,
Satheesh.R
We are using JDBC Connector to connect to the remote Database using oracle thin client using Type 4 connection.
When we try to extract the data from the Database....the fetch or rows/sec is very low(800-1000 rows/sec).
Properties:
FetchSize:2000
HeapSize:256
Kindly let me know your thoughts to increase the performance of the data extraction.
Thanks,
Satheesh.R
You need to determine the bottleneck. Rather than a magic setting in the connector it could be the source database, complexity of the source SQL, network issues or just how long it takes to write to the target. Eliminate each one by one.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Hi paul,
Can we use partition read for non-partitioned tables.When I try to read data on partitioned read, the count shows a huge variation and it fetches much faster 25k rows/sec. But..i am really worried about the huge count mismatch.
Kindly let me know your thoughts on the same.
Regards,
Satheesh.R
Can we use partition read for non-partitioned tables.When I try to read data on partitioned read, the count shows a huge variation and it fetches much faster 25k rows/sec. But..i am really worried about the huge count mismatch.
Kindly let me know your thoughts on the same.
Regards,
Satheesh.R
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
Hello Satheesh, you can use partition reads on non-partitioned tables for sure, there are partions methods like round-robin which divides the table evenly.
You just need to be aware of your ETL design, some stages need specific partition types to work, so if you have a complex ETL design, you data may need be repartitioned, leading to new bottlenecks.
But if its a simple source->target design, it will be fine. Just make a validation/count on target based on some key after the operation to check.
You just need to be aware of your ETL design, some stages need specific partition types to work, so if you have a complex ETL design, you data may need be repartitioned, leading to new bottlenecks.
But if its a simple source->target design, it will be fine. Just make a validation/count on target based on some key after the operation to check.
Leandro Vieira
Data Expert - Brasilia, Brazil
Data Expert - Brasilia, Brazil
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Check with your support provider to determine if there are any known issues with partitioned read in the JDBC Connector.
Add $CC_MSG_LVL to your job parameters and set it to 1, 2, or 3 to enable some connector tracing to be recorded in the job log. I usually start with a setting of 2. You may have to add it as a user defined project environment variable if you have not done so previously (leave the default empty so as not to generate excessive logging across your entire project).
In the job log you will see the actual query that was passed to the database. A partitioned read usually injects some additional logic in the WHERE clause. If you have anything more complex than a single table SELECT, the partitioned read may not work properly. You can determine that by evaluating the generated query.
Mike
Add $CC_MSG_LVL to your job parameters and set it to 1, 2, or 3 to enable some connector tracing to be recorded in the job log. I usually start with a setting of 2. You may have to add it as a user defined project environment variable if you have not done so previously (leave the default empty so as not to generate excessive logging across your entire project).
In the job log you will see the actual query that was passed to the database. A partitioned read usually injects some additional logic in the WHERE clause. If you have anything more complex than a single table SELECT, the partitioned read may not work properly. You can determine that by evaluating the generated query.
Mike
Start to debug in a phased approach.
chop your job up into pieces.
Oracle Connector -> dataset
Oracle Connector -> sequential file
With 1000 rows in your source table, I would have expected maybe 4000 rows on your output if your partitioning was entire, but ... 5000ish?!? that is odd.
When faced with an issue, I like to break it down into it's smallest components. That way you remove "noise" from the equation. If it's your Oracle read that you suspect is wrong, then just do a read in your job, no other transforms, etc...
chop your job up into pieces.
Oracle Connector -> dataset
Oracle Connector -> sequential file
With 1000 rows in your source table, I would have expected maybe 4000 rows on your output if your partitioning was entire, but ... 5000ish?!? that is odd.
When faced with an issue, I like to break it down into it's smallest components. That way you remove "noise" from the equation. If it's your Oracle read that you suspect is wrong, then just do a read in your job, no other transforms, etc...