This topic is a cover-all topic to clarify rules for the Phil Factor Speed Phreak Challenges. Please do post up feedback about it, if you have any!
Rules:
You are allowed to use any method you see fit, you may also add any indexes, table-functions or views that you wish (though not an index view). Creation/dropping of these will not count to the overall execution time.
Your entry must remove all created items afterwards, leaving a clean slate.
If you write your solution using SQLCLR, then you must post SQL which creates / drops the assembly and CLR functions / procedures. You should also post the source code to your SQLCLR solution.
If you are unsure if what you want to do might disqualify you, then please post a comment on the challenge in question.
"Passing the torch"
The winner of the competition gets to set the next competition and to run the test harness. He/She will be able to declare the fastest entry that gives the correct results in a reliable manner. Any doubts or disputes will be resolved by Phil Factor in discussion with the entrants and the Webmaster/Editor of SQLServerCentral.
In the unlikely event of a winner being unable or unwilling to set and run the next competition, then He/She is allowed to pass the baton to the runner-up, or the recipient of one of the special prizes in the previous competition.
How to enter:
Include a header in your suggestion. Make sure your name and the current date is present.
Include an edition number. First edition is 1. If you change the method you are using significantly, change the edition to 2. If you update it slightly (e.g. change an index, change groupings etc) then add a version. So you might start with "Peso 1", improve it to "Peso 1b" and then "Peso 1c". Then you might try a completely different way of getting the results, which would then become "Peso 2". Improvements to that would then become "Peso 2b", "Peso 2c" etc.
The solution must clear up all its mess (temporary tables, indexes, etc.) so it can be re-run without errors.
Post your entry in three sections - Setup: includes creation of any indexes, functions, procedures etc Main code: The code that produces the correct result, and will be timed. Teardown: drops any objects created during Setup.
How is timing done?
Timing is done using a custom-written test harness. This harness will run your setup code, then run your main code over at least 20 iterations. The time that will be used is the median time over those runs. Before the first run, the harness will run DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE and DBCC FREESYSTEMCACHE. After the runs, the harness will run your teardown code. Regular updates will be posted showing who is in what position.
What is a correct result?
Each challenge includes a reference implementation, usually by a member of the Bar family. This is the result which your entry must produce. A correct result is a result that:
- includes the right number of result sets
- uses the same column names as the reference solution's result
- has the right number of columns in the correct order in those result sets
- uses the same data types as the reference solution's result
- has the right data.
Whether the order of the data is important or not will be specified for each challenge.
What data do I test with?
Each challenge provides the DDL for the tables involved, as well as BCP-suitable text files in order to allow the data to be loaded onto your system. This will be the data that is used for the final judgement of performance, unless entries are to the point that the data needs to be expanded to differentiate them.
How long do I have?
The entry closing date for each challenge is specified in the challenges.
What do I get if I win?
Each challenge has it's own unique prize, as well as the privilege of displaying this rather nice trophy jpg on your blog / website / bedroom wall:
