Forum Discussion
How do native queries effect evaluation during load and refresh?
Hello,
I am using the Odbc.Query to access a vendor's MySQL database. My query looks something like this:
SELECT x.a, x.b, z.c, z.d, w.e, w.f, z.g
FROM z
JOIN y ON y.a = z.a
JOIN x ON x.b = z.b
JOIN w ON w.b = z.b
WHERE z.c = 1
AND z.a IS NOT NULL
AND w.e IS NOT NULL
AND z.g = 2
AND CHAR_LENGTH(w.f) = 3
The tables are pretty big, and I expect tens of thousands of rows as the result of this query.
The thing is, that if I run something like above, Power BI sits on 'Evaluating' forever. If I add a limit clause, like 'LIMIT 20000', it runs pretty quickly. Confusingly other queries I run return millions of rows and I don't have this issue with all of them
Can someone explain to me why this is? I'd like to optimize my queries for Power BI, but I don't understand what's happening during evaluation.
Thanks
1 Reply
I'd avoid running native queries if I can. If you want to leverage your database's capabilities, create views in MySQL that are optimized for BI purposes. Also, from the multiple joins in your query I wonder about what you're trying to load in Power BI. Are you trying to create on big table, or is this part of a star schema?
