How to select and export the large data in big database to excel

My task is selecting and exporting a large monitoring data from database to exceljs
My database have two big tables to store monitoring data.
- Table MonitoringDataInfo (~60M rows): have some fields id (pk), stationId, createdAt and one group index (stationId asc, createdAt desc)
- Table MonitoringData (~300M rows): have some fields id (pk), dataId, indicator, value, unit and one group index (dataId asc, indicator asc)
(id of MonitoringDataInfo is foreign key of dataId of MonitoringData)
I do a query below:

SELECT
  [Info].[id], [Info].[sentAt], [Data].[id] AS [Data.id], [Data].[indicator] AS [Data.indicator], [Data].[value] AS [Data.value]
FROM
  [monitoring_data_info] AS [Info]
LEFT OUTER JOIN
  [monitoring_data] AS [Data]
    ON [Info].[id] = [Data].[dataId]
WHERE
      [Info].[stationId] = N'EKTjhVrZibUE7h55b6tu'
  AND [Info].[createdAt] BETWEEN N'2021-10-07 07:14:51.000  00:00' AND N'2021-10-14 07:14:51.000  00:00'
ORDER BY
  [Info].[createdAt] ASC;

==> This query return 69253 rows after more than 10 minutes.

My Questions are:
- How should I do to optimize the execution time as well as database?
- Due to the slow execution time above, I have to do some further heavy task in nodejs to export to excelJs. Can you suggest the solution to handle this task?

Thanks in advand!

CodePudding user response：

Create an index on either of below columns

    id, stationId, createdAt, sentAt column of 
    monitoring_data_info table

   and  dataId column of monitoring_data table

depending upon the data you can have your indexes made especially on joined columns and where clause columns

Also, Dont overload the indexes too as i could see you have grouped index though it includes the necessary column but additional column in the group index may have cause to resort the data likewise would recommend to have index on just joined columns and sentAt as per your recent edit

CodePudding user response：

Is that query in Excel?

It might be faster to export the two sheets into database tables with minimal conversion or querying. Then do the Joining/Grouping in the database engine, which is more optimized for such.