Posted on: January 2, 2020 Posted by: admin Comments: 32

Replacing cursors using joins in sql server Part 64



Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In Part 63, we have discussed about cursors. The example, in Part 63, took around 45 seconds on my machine. Please watch Part 63, before proceeding with this video. In this video we will re-write the example, using a join.

Update tblProductSales
set UnitPrice =
Case
When Name = ‘Product – 55’ Then 155
When Name = ‘Product – 65’ Then 165
When Name like ‘Product – 100%’ Then 10001
End
from tblProductSales
join tblProducts
on tblProducts.Id = tblProductSales.ProductId
Where Name = ‘Product – 55’ or Name = ‘Product – 65’ or
Name like ‘Product – 100%’

When I executed this query, on my machine it took less than a second. Where as the same thing using a cursor took 45 seconds. Just imagine the amount of impact cursors have on performance. Cursors should be used as your last option. Most of the time cursors can be very easily replaced using joins.

To check the result of the UPDATE statement, use the following query.
Select Name, UnitPrice from
tblProducts join
tblProductSales on tblProducts.Id = tblProductSales.ProductId
where (Name=’Product – 55′ or Name=’Product – 65′ or
Name like ‘Product – 100%’)

Nguồn: https://medinaportal.net/

Xem thêm bài viết: https://medinaportal.net/category/cong-nghe

Categories:

32 People reacted on this

  1. Beautiful. I have a system that uses many cursors. I will be going throughout and evaluating if a join would be better. Thank you sir

  2. Watch a 30 mins video in 3 mins. The BEST extension in google chrome store. https://chrome.google.com/webstore/detail/threelly-ai-for-youtube/dfohlnjmjiipcppekkbhbabjbnikkibo
    Plus,the state of the art Artificial Intelligence algorithms automatically analyzes videos to locate and pull the precise location of key points of interest like – topics, scenes, people, sentiments, brands, expressions, labels and much more. Allowing you to rapidly gain intelligent insights from any video.

  3. Thank you sir for helping beginners like me. Please I have a question. Can a User defined Function(UDF) be used instead of a cursor?

  4. nice way of Teaching sir, Awesome videos. i have got a Much respect into the era of Developing . its all because of you sir. thank's a lot

  5. My learning lasts about 1month, i will end this whole course in 2 months. I know much more nowadays. Thank U Venkat. I recommended this course to all of my friends.

  6. Hi venkt, really your video is helping me a lot I'm from Liberia west Africa.
    with your video i'm becoming a big boy in technology in class in T–SQL thanks a lot.

  7. hello venkat garu your videos are superb. i have been watching this videos for past 5 months thank you for making this videos. please do more videos

  8. Hello venkat . . .your vedios are simply awsome and please share if you possible total DBA or Apps tech vedios
    . . .if you stay in hyd I wanted to take coaching with you. . . please do rply??????

  9. Thank you very much for taking time to give feedback. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video. For email alerts, when new videos are uploaded, you may subscribe to my channel.

  10. Hi Sir,

    Your way of teaching is really very very nice.
    I have learned SQL server through your videos.
    Thanks a lot for your time
    Keep uploading.
    God bless you

  11. Hi sir..Thank You very much for these videos.. It Helped me a lot, every example was clear and provides the understanding of the concept in a great details.. I forwarded this videos to all my friends and they also find them very useful.. Please Keep Posting such videos.. Thank You Sir..:)

  12. Dear Venkat,
    Can you please site an example where we must use a cursor; a requirement which can not be achieved by a join.

  13. Good Morning Sir
    your videos are superb , from these video series i learned lot
    Can you please upload video on crystal report and reporting services
    i am waiting for your reply
    thank you very much

  14. Venkat, I watched all your videoes up to part 64 for many times. They are very good, much better than others on Youtube. Looking forward more. Thanks a lot.

  15. Take a bow Venkat sir!!
    I am a huge fan of your video tutorials on SQL Server, every time I watch your videos I learn something new.

  16. Thank you so much Venkat Sir for doing just a Brilliant work.

    You are Awesome!!! I love your Videos…

Leave a Comment