asked on September 14, 2022
Hi,
We have a ~1000 row MySQL database. We have a Data Server set up with an index pointing to a table in the database. Flex Pivot is set up to communicate with the Data Server.
We are seeing very slow loading times. It lists each of the fields and loads them individually taking around 45 seconds total to load this data.
Please let me know what strategies I can use to get this load time down. I do not think our end user is going to be ok with the speed. Luckily we are still just prototyping this new site out and the end user will not be seeing it until we get closer to the end goal.
We are not using this as a pivot table. We do not need any calculations and just want it to display the values like a spreadsheet.
Here is how we have the component set up:
mapping: {
id: {
type: "number",
},
field1: {
type: "string",
caption: "Field 1",
},
field2: {
type: "string",
caption: "Field 2",
},
field3: {
type: "number",
caption: "Field 3",
},
field4: {
type: "string",
caption: "Field 4",
},
field5: {
type: "string",
caption: "Field 5",
},
field6: {
type: "datetime",
caption: "Field 6",
},
field7: {
type: "datetime",
caption: "Field 7",
},
field8: {
type: "datetime",
caption: "Field 8",
},
field9: {
type: "datetime",
caption: "Field 9",
},
field10: {
type: "string",
caption: "Field 10",
},
},
},
slice: {
rows: [
{
uniqueName: "id",
},
{
uniqueName: "field2",
},
{ uniqueName: "field3" },
{ uniqueName: "field4" },
{ uniqueName: "field1" },
{ uniqueName: "field5" },
{ uniqueName: "field6" },
{ uniqueName: "field7" },
{ uniqueName: "field8" },
{ uniqueName: "field9" },
{ uniqueName: "field10" },
],
columns: [
{
uniqueName: "[Measures]",
},
Thanks,
Brian
⋅ September 15, 2022
Hello, Brian!
Thank you for your question.
Firstly, we would like to mention that using Flexmonster Data Server only for flat form would not significantly improve performance, as you still need to load the whole dataset into the component. For such cases, we recommend using a simpler way of connecting your database to Flexmonster - using a server-side script that will return the data from the database in JSON or CSV format.
Regarding performance figures, loading from similar-sized tables usually takes a couple of seconds. Flexmonster Data Server logs how much time was spent processing each request (when the LoggerMinLevel configuration option is set as "Info"). We recommend checking the response time details in the Flexmonster Data Server logs to find out which part of data loading causes the performance issues. You can find logs in the "Diagnostics" tab of the Flexmonster Admin Panel.
Looking forward to hearing your feedback.
Best Regards,
Maksym
⋅ September 15, 2022
Hi Maksym,
I see this message in the logs which is consistent with what I am seeing in the frontend.
Sep 15 17:28:04 flexmonster-ds fds: 2022-09-15 17:28:04.0665|INFO|Flexmonster.DataServer.Controllers.CubeController|Request starting HTTP/1.1 POST http://10.128.0.2:9500/select application/json 810
Sep 15 17:28:04 flexmonster-ds fds: 2022-09-15 17:28:04.1142|INFO|Flexmonster.DataServer.Controllers.CubeController|Request finished in 47.7142ms 200
⋅ September 15, 2022
It looks like it is sending a POST for each field we want to display that takes ~2 seconds. We are basically trying to display a flat list with no aggregations. Aside from the part I posted earlier, here is how the component is set up:
{
report: {
dataSource: {
type: "api",
url: process.env.VUE_APP_PIVOT_DATA_SOURCE_URL,
index: "index-name",
mapping: {<SEE EARLIER POST>},
},
slice: {<SEE EARLIER POST>},
options: {
editing: false,
grid: {
type: "flat",
showTotals: "off",
showGrandTotals: "off",
showFilter: true,
showHeaders: false,
dragging: false,
},
configuratorActive: false,
},
formats: [
{
name: "",
thousandsSeparator: "",
},
],
},
}
⋅ September 16, 2022
Hello, Brian!
Thank you for providing us with additional input.
We suppose the reason for the slow loading is a bad internet connection, or there might be an inbound/outbound limit imposed by your server, which causes the slowness.
From one of the provided lines of FDS logs, it is clear that the request is processed in milliseconds:
17:28:04.1142|INFO|Flexmonster.DataServer.Controllers.CubeController|Request finished in 47.7142ms 200
In the meantime, your screenshot from the "network" tab shows that the /select result takes 2.49 seconds to load.
We recommend testing the loading speeds with a better internet connection or lowering possible inbound/outbound limits on your server to achieve faster retrieval. Also, from our experience, selecting a server in the closest region could help save time on data transfer. Still, since you plan to use only the flat layout without any aggregations, our team recommends a different approach for loading the data from your database. Flexmonster Data Server delegates certain operations, such as aggregation, to the server-side, making it possible to work with larger data volumes using compact/classic layouts. When the flat layout is used, the data is shown as is (in raw format).
With this in mind, we believe the approach with the server-side script from our previous answer may work better for your use case (instead of Flexmonster Data Server). Also, using the JSON/CSV approach would mean that no additional requests will be sent during filtering and other operations.
Please let us know if our answer was helpful.
Best Regards,
Maksym
⋅ September 19, 2022
The issue was caused by a reverse proxy. It was completely unrelated to FlexMonster data server. Thank you so much for your help Maksym!
⋅ September 20, 2022
Hi, Brian!
Thank you for sharing your feedback.
Feel free to contact us if any other questions arise.
Best Regards,
Maksym