Does using stored procedures with Amazon Aurora reduce costs and/or speed up response time?

Forgive the length of this answer, this kind of question will have many different answers/solutions, I'm trying to remain as general as I can...

The Structured Query Language, by design, is able to query relational data across multiple tables. You know this because you've created a Stored Procedure to do so (I'm assuming you're using the JOIN syntax to retrieve relational data using Primary Keys & Conditions).

I can't think of a reason (off hand) that would prevent you from being able to execute the same query from within your Application as a single database call. In fact, I would expect the query to perform ever so slightly quicker from your Application because you haven't included the overhead of calling the Stored Procedure in the middle. However, when it comes down to general read performance and how the DBMS executes the query, you should not notice any difference in query execution as the query will be executed the same way.

I come from a PHP background and, although there are specific situations where multiple DB connections may be required, in general would expect a Web Application to start/maintain a single database connection between each running Script and the DBMS. It's established practice nowadays that once a script has begun, it will open a DB connection when required that will remain open for the duration of the script's execution. Yes, you could take the approach that you only open/maintain a DB connection while you actually need to use it but unless your scripts are running for longer that the seconds needed to handle a HTTP request in my opinion you are adding unnecessary overhead to your applicaiton.

The first thing that came to mind while reading your question was "Cache". By default, nearly all DMBS will automatically cache individual DB queries until a period of time has passed or the service is told to ignore the cache or the underlying data is changed. This is automatic behaviour designed to reduce the amount of disk reads over time (cache being stored in system memory). So from the point of view of an application developer, you would expect repeat calls of the same Query to return faster because the hard work was done when the cache was populated.

However, although the query cache will take the load off the backend storage, it does nothing to reduce the number of times the application servers will need to query the DMBS for the data. At this level you should start thinking about using a caching service like ElastiCache to hold frequently accessed non-volatile data and save the servers having to query the DBMS at all. In a well designed solution, where you are caching data that doesn't frequently change, this implementation will dramatically reduce Query load on the DBMS and should also improve application performance due to faster turnarounds.

This answer is already turning into an essay and I'm getting hungry so if you don't mind I'll be a little more flippant with the rest of it:

• Should I be concerned about I/O operation charges in an average DB? No, the charges will be negligible and dwarfed by the server costs
• Will using Stored Procedures save me money? No, they were designed for specific situations (that I won't go into) but in your case they are no different to executing queries on the DB.
• Will the travel time of each query have a material impact on my application's performance? No, assuming the DB is located in the same region and your VPC design is sufficiently optimised, you should be able to perform hundreds of queries in each script without breaking a sweat.