Taking too much time to load data

[javvaji]

Hi,

I am loading data from Netezza to Vertica Database, i am having 1.3cr records of data it was taking almost 2hr to load data.

I tried using bulk load,i also enabled the cursor option in NetezzaInput component.

And i also tried to load data using Dynamic data type option in Edit schema i was getting the following error

org.netezza.error.NzSQLException: An existing connection was forcibly closed by the remote host

can any one can help me on this error and also any other option to load data faster.

Thanks,

Bharath.

[javvaji]

OK.

Thank you!! for spending your valuable time. Please let me know if you get anything positive.

Thanks,

Bharath.

[TRF]

First, regarding the cause of the NzSQLException, maybe a timeout.
I've encountered a similar issue with Salesforce in the past. The operations triggered by the tSalesforceOutputBulkExec were so slow that at a moment the component failed with a timeout exception.
As you are facing a response time issue, maybe it should be a trail to follow. What happens if you reduce the number of records you try to get from Netezza?

Second, even if the case is not solved, my opinion is that explainations are so detailed and meaningful that you should give him some kudos for it's time.

[javvaji]

Hi,

Thanks for your response.

When tried to load data by reducing the records it works fine, but if we don't do that i am getting this exception.

Thanks,

Bharath.

[TRF]

Hi,

Thank's for the Kudo even if I was talking about @rhall effort!

So, it seems the trail I suggested is not so bad

Don't know the specificities of tELTNetezza components and how to optimize query or work with subset and iterate to get the expected result.

[javvaji]

Hi,

I am not using tELTNetezza i am using ETL component only. And i was using Dynamic datatype to load data.

So could you please help me on this.

Thanks,

Bharath.

[cterenzi]

If you're running into a connection timeout, you can try to break up your data set into smaller files and load each separately.

[TRF]

OK, Netezza components were not visible in my palette, now they are.

As I suggest in my previous answer, 1st thing to do is to optimize the query.

Then you may also try to retrieve subset of rows instead of all rows at the same time.

Playing with "LIMIT" you can decide how many rows to select at a time and playing with "OFFSET" you decide how many rows to ignore.

So, you can select rows 1-100000 then 1000001-2000000 and so on.

Just add a loop over the "select" operation, and use the change the query at each time to get the desired values for the OFFSET/LIMIT parameters.

[javvaji]

Thanks for your reply.

But there is nothing to optimize the query,it is just direct load from netezza to vertica.

There are many other tables which needs to be automated.I think in this scenario limit will not work.

Please correct me if i am wrong.

Thanks,

Bharath.

[TRF]

OK, nothing to optimize, it's possible.

However, why do you think the proposed scenarion will not work?

In fact, I think it should, you just have to set the limit and offset values to minimize the number of queries to get all the expected rows.

Now, how these parameters are processed by the db engine? 

If querying all the rows is not necessary to apply the offset, you should have not too bad response time.

[javvaji]

OK.

If we use offsets we cannot automate the jobs right,we need to do it manually.We exactly don't know what is the row count of table, so in this case what will be the solution.Please correct me if i am wrong.

Thanks,

Bharath.