Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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:
Query runtime is also dependent on some things that you can't really control related to the database itself:
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?
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |