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.
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)
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?
You are best person Nice vidéo thanks
hi Venkat, thanks for sharing, should I practice to capitalize SQL reserved word every time when writing my script?
What an explanation? Hats of you sir….
in mysql declare is not recognized, why?
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
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
Thanks alot
Unit price should be part of the products table. Total price should be a calculated value in the orders table (quantity*price).
That's all for today. Thank U Venkat ! You are best teacher I met in my life.
See U tomorrow
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!
Revisiting.
Thanks for educating the community
Thanks a lot
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.
great explanation
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!
very nice explanation ,thank u very much.
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??
Great tutorial…
wooo woooo……great ..!!