I have a large query that joins numerous tables and runs a bunch of calculations to retrieve YoY Sales Data from 2020 to date. When I run the full query from our production database it takes about 40 seconds to execute and returns about 700,000 records. This report contains line item invoice/credit memo data by customer for all invoice/CM documents, so as a view it has a lot of powerful metrics to work off of. My goal is to optimize the time it takes to run the query so I can use the query as a view to source BI reports off of. (Not Power BI--we use another BI tool called Dashboard Fox.)
I insert all of the records into a datamart so that SQL can pull the hard-coded values and not have to process any joins or calculations. This should help immensely with performance and I should in theory be able to pull the datamart table in a matter of seconds. But the datamart table still takes 30 seconds to return a SELECT * query. I've looked at the execution plan and can confirm that all it's doing is a table scan, whereas the original production query execution plan was complex.
I was expecting much more improvement from the production database view.
Is it normal for 700,000 records to take 30 seconds to return?
How can I pull this data faster for dynamic reporting solutions?
Try a query that doesn't return 700,000 rows to the client, like a GROUP BY query that returns fewer rows. And store the table as a clustered columnstore which provide excellent compression and super-fast scanning.