Challenge Overview
This competition requires the developer(s) to convert the provided SQL queries into Stored Procedures that perform as per the requirements stated in the Requirements section.
A sample conversion has been included as part of Assets package to provide further contextual information to clarify the requirements further.
NOTE: you need to read the current code first in order to complete the task, ask questions in contest forum as early as possible to get your questions clarified.
1.1 Competition Task Overview
1.1.1 Assets Provided
The following assets required to accomplish the task are provided as part of Assets.zip (attached).
1. Data and DDLs
a. Transactions.bak: SQL Server 2008 R2 database backup with test transaction data. The stored procedures are to be created here.
2. Sample Conversion: These scripts show as an example of the changes made, standards to be followed and the stored procedure parameters required.
a. Sales_Original.sql: the original Store Sales query
b. GetTillSalesData.sql: the query converted to a stored procedure
c. SalesProc_TEST.sql: the test script for the Sales stored procedure
3. SQL Queries to be converted to Stored Procedures
a. DepartmentDetail1_Original.sql, DepartmentDetail2_Original.sql, DepartmentDetail3_Original.sql: The three queries for the Department report to convert into one stored procedure, GetDepartmentDetailData.
b. DepartmentTotal1_Original.sql, DepartmentTotal2_Original.sql, DepartmentTotal3_Original.sql: The three queries for the Department Totals report to convert into one stored procedure, GetDepartmentTotalData.
c. AvgPercentFields1_Original.sql, AvgPercentFields2_Original.sql, AvgPercentFields3_Original.sql: The three queries for the Operator/Workstation Average fields to be converted into one stored procedure, GetAvgPercentData.
d. SalesSummary_Original.sql: The Sales Summary report query to be converted into one stored procedure, GetSalesSummaryData.
e. SalesCategory_Original.sql: This script is not for a separate stored procedure but is an example of how to group data by sales category for the department detail queries.
4. Test SQL script: Test_Scripts_Department.sql
5. Stub SQL script for GetDepartmentDetailData with required parameters, GetDepartmentDetailData_STUB.sql
1.1.2 High level design to be followed
Need to follow similar standards as in the Sample Conversion provided in Assets.zip package (details below). Deviations from this standard can be allowed upon approval using Q&A.
The SQL queries must be able to be executed on a SQL Server 2008 R2 instance.
Existing SQL
The current T-SQL scripts are generated from a template engine. The engine parameters are the table name, column name, final field name, and the specific template to use. The engine combines the templates into one large SELECT statement with several JOINs. The tables hold financial data. The important keys are store id (ID_BSN_UN), business day (ID_TS), and operator or workstation id (ID_OPR, ID_WS, ID_OPR_WS).
In some cases, fields are defined by a formula containing other fields. In this case the templates are processed recursively, so some of the final fields are made up of similar SELECT/JOINs inside them. This is not done efficiently, as often the inner fields are recalculated over and over again.
The queries are generated for a single context, generally Store, Operator, or Workstation. The ID column for the requested context is used. The list of context IDs comes from the user interface, and the generated SQL populates a temp table with the IDs. (The IDs are also copied directly in the IN clause in some older queries.) The overall grouping is by ID and “PeriodStart”, which is a grouping for a day or set of days. So each field returned refers to one specific value for one ID for that requested period.
Conversion process
For converting the existing Sales SQL, we created table-valued parameters for context IDs and store id/business day list, to replace the temp tables. We reduced the number of JOINs by combining several SELECT statements that use the same table, and using CASE statements to provide the correct filter for each result field. We also removed some of the formula calculations, moving the calculation to the wrapper SELECT statement where the base fields had already been calculated.
For example in the original, one sub-query creates fields A, B, and C, and another sub-query creates fields A, B, and C and then sums them up into field D. In the modified SQL, fields A, B, and C are created only once, and field D is created as the sum in the outer query.
In order to be sufficiently dynamic, some of the filters and groupings have CASE statements to determine which column to use based on the @Context parameter. Also the filter has to be constructed to not filter on operator or workstation if that particular context is not being used.
For these department queries, the training flag (FL_TRG_TRN) will always be 0. The @intervalBegin and @intervalEnd parameters must be used to filter data (see the #SingleSlice temp table in the original queries or the hardcoded values 900 and 1659). If the @ID_OPR or @ID_WS table parameters have rows, then the data should be filtered on those values. These parameters are independent of the @Context parameter, which determines how the results are grouped.
Please note that even though the original department queries use an IN list for the date parameters, the stored procedures should JOIN to the table parameter, just like in the sample conversion.
Also note that the department list is a hierarchy. For example, Department 10001 contains over a hundred sub-departments, and the totals for Department 10001 include data for all the sub-departments.
1.1.3 Stored Procedure Requirement
1. Convert the SQL queries in the DepartmentDetail1_Original.sql, DepartmentDetail2_Original.sql, and DepartmentDetail3_Original.sql files to one stored procedure called GetDepartmentDetailData using the same parameters provided in the department detail stub.
a. The test script should provide the same results as the original queries. The stored procedure must return the same columns (name, type, and order) as the original queries, but in one result set. The columns ID, PeriodStart, and NumberOfBusinessDays (as well as the group by columns) should only appear once each. The rows must be the same number and with the same data for the test data.
b. The test script should perform at least 5 times faster (return results in less than 20% of the time) than original SQL.
c. This stored procedure will be executed for the following contexts: Department, SalesCategory, Operator, or Workstation. If the context is not Department, then the result should also group by ID_SAL_CAT, ID_OPR, or ID_WS. The column ID_OPR column should return 0 if the context is not Operator; and the column ID_WS column should return 0 if the context is not Workstation. For an example of SalesCategory, see the script SalesCategory_Original.
2. Convert the SQL queries in the DepartmentTotal1_Original.sql, DepartmentTotal2_Original.sql, and DepartmentTotal3_Original.sql files to one stored procedure called GetDepartmentTotalData using the same parameters provided in the department detail stub.
a. The test script should provide the same results as the original queries. The stored procedure must return the same columns (name, type, and order) as the original queries, but in one result set. The columns ID, PeriodStart, and NumberOfBusinessDays should only appear once each. The rows must be the same number and with the same data for the test data.
b. The test script should perform at least 5 times faster (return results in less than 20% of the time) than original SQL.
c. This stored procedure will ONLY be executed for the Department context. The columns ID_SAL_CAT, ID_OPR, and ID_WS are NOT in the results. However they may still be part of the filter.
3. Convert the SQL queries in the AvgPercentFields1_Original.sql, AvgPercentFields2_Original.sql, and AvgPercentFields3_Original.sql files to one stored procedure called GetAvgPercentData using the same parameters provided in the department detail stub.
a. The test script should provide the same results as the original queries. The stored procedure must return the same columns (name, type, and order) as the original queries, but in one result set. The columns ID, PeriodStart, and NumberOfBusinessDays (as well as the group by columns) should only appear once each. The rows must be the same number and with the same data for the test data.
b. The test script should perform at least 5 times faster (return results in less than 20% of the time) than original SQL.
c. This stored procedure will ONLY be executed for Operator or Workstation context. The ID column will always be -1. The column ID_OPR column should return 0 if the context is not Operator; and the column ID_WS column should return 0 if the context is not Workstation. All the filters still apply.
4. Convert the SQL queries in the SalesSummary_Original.sql file to one stored procedure called GetSalesSummaryData using the same parameters provided in the department detail stub.
a. The test script should provide the same results as the original queries. The stored procedure must return the same columns (name, type, and order) as the original query. The rows must be the same number and with the same data for the test data.
b. The test script should perform at least 5 times faster (return results in less than 20% of the time) than original SQL.
c. This stored procedure will ONLY be executed for the Department context. The columns ID_SAL_CAT, ID_OPR, and ID_WS are NOT in the results. However they may still be part of the filter.
1.2 Submission Deliverables
A complete list of deliverables can be viewed in the TopCoder Assembly competition Tutorial at:
http://apps.topcoder.com/wiki/display/tc/Assembly+Competition+Tutorial
1.3 Technology Overview
• SQL
• SQL Server 2008 R2
1.4 Documentation Provided
See contest page.
1.5 Timeline
Submission Phase Length (days): 5 days
Registration Phase Length (days): 2 days
1.6 Payment
1.6.1 Base Payment
TopCoder will compensate members with first and second place submissions. Initial payment for the winning member will be distributed in two installments. The first payment will be made at the close of the approval phase. The second payment will be made at the completion of the support period.
Winning Submission Prize: $1200
Second Place Prize: $600
1.6.2 Support
Standard 30-days support is required.
1.7 Final Submission
For each member, the final submission should be uploaded to the Online Review Tool.
1.8. Competitor Restrictions
CCA is required.
Final Submission Guidelines
N/A