Forum Discussion
Connection Only or Load as Table and queries
That's correct. Once you are all loaded and the editor is closed, it's only the Connection Queries that cannot be refreshed.
--Nate
It's does refresh, but only when you refresh the table that loads from the connection file, and only for what is needed for that file. If you have a connection file with no filters, and then three files that reference the connection file but then filter by region, when your files are loaded, and then you refresh only the West Region, the connection file (assuming query folding) query plan will filter to the West Region, and never bother with bringing in the other regions. Or if your West query only needs two columns, the connection query will select only those two columns.
Try adding an unfiltered connection only file, then add some reference files that remove columns, filter dates, etc, and if they fold, look at the native SQL. You'll see what I mean.
--Nate
Here's what I'd say about that. I say there definite is a difference, and that if you don't need to load as a table, then you shouldn't. A few things to think about:
Suppose that QueryB references QueryA. The final step in QueryA has 5000 rows, and a column named "Events".
QueryB references QueryA and it's 5000 rows and with its "Events" column. You then transform QueryB by removing the Events column, and by filtering some other stuff so your table had a few hundred rows.
If you make QueryA Connection Only, lazy M is going to think of the most efficient, laziest way to evaluate QueryB. By not loading QueryA, QueryB is the only Query being evaluated for a result. M will basically make one efficient query, and will do the filtering and probably never even add the "Events" column, because it never needs it.
But if you Load QueryA, M does have to grab and load the "Events" column, and all 5000 rows, and then QueryB has to evaluate QueryA in its fully evaluated instance, and then evaluate QueryB.
I think of Connection Only as "I still want my queries that reference QueryA to act like a continuation of QueryA." I think of Load as "I want my queries that reference QueryA to act like a brand new query that refers to the fully processed QueryA."
I wish I could explain it more technically!
--Nate
Hi Nate, thank you very much. I have a follow up. So if I load as a table as opposed to Connectuon and I right click refresh then it should give me the most recent deletions additions and whatever else to that table, correct? I just remember when I was testing it that it didn't seem like it was but I could've been doing something wrong. I just want to know from a more seasoned user that if I It is loaded as a table and right click refresh then it should give me the latest and greatest. I hope that makes sense. Thank you