Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Generating Test Data: Part 2 - Generating Sequential and Random Dates Expand / Collapse
Author
Message
Posted Saturday, April 28, 2012 4:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 5,615, Visits: 25,191
Jeff Moden (4/26/2012)
TheSQLGuru (4/25/2012)
Hey Jeff, can you put a downloadable file with the relevant operational code parts of the post? Thanks in advance, and wonderful stuff as always!


If I understand correctly, those are pretty well summarized in the last two sections of the article. Is that what you want as a downloadable file?


Certainly, am too lazy to do a cut and paste to my Sandbox DB...


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1292074
Posted Monday, April 30, 2012 6:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 37,075, Visits: 31,631
Samrat Bhatnagar (4/28/2012)
These two part series were really useful. Thanks.

Any suggestions on how to generate test data for following scenarios:
1. Two tables linked using PK-FK relationship e.g. Product Category and Product Subcategory
2. Self Referential Tables like the Employee table with EmployeeId, ManagerId, <Other employee details>
3. Using the master tables in 1, 2 generate a table that has ProductFK, EmployeeFK, <Some data> as in a Data warehouse.


Sure. I might be able to include some of that in part 3.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1292446
Posted Monday, April 30, 2012 10:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:55 PM
Points: 3,648, Visits: 5,321
Jeff Moden (4/30/2012)
Samrat Bhatnagar (4/28/2012)
These two part series were really useful. Thanks.

Any suggestions on how to generate test data for following scenarios:
1. Two tables linked using PK-FK relationship e.g. Product Category and Product Subcategory
2. Self Referential Tables like the Employee table with EmployeeId, ManagerId, <Other employee details>
3. Using the master tables in 1, 2 generate a table that has ProductFK, EmployeeFK, <Some data> as in a Data warehouse.


Sure. I might be able to include some of that in part 3.


How about something in part XXX about generating data with random gaps and islands.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1292925
Posted Tuesday, May 1, 2012 5:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 37,075, Visits: 31,631
Heh... XXX... did you really mean "Part 30"?

Random gaps and islands are easy although known gaps and islands make life a whole lot easier test wise. Just build a wad of sequential dates and randomly delete a percentage of them.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1293024
Posted Friday, May 4, 2012 4:08 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:03 PM
Points: 259, Visits: 1,089
Jeff,

I may have missed it glancing through all of the posts. When do you plan to produce Part 3?

Thanks for the effort you expended on such excellent documentation.

LC
Post #1295496
Posted Saturday, May 19, 2012 6:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 37,075, Visits: 31,631
Lee Crain (5/4/2012)
Jeff,

I may have missed it glancing through all of the posts. When do you plan to produce Part 3?

Thanks for the effort you expended on such excellent documentation.

LC


Apologies for the late response. It's one of those things where you say to yourself that you'll answer that one "tomorrow".

I'd intended to be done with Part 3 by now but have barely scratched the surface of it. I'll going to try to get it done this week and submit it next weekend. It takes about 4 to 6 weeks after submital for an article to come out.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1303072
Posted Saturday, May 19, 2012 7:41 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:03 PM
Points: 259, Visits: 1,089
Thanks. I'm looking forward to it, as is my company's software development staff.

LC
Post #1303074
Posted Saturday, May 19, 2012 8:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:35 PM
Points: 831, Visits: 1,581
Now that we're all up-to-speed on generating test data, you know what would be trez cool (not suggesting that Jeff should have to do it, but it would be great if it existed)? A step-by-step guide to setting up an empirical test environment. It seems to me that there are too many traps for us new player, that will lead us to incorrectly conclude that method A is better/worse/no different to method B.

Issues to consider, for example:
:: I've read recently that you shouldn't conclude that because a query takes X seconds to bring the data to your screen, that it's a fair representation of how long the query took to run. Most of the "execution" time could simply be shipping a million rows of data over the network. The workaround might be to run the data into a temp table or a table variable... or... something (he said knowing he was out of his depth)
:: How should we treat the cache and buffers?
:: How do we set up a timer. I simply set variables to getdate() at the start and end of what I'm trying to test, and datediff them. Is this reasonable?
:: What are the pitfalls to taking the execution plan's "cost relative to batch" and "estimated subtree cost" literally? I've seen it wildly inaccurate, and not just because of outdated statistics and so on. It's often because it can't accurately estimate the cost of a scalar function, for example.
:: I've used Adam Machanic's SQLQueryStress tool before because it seems that it can give you an idea of how the query will perform with multiple threads and iterations with a variety of parameters.






One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1303078
Posted Sunday, May 20, 2012 4:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 2,013, Visits: 1,589
Nice Article Jeff!!!

Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1303088
Posted Sunday, May 20, 2012 3:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 37,075, Visits: 31,631
GPO (5/19/2012)
Now that we're all up-to-speed on generating test data, you know what would be trez cool (not suggesting that Jeff should have to do it, but it would be great if it existed)? A step-by-step guide to setting up an empirical test environment. It seems to me that there are too many traps for us new player, that will lead us to incorrectly conclude that method A is better/worse/no different to method B.

Issues to consider, for example:
:: I've read recently that you shouldn't conclude that because a query takes X seconds to bring the data to your screen, that it's a fair representation of how long the query took to run. Most of the "execution" time could simply be shipping a million rows of data over the network. The workaround might be to run the data into a temp table or a table variable... or... something (he said knowing he was out of his depth)
:: How should we treat the cache and buffers?
:: How do we set up a timer. I simply set variables to getdate() at the start and end of what I'm trying to test, and datediff them. Is this reasonable?
:: What are the pitfalls to taking the execution plan's "cost relative to batch" and "estimated subtree cost" literally? I've seen it wildly inaccurate, and not just because of outdated statistics and so on. It's often because it can't accurately estimate the cost of a scalar function, for example.
:: I've used Adam Machanic's SQLQueryStress tool before because it seems that it can give you an idea of how the query will perform with multiple threads and iterations with a variety of parameters.




You should see the nasty problems that come up with supposedly reliable things like SET STATISTICS TIME ON. I'm mostly convinced that the errors there are the reason the supposed best practice of avoiding scalar UDFs exists. I say, "It Depends" and have "guts" of an "SQL Spackle" article setup for it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1303122
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse