Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SQL database limitations, how to break down to smaller queries and combine data

Hello everyone,

Im trying to retrieve test data from SQL server using a query below (simplified a little, actual query uses more filters). SQL table contains millions of rows

 

 

let
 Source = Sql.Database("DATABASE", "DATABASE",
Query="SELECT 
TEST.STATION,
TEST.START_DATE_TIME,
TEST.DATA
FROM TEST
WHERE TEST.STATION = '"&Station&"'
AND TEST.START_DATE_TIME >= DATEADD(day, -"&DaysStart&", GETDATE())
AND TEST.START_DATE_TIME < DATEADD(day, -"&DaysEnd&", GETDATE())"])
in
    Source

 

 

 

However is I select date interval to a higher value, query gets stuck on evaluating state. This might be a server memory limitation.

 

I can solve this issue by creating several smaller queries with lesser date intervals and appending them. Also I can create one query with multiple SELECT statements and UNION ALL in between them. Both options work, but the query code gets realy big and not easily editable.

 

Is there a way to loop the query, iterate date parameter on each pass and append all data? I couldn't find a simple way in both M and SQL for this. Hope you can help me.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I found a solution by myself. I created a function:

let Loaddata= (Days as text) =>
let
 Source = Sql.Database("DATABASE", "DATABASE",
Query="SELECT 
TEST.STATION,
TEST.START_DATE_TIME,
TEST.DATA
FROM TEST
WHERE TEST.STATION = '"&Station&"'
AND TEST.START_DATE_TIME >= DATEADD(day, -"&Days&", GETDATE())
AND TEST.START_DATE_TIME < DATEADD(day, -"&Days&"+1, GETDATE())"])
in
    Source
in Loaddata

 

And used it as a custom column for list. After expanding it I got the wanted result

pbix.JPG 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

A database is a piece of software that runs on a computer, and is subject to the same limitations as all software—it can only process as much information as its hardware is capable of handling. The way to make a query run faster is to reduce the number of calculations that the software (and therefore hardware) must perform. To do this, you'll need some understanding of how SQL actually makes calculations. First, let's address some of the high-level things that will affect the number of calculations you need to make, and therefore your querys runtime:

  • Table size: If your query hits one or more tables with millions of rows or more, it could affect performance.
  • Joins: If your query joins two tables in a way that substantially increases the row count of the result set, your query is likely to be slow. There's an example of this in the subqueries lesson.
  • Aggregations: Combining multiple rows to produce a result requires more computation than simply retrieving those rows.

Query runtime is also dependent on some things that you can't really control related to the database itself:

  • Other users running queries: The more queries running concurrently on a database, the more the database must process at a given time and the slower everything will run. It can be especially bad if others are running particularly resource-intensive queries that fulfill some of the above criteria.
  • Database software and optimization: This is something you probably can't control, but if you know the system you're using, you can work within its bounds to make your queries more efficient.
Anonymous
Not applicable

I am aware of the server limitations so I am trying to reduce the server calculations needed for queries.

 

As I said before I would like to loop the query, iterate date parameter on each pass and append all data. How could this be achieved using power BI?

Anonymous
Not applicable

I found a solution by myself. I created a function:

let Loaddata= (Days as text) =>
let
 Source = Sql.Database("DATABASE", "DATABASE",
Query="SELECT 
TEST.STATION,
TEST.START_DATE_TIME,
TEST.DATA
FROM TEST
WHERE TEST.STATION = '"&Station&"'
AND TEST.START_DATE_TIME >= DATEADD(day, -"&Days&", GETDATE())
AND TEST.START_DATE_TIME < DATEADD(day, -"&Days&"+1, GETDATE())"])
in
    Source
in Loaddata

 

And used it as a custom column for list. After expanding it I got the wanted result

pbix.JPG 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.