SOLVED

Expensive Query execution need for Customer report

Copper Contributor

Hello - We are using Azure sql and cloud services. I have the use case where customer should extract the report where yearly data will be included. This is to be dealt with a couple of sql tables where the query will tend to run for a longer period which may affect the performance of the system considering CPU and DTU utilization.  I know that there are areas that I need to see how I can alter the system design (live product) which doesn't have table partitioning. It holds millions of data without partition. 

 

Are there any recommendation or best practices on how large data can be pulled for customer's report?

4 Replies

It holds millions of data without partition. 


@Bremanand , I have a table with 1.5 billion records and no performance issues; SQL Server can handle large data amount without any problem.

The rest of your post is to broad and to vague to give an answer on, please specify and provide more informations.

Assume, there is a sql query that fetches more volume of data. This query will be called through a backend API services to allow customers to download the data in form of json or csv, etc. My question here - how is this report extraction possible without impacting other regular users who uses application? This sort of cost query operation will utilize more server resources which may slowdown the application for other users. Does this answer your question?
best response confirmed by Bremanand (Copper Contributor)
Solution
Hello
if you don't want to have an impact on users. Have you consider creating a SSRS subscription to create the report at night when less or no users are hitting the Azure SQL DB ?

Another possibility is to use Data Virtualization in Azure SQL DB (which is currently in Private Preview https://www.youtube.com/watch?v=oYXCknUvdLA ) with this you can export your table/query to ADSL2 in csv or parquet format

Data Virtualization is Available in SQL Server 2022 and Azure SQL Managed Instance

Regards
Javier
Hi - I do have this mind on running such expensive queries during off business hours. Thanks for the suggestion.
I will explore more on sql data virtualization concept. Hope, it will help to answer my below queries when I learn this more.
1. How virtaul table on external data store can be set up? like azure datalake or storage accounts.
2. How it maintains the concurrency of real data which needs to be reflected in external storage account ?
3. Will it add any performance problem when DML operation happens on real sql tables as it got extra job to sync to external storage accounts.
1 best response

Accepted Solutions
best response confirmed by Bremanand (Copper Contributor)
Solution
Hello
if you don't want to have an impact on users. Have you consider creating a SSRS subscription to create the report at night when less or no users are hitting the Azure SQL DB ?

Another possibility is to use Data Virtualization in Azure SQL DB (which is currently in Private Preview https://www.youtube.com/watch?v=oYXCknUvdLA ) with this you can export your table/query to ADSL2 in csv or parquet format

Data Virtualization is Available in SQL Server 2022 and Azure SQL Managed Instance

Regards
Javier

View solution in original post