Timeline for How to build reports for an ecommerce webapp without slowing it down
Current License: CC BY-SA 4.0
18 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 31, 2018 at 14:51 | comment | added | Dan Pichelman | 400,000 records is very small. You might want to verify which indices are being used. As for my 2nd question above, I was assuming you had 10 million orders and perhaps your query would benefit from moving the old records to another table. | |
| Jul 31, 2018 at 14:30 | comment | added | Abhinav | @DanPichelman Around 400,000 records I have. Pardon me, I do not understand your second question. | |
| Jul 31, 2018 at 14:29 | comment | added | Abhinav | @nikhiljhaveri I haven't been doing this in Java because I felt like I need to fetch more records and then filter from them which is unnecessary if I could fetch only the required data. And I haven't tried because in this particular case, I do not believe that I can gain much. But still I will give it a try. thanks | |
| Jul 31, 2018 at 13:43 | comment | added | Dan Pichelman | How many records are in your orders table? Do all of them still need to be there? | |
| Jul 31, 2018 at 10:24 | comment | added | nikhil jhaveri | Also, if doing this in Java is not possible have you considered creating views for the queries that are required to build the report. These will be easily queried, as they will be another abstraction of the query, and they can be indexed as per your needs. | |
| Jul 31, 2018 at 8:55 | comment | added | nikhil jhaveri | Is it not possible to do the data filtering and conversion in Java? Java 8 has some very nice concurrent utilities and parallel streams with filters that will easily trump MySql in performance. | |
| Jul 31, 2018 at 5:24 | comment | added | Ewan | it does seem slow but you don't provide enough info for us to optimise the query for you | |
| Jul 31, 2018 at 4:19 | comment | added | Abhinav | I have edited my question. Added tests of different queries without timezone conversion | |
| Jul 31, 2018 at 4:18 | history | edited | Abhinav | CC BY-SA 4.0 | added 14 characters in body |
| Jul 31, 2018 at 4:08 | history | edited | Abhinav | CC BY-SA 4.0 | Added modified queries and their response time |
| Jul 30, 2018 at 15:59 | answer | added | Ewan | timeline score: 1 | |
| Jul 30, 2018 at 15:53 | comment | added | Ewan | for your sample query, store the date in utc and lose the timezone conversion | |
| Jul 30, 2018 at 14:15 | answer | added | Dan Pichelman | timeline score: 2 | |
| Jul 30, 2018 at 12:34 | comment | added | Abhinav | Edited my question and added sample query, please check | |
| Jul 30, 2018 at 12:34 | history | edited | Abhinav | CC BY-SA 4.0 | Added a sample query |
| Jul 30, 2018 at 12:24 | comment | added | Bart van Ingen Schenau | How slow is slow? How long does it take to get your query results, 1 sec, 10 sec, 1 minute, more? Are your queries returning lots of excess data? For example, all orders even if you only need those of last month or you need just the number of records? | |
| Jul 30, 2018 at 12:10 | comment | added | Dave | Your last point seems to be key If I run these queries directly on RDS using MySql workbench then also it is slow.. That said showing the table structure and queries might help us give you some pointers. | |
| Jul 30, 2018 at 12:07 | history | asked | Abhinav | CC BY-SA 4.0 |