All tests are were against the dev database through an RDS connection. Transaction filter tests used the /api/v2/download/count endpoint and the awards uses /api/v2/search/spending_by_award_count/. Tests against the response were done using postman, the test for sql queries were used with Postico.
Request
{"filters":{
"place_of_performance_locations": [
{
"country": "USA",
"state": "MD"
}
]
}
}
- Endpoint Time: 7s
- Query (~3s limit 100)
SELECT
"transaction_normalized"."id",
"transaction_normalized"."award_id",
"transaction_normalized"."usaspending_unique_transaction_id",
"transaction_normalized"."type",
"transaction_normalized"."type_description",
"transaction_normalized"."period_of_performance_start_date",
"transaction_normalized"."period_of_performance_current_end_date",
"transaction_normalized"."action_date",
"transaction_normalized"."action_type",
"transaction_normalized"."action_type_description",
"transaction_normalized"."federal_action_obligation",
"transaction_normalized"."modification_number",
"transaction_normalized"."awarding_agency_id",
"transaction_normalized"."funding_agency_id",
"transaction_normalized"."recipient_id",
"transaction_normalized"."description",
"transaction_normalized"."place_of_performance_id",
"transaction_normalized"."drv_award_transaction_usaspend",
"transaction_normalized"."drv_current_total_award_value_amount_adjustment",
"transaction_normalized"."drv_potential_total_award_value_amount_adjustment",
"transaction_normalized"."last_modified_date",
"transaction_normalized"."certified_date",
"transaction_normalized"."create_date",
"transaction_normalized"."update_date",
"transaction_normalized"."fiscal_year"
FROM
"transaction_normalized"
LEFT OUTER JOIN
"references_location"
ON (
"transaction_normalized"."place_of_performance_id" = "references_location"."location_id"
)
WHERE
(
"references_location"."state_code" = 'MD'
AND "references_location"."location_country_code" = 'USA'
)
Request
{"filters":{
"recipient_locations": [
{
"country": "CAN"
}
]
}
}
- Time: <1s
- Query: ~17s limit 100 (dev was running slow at time of request, should retest)
SELECT
"transaction_normalized"."id",
"transaction_normalized"."award_id",
"transaction_normalized"."usaspending_unique_transaction_id",
"transaction_normalized"."type",
"transaction_normalized"."type_description",
"transaction_normalized"."period_of_performance_start_date",
"transaction_normalized"."period_of_performance_current_end_date",
"transaction_normalized"."action_date",
"transaction_normalized"."action_type",
"transaction_normalized"."action_type_description",
"transaction_normalized"."federal_action_obligation",
"transaction_normalized"."modification_number",
"transaction_normalized"."awarding_agency_id",
"transaction_normalized"."funding_agency_id",
"transaction_normalized"."recipient_id",
"transaction_normalized"."description",
"transaction_normalized"."place_of_performance_id",
"transaction_normalized"."drv_award_transaction_usaspend",
"transaction_normalized"."drv_current_total_award_value_amount_adjustment",
"transaction_normalized"."drv_potential_total_award_value_amount_adjustment",
"transaction_normalized"."last_modified_date",
"transaction_normalized"."certified_date",
"transaction_normalized"."create_date",
"transaction_normalized"."update_date",
"transaction_normalized"."fiscal_year"
FROM
"transaction_normalized"
LEFT OUTER JOIN
"legal_entity"
ON (
"transaction_normalized"."recipient_id" = "legal_entity"."legal_entity_id"
)
LEFT OUTER JOIN
"references_location"
ON (
"legal_entity"."location_id" = "references_location"."location_id"
)
WHERE
"references_location"."location_country_code" = 'CAN'
Award tests are done with /api/v2/search/spending_by_award_count/ endpoint
Request
{"filters":{
"place_of_performance_locations": [
{
"country": "USA",
"state": "VA"
}
]
}
}
- Response: 2s
- Query: <1s Limit 100
SELECT
"awards"."id",
"awards"."data_source",
"awards"."type",
"awards"."type_description",
"awards"."category",
"awards"."piid",
"awards"."parent_award_id",
"awards"."fain",
"awards"."uri",
"awards"."total_obligation",
"awards"."total_outlay",
"awards"."awarding_agency_id",
"awards"."funding_agency_id",
"awards"."date_signed",
"awards"."recipient_id",
"awards"."description",
"awards"."period_of_performance_start_date",
"awards"."period_of_performance_current_end_date",
"awards"."place_of_performance_id",
"awards"."potential_total_value_of_award",
"awards"."base_and_all_options_value",
"awards"."last_modified_date",
"awards"."certified_date",
"awards"."create_date",
"awards"."update_date",
"awards"."latest_transaction_id",
"awards"."total_subaward_amount",
"awards"."subaward_count"
FROM
"awards"
LEFT OUTER JOIN
"legal_entity"
ON (
"awards"."recipient_id" = "legal_entity"."legal_entity_id"
)
LEFT OUTER JOIN
"references_location"
ON (
"legal_entity"."location_id" = "references_location"."location_id"
)
WHERE
(
"awards"."latest_transaction_id" IS NOT NULL
AND "awards"."category" IS NOT NULL
AND "references_location"."state_code" = 'MD'
AND "references_location"."location_country_code" = 'USA'
)
Request
{"filters":{
"recipient_locations": [
{
"country": "USA",
"state": "GA",
"county": "121"
}
]
}
}
Results
- < 1s
- Query (<1s limit 100):
SELECT
"awards"."id",
"awards"."data_source",
"awards"."type",
"awards"."type_description",
"awards"."category",
"awards"."piid",
"awards"."parent_award_id",
"awards"."fain",
"awards"."uri",
"awards"."total_obligation",
"awards"."total_outlay",
"awards"."awarding_agency_id",
"awards"."funding_agency_id",
"awards"."date_signed",
"awards"."recipient_id",
"awards"."description",
"awards"."period_of_performance_start_date",
"awards"."period_of_performance_current_end_date",
"awards"."place_of_performance_id",
"awards"."potential_total_value_of_award",
"awards"."base_and_all_options_value",
"awards"."last_modified_date",
"awards"."certified_date",
"awards"."create_date",
"awards"."update_date",
"awards"."latest_transaction_id",
"awards"."total_subaward_amount",
"awards"."subaward_count"
FROM
"awards"
LEFT OUTER JOIN
"legal_entity"
ON (
"awards"."recipient_id" = "legal_entity"."legal_entity_id"
)
LEFT OUTER JOIN
"references_location"
ON (
"legal_entity"."location_id" = "references_location"."location_id"
)
WHERE
(
"awards"."latest_transaction_id" IS NOT NULL
AND "awards"."category" IS NOT NULL
AND "references_location"."county_code" = '121'
AND "references_location"."state_code" = 'GA'
AND "references_location"."location_country_code" = 'USA'
)