Getting error when Calling "select * from PaymentItem"

Rachel Bender

I'm trying to get the total number of records in PaymentItem, and also get the first record, so I can break it down and import the records in chunks of 100. However, I'm getting an error when calling "select * from PaymentItem". PHP Error[2]: SoapClient::__doRequest(): SSL: Connection reset by peer I have a hunch it's related the number of records in the table, as I think I've seen this before, intermittently, with large datasets. I'm with the Autism Speaks organization. Let me know if you need anything else to troubleshoot, and thanks.

Eric Pecoraro

Rachel Bender:
I'm trying to get the total number of records in PaymentItem, and also get the first record, so I can break it down and import the records in chunks of 100. However, I'm getting an error when calling "select * from PaymentItem". PHP Error[2]: SoapClient::__doRequest(): SSL: Connection reset by peer I have a hunch it's related the number of records in the table, as I think I've seen this before, intermittently, with large datasets. I'm with the Autism Speaks organization. Let me know if you need anything else to troubleshoot, and thanks.

I just ran some tests on this exact query across a few of our clients. The API is running EXCEEDINGLY slow.



Queries are taking about 25 times longer than normal on this particular query (at this time), so it must be a system wide problem.



I would recommend opening up a ticket with Blackbaud (ask that it be escalated to the API dev staff) on this if it persists.



I can provide real-world clients to the BB support staff if necessary to validate this issue (should it persist).

Rachel Bender

Eric Pecoraro:
I just ran some tests on this exact query across a few of our clients. The API is running EXCEEDINGLY slow.



Queries are taking about 25 times longer than normal on this particular query (at this time), so it must be a system wide problem.



I would recommend opening up a ticket with Blackbaud (ask that it be escalated to the API dev staff) on this if it persists.



I can provide real-world clients to the BB support staff if necessary to validate this issue (should it persist).

OK, how do I open up a ticket?

Eric Pecoraro

Rachel Bender:
OK, how do I open up a ticket?

From here:



https://www.blackbaud.com/support/support.aspx



Click "Manage Your Cases", then "Create a Case".



Unfortunately there is no direct support for the API anymore [sigh], so you will have to make it clear that this is a technical issue concerning the API and needs an engineer/developer, elsewise you my go back & forth explaining the situation ad nauseum.



You will need to explain clearly that you are fairly certain that this is SYSTEM WIDE API bug, verified across multiple accounts (I can validate if necessary).

Rachel Bender

Eric Pecoraro:
From here:



https://www.blackbaud.com/support/support.aspx



Click "Manage Your Cases", then "Create a Case".



Unfortunately there is no direct support for the API anymore [sigh], so you will have to make it clear that this is a technical issue concerning the API and needs an engineer/developer, elsewise you my go back & forth explaining the situation ad nauseum.



You will need to explain clearly that you are fairly certain that this is SYSTEM WIDE API bug, verified across multiple accounts (I can validate if necessary).

Thanks, Eric, always appreciated.

Arturo Escobedo

Rachel Bender:
I'm trying to get the total number of records in PaymentItem, and also get the first record, so I can break it down and import the records in chunks of 100. However, I'm getting an error when calling "select * from PaymentItem". PHP Error[2]: SoapClient::__doRequest(): SSL: Connection reset by peer I have a hunch it's related the number of records in the table, as I think I've seen this before, intermittently, with large datasets. I'm with the Autism Speaks organization. Let me know if you need anything else to troubleshoot, and thanks.

Can you try getting a smaller dataset. For example SELECT * FROM PaymentItem WHERE DATEUPDATED > '2/1/2001' and DATEUPDATED <= '1/1/2005' . Otherwise you are generating a several million dataset and retrieving 100 at a time through the web service. In general a connection reset by peer error is caused by a server dropping your connection for one reason or another, error message is very general though.

Eric Pecoraro

Arturo Escobedo:
Can you try getting a smaller dataset. For example SELECT * FROM PaymentItem WHERE DATEUPDATED > '2/1/2001' and DATEUPDATED <= '1/1/2005' . Otherwise you are generating a several million dataset and retrieving 100 at a time through the web service. In general a connection reset by peer error is caused by a server dropping your connection for one reason or another, error message is very general though.

Here are some queries against a db w/ about 2MM records 

SELECT * FROM PaymentItem 

25 sec response, rec count 1,898,936 

SELECT * FROM PaymentItem WHERE DateUpdated > '5/31/2014' AND DateUpdated < '7/1/2014'

15 sec response, rec count 5,198 

SELECT * FROM PaymentItem WHERE PaymentID > 161571693 

1 sec response, rec count 25,675 

Seems to be a db optimization problem. While the second query is 10 seconds faster per 100 records, it still is not practical to iterate over an entire database...and using DateUpdated requires additional programming/logic.