- Newest
- Most votes
- Most comments
Can you provide the SQL being executed? Keep in mind that redshift is a columnar database that is designed to be extremely fast at executing complex analytical workloads on very large datasets and rds MySQL is a row-oriented database that is designed to be very fast at transactional workloads such as a high volume single-row operations against modest sized data sets. Also keep in mind that depending on the command you’re executing, sporadic network congestion could be playing a role with the speed at which results are returned? Have you repeated the test at least three times to ensure consistency?
You are not doing a "select *" which in some cases can be an anti-pattern for columnar databases such as Redshift, you are instead picking a handful of specific columns that you want from the table - which is a best practice. 100k rows is a lot for any db to transmit over a jdbc connection let alone api call, but if you need to do it, so be it. I would suggest investigating if your redshift cluster is undersized by trying increasing the "horsepower" (i.e. node count/type or RPU)?
I've used a sql query like
Also there is a scenario I've been using my query with offset to load the entire data with pagination