c# - 14 列报表的 SQL 查询超时（14 天）

Question

if (myMandays == 1)
                Filter1 = " count(empno)";
            else
                Filter1 = " sum(mdays)";

            switch (myKcodeInt)
            {
                case 1:
                    Filter2 = "";
                    break;
                case 2:
                    Filter2 = " and a.khead = @myKcodeStr";
                    break;
                case 3:
                    Filter2 = " and a.kcode = @myKcodeStr";
                    break;

                default:
                    Filter2 = " and a.ktype = @myKcodeStr";
                    break;
            }
            switch (myShiftno)
            {
                case 1:
                    Filter3 = " and shiftno IN (1,2,5)";
                    break;
                case 2:
                    Filter3 = " and shiftno = 1";
                    break;
                case 3:
                    Filter3 = " and shiftno = 2";
                    break;

                default:
                    Filter3 = " and shiftno = 5";
                    break;
            }
            //commandText = "Select * from Wageentry where " +  Filter1 + " and empno in (Select empno from Empmain)";
            commandText = @"Select a.kdesc,a.kcode,b.khead,b.khdesc,a.emandays,a.eamt,a.wtype,a.wtype2,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date1) and kcode = a.kcode" + Filter3 + @") as day1,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date2) and kcode = a.kcode" + Filter3 + @") as day2,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date3) and kcode = a.kcode" + Filter3 + @") as day3,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date4) and kcode = a.kcode" + Filter3 + @") as day4,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date5) and kcode = a.kcode" + Filter3 + @") as day5,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date6) and kcode = a.kcode" + Filter3 + @") as day6,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date7) and kcode = a.kcode" + Filter3 + @") as day7,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date8) and kcode = a.kcode" + Filter3 + @") as day8,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date9) and kcode = a.kcode" + Filter3 + @") as day9,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date10) and kcode = a.kcode" + Filter3 + @") as day10,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date11) and kcode = a.kcode" + Filter3 + @") as day11,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date12) and kcode = a.kcode" + Filter3 + @") as day12,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date13) and kcode = a.kcode" + Filter3 + @") as day13,
            (Select " + Filter1 + @"from WageEntry where kdate = (@date14) and kcode = a.kcode" + Filter3 + @") as day14,
            (Select " + Filter1 + @"from WageEntry where kdate between (@date1) and (@date14) and kcode = a.kcode" + Filter3 + @") as mandays,
            (Select sum(wageamt+addamt+overtime) from WageEntry where kdate between (@date1) and (@date14) and kcode = a.kcode " + Filter3 + @") as totalamt,
            (Select sum(wageamt) from WageEntry where kdate between (@date1) and (@date14) and kcode = a.kcode" + Filter3 + @") as wageamt,
            (Select sum(addamt) from WageEntry where kdate between (@date1) and (@date14) and kcode = a.kcode" + Filter3 + @") as addamt,
            (Select sum(overtime) from WageEntry where kdate between (@date1) and (@date14) and kcode = a.kcode" + Filter3 + @") as overtime, 
            (@date1) as sdate,(@date14) as edate
            from kamjaricode a,kamjarihead b
            where a.khead = b.khead" + Filter2 + " order by khead,kcode";

这是为了报告。开始日期和过滤器是从 UI 设置的。这是一份 14 天的工作日报告。使用本地服务器和数据一年，它可以工作，但是一旦我将数据移动到 Azure 并添加了 10 年的数据，报告查询就会开始超时。

Work code A1 day1 day2 day3 ..... day14
Work code A2 day1 day2 day3 ..... day14
Work code A3 day1 day2 day3 ..... day14
Work code A4 day1 day2 day3 ..... day14
Work code A5 day1 day2 day3 ..... day14
Group A Sub-Total
.
.
.
Grand Total for each day for each column.

这就是报告的外观。

选项2：

使用零值生成 14 天的数据表 dt。然后为每一天生成数据表并替换 dt 中的列。

最终输出

标签： c#sql-serverwinforms