Solving NOLOCK SQL Power BI issues with Sample Code for STEEMSQL

[paulag]

Comments

[arcange]

Thanks @paulag for giving visibility to the importance of the NOLOCK sql hint.

Yes, I implemented a workaround with views and the the use of the NOLOCK can be omitted. For those who missed the post, you can read it here. One still need to be aware that they will get dirty read with the NOLOCK and the result set might not be consistent.

Anyway, good to know how it works and how to use it with Power BI. It will be very useful for people using Power BI and other databases.

BTW, SELECT * FROM TxTransfers (NOLOCK) might not be the best example to show, as this unfiltered query returns more than 3 millions rows :/

[paulag]

"BTW, SELECT * FROM TxTransfers (NOLOCK) might not be the best example to show, as this unfiltered query returns more than 3 millions rows :/"

I did not consider this, very valid point

Thanks for adding the link back to your post. This tutorial is not only aimed at those using steemsql but also those connecting to any live sql database. I will be sending out a link later on in a newsletter to a power bi community as it is a relevant topic to cover

[whd]

doesnt it force nolock by views?

[paulag]

Yes as far as I am aware it does. This is for those wishing to now get their heads around a little sql.

[kennyroy]

Interesting my friend... I love to try...

[abh12345]

Thanks Paula. Now I have no excuses not to have PowerBI added to my skill set!

[paulag]

haha I knew I would catch you in my powerbi web

[ace108]

Personally, I like to make sure the SQL works the way I want first and use SQL Developer for that. After that, I shall then put in the query in Power BI.

[paulag]

SQL developer is a foreign language and totally new tool when you don't know sql and just come from an excel background. But when I get to developing more complex queries this will be an option

[ace108]

yes, it is quite fun when you get the hang of it and give your better control.
there's another 2 tool I heard of which I have not tried is Lincpad and Valentina Studio.
only because I'm quite comfortable with SQL developer.
although I did go to the Valentina Studio to read bit about and was bit impress.
maybe something for you to consider https://www.valentina-db.com/en/valentina-studio-overview :-)

[miniature-tiger]

I use Valentina Studio for my steemsql SQL queries. It works pretty well once you get the hang of it. And it's free.

[ace108]

Yay... free is good. So, is SQL developer but I see the Valentino Studio has a more funky look than the boring SQL developer. :)

[ace108]

One more thing, I see you use a condition of
author in ('somebody')
That's useful if you want to go with more than one author. In Oracle SQL, when you want only one, the performance is better if you do.
author = 'somebody'
I suspect MS SQL should be the same.

[paulag]

Thank you!

[ace108]

you're welcome :-)

[yassine1234]

doesnt it force

[ace108]

Don't know for sure but if you have the answer, please do update.

[cryptocheta]

wow, really going to follow you, great work, I will continue to follow you.

[zeartul]

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

[utopian-io]

Hey @paulag I am @utopian-io. I have just upvoted you at 17% Power!

Achievements

• Seems like you contribute quite often. AMAZING!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

• Vote for my Witness
• Proxy to my Witness
• Or vote/proxy on Steemit Witnesses

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x

[rzenga]

Thanks @paulag ! This article help me a lot! :-)