Creating a large table with random data for performance testing Part 61

June 27, 2020



In this video we will discuss about inserting large amount of random data into sql server tables for performance testing.

In our next video, we will be using these tables, for performance testing of queries that uses subqueries and joins.

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

Nguồn: https://mensagensdamanha.com/

Xem thêm bài viết khác: https://mensagensdamanha.com/cong-nghe/

All Comments

  • For Simple Performance Testing. If you want to Create 100K or more tables. Check this video.

    ​https://www.youtube.com/watch?v=amh2IxXUz1c&t=2s

    Note: No Tools or Programming language is required.

    DATADECODER June 27, 2020 7:15 am Reply
  • A CTE would insert the data much faster than a loop (10x).

    Declare @MaxId int

    Set @MaxId = 300000

    ;WITH cte_Products as (

    SELECT

    1 as ID

    ,'Product – ' + CAST((1) as nvarchar(20)) as Name

    ,'Product – ' + CAST((1) as nvarchar(20)) + ' Description' as Description

    UNION ALL

    SELECT

    cteCust.ID + 1 as ID

    ,'Product – ' + CAST((cteCust.ID + 1) as nvarchar(20)) as Name

    ,'Product – ' + CAST((cteCust.ID + 1) as nvarchar(20)) + ' Description' as Description

    FROM cte_Products cteCust

    WHERE cteCust.ID < @MaxId

    )

    Insert into tblProducts SELECT Name,Description FROM cte_Products OPTION (MAXRECURSION 0)

    Soarin Dragon June 27, 2020 7:15 am Reply
  • I wanted to understand the difference between SQL and Procedural SQL like PL/SQL or T SQL. I see that you have used coding blocks, declare block etc in this example. In some examples you have also used try/catch block for exception handling. How is it different from Procedural SQL?

    Anusha June 27, 2020 7:15 am Reply
  • You are best person Nice vidéo thanks

    t kumar June 27, 2020 7:15 am Reply
  • hi Venkat, thanks for sharing, should I practice to capitalize SQL reserved word every time when writing my script?

    Ralph Ng June 27, 2020 7:15 am Reply
  • What an explanation? Hats of you sir….

    dennis daniel June 27, 2020 7:15 am Reply
  • in mysql declare is not recognized, why?

    Madalin B June 27, 2020 7:15 am Reply
  • Hi Venkat, Your series is very good. I learn so many new things. Very simple and up to the mark explanations. But for this, I am getting an error while inserting the values in tblProductSales.Error states: The insert statement conflicted with Foreign key Constraint. If you could please tell me what is the cause of this error as I have followed the same code that you have explained in this video. Thanks a lot

    Madhavi Bharani June 27, 2020 7:15 am Reply
  • Use select round(rand()*5,0) to generate values between 1 and 5. Replace 5 with any number to generate values between 1 and that number

    DIPALEE G June 27, 2020 7:15 am Reply
  • Thanks alot

    pramod maurya June 27, 2020 7:15 am Reply
  • Unit price should be part of the products table. Total price should be a calculated value in the orders table (quantity*price).

    Kenneth Hutson June 27, 2020 7:15 am Reply
  • That's all for today. Thank U Venkat ! You are best teacher I met in my life.
    See U tomorrow

    Krzysztof S June 27, 2020 7:15 am Reply
  • There is slight differences in the counter shown in this video and the counter in your blog. Please correct those if possible.
    In the blog, counter is huge (lakh records!) I changed it to those given in the video. Still great video!

    Salman Shaikh June 27, 2020 7:15 am Reply
  • Revisiting.

    Thanks for educating the community

    Thanks a lot

    Kris Maly June 27, 2020 7:15 am Reply
  • Great series of videos, this is the first one I've seen anything that I felt needed a comment. At 9:57 you say you should add 1 to produce values between 1 and 5. This is correct but only because the lower limit is 1. If you change the lower limit to 2 and the upper limit to 6 you'll still generate numbers between 1 and 5. The correct thing to do is add lower limit. More formally the subtraction determines the range of the random numbers the addition determines the offset.

    Natural Born Coder June 27, 2020 7:15 am Reply
  • great explanation

    #Stuti# #Tehri# June 27, 2020 7:15 am Reply
  • I have seen the random function being used in some of the legacy code, but have never used it by myself. This video teaches me how and why should i use this function. However, i can sense that in this video, for the first time in this video series, I was a bit lost as to what we are trying to achieve here. I am sure that next video will put all my doubts to rest. Thanks a lot Venkat for making the tough look so easy!

    Anurag Vashishtha June 27, 2020 7:15 am Reply
  • very nice explanation ,thank u very much.

    ipsita pani June 27, 2020 7:15 am Reply
  • I have watched your videos on Indexes. Could you please make some videos on Performance Tuning with Indexed Views.
    when we will get more performance with Indexed views?? what is the benefit of WITH (noexpand) at the indexed view declaration??

    eshwar chowdary Vempati June 27, 2020 7:15 am Reply
  • Great tutorial…

    kai13man June 27, 2020 7:15 am Reply
  • wooo woooo……great ..!!

    Puurusottam Saha June 27, 2020 7:15 am Reply

Leave a Reply

Your email address will not be published. Required fields are marked *