· 7 years ago · Sep 11, 2018, 11:16 AM
1How to make a cursor faster
2/* just preparation of cursor, this is not time consuming */
3CREATE TABLE #result
4 (
5 repid INT,
6 AccountNo VARCHAR(100),
7 supplier VARCHAR(15),
8 CompanyName VARCHAR(200),
9 StartDate DATETIME,
10 EndDate DATETIME,
11 Product VARCHAR(25),
12 commodity VARCHAR(25),
13 ContractEnd DATETIME,
14 EstUsage INT,
15 EnrollStatus VARCHAR(10),
16 EnrollDate DATETIME,
17 ActualEndDate DATETIME,
18 MeterStart DATETIME,
19 MeterEnd DATETIME,
20 ActualUsage INT
21 )
22
23DECLARE @AccountNo VARCHAR(100)
24DECLARE @supplier VARCHAR(10)
25DECLARE @commodity VARCHAR(15)
26DECLARE @meterstart DATETIME
27DECLARE @meterEnd DATETIME
28DECLARE @volume FLOAT
29DECLARE @RepID INT
30DECLARE @Month INT
31DECLARE @Year INT
32
33SET @repID = 80
34SET @Month = 1
35SET @year = 2012
36
37/* the actual cursor */
38DECLARE commission_cursor CURSOR FOR
39 SELECT AccountNo,
40 supplier,
41 commodity,
42 meterStart,
43 MeterEnd,
44 Volume
45 FROM commission
46 WHERE Datepart(m, PaymentDate) = @Month
47 AND Datepart(YYYY, PaymentDate) = @Year
48
49OPEN commission_cursor
50
51FETCH next FROM commission_cursor INTO @AccountNo, @supplier, @commodity, @MeterStart, @MeterEnd, @Volume;
52
53WHILE @@fetch_status = 0
54 BEGIN
55 IF EXISTS (SELECT id
56 FROM Records
57 WHERE AccountNo = @AccountNo
58 AND supplier = @supplier
59 AND Commodity = @commodity
60 AND RepID = @repID)
61 INSERT INTO #result
62 SELECT TOP 1 RepID,
63 AccountNo,
64 Supplier,
65 CompanyName,
66 [Supplier Start Date],
67 [Supplier End Date],
68 Product,
69 Commodity,
70 [customer end date],
71 [Expected Usage],
72 EnrollStatus,
73 ActualStartDate,
74 ActualEndDate,
75 @meterstart,
76 @MeterEnd,
77 @volume
78 FROM Records
79 WHERE AccountNo = @AccountNo
80 AND supplier = @supplier
81 AND Commodity = @commodity
82 AND RepID = @repID
83 AND @MeterStart >= Dateadd(dd, -7, ActualStartDate)
84 AND @meterEnd <= Isnull(Dateadd(dd, 30, ActualEndDate), '2015-12-31')
85
86 FETCH next FROM commission_cursor INTO @AccountNo, @supplier, @commodity, @MeterStart, @MeterEnd, @Volume;
87 END
88
89SELECT *
90FROM #result
91
92/* clean up */
93CLOSE commission_cursor
94
95DEALLOCATE commission_cursor
96
97DROP TABLE #result
98
99declare commission_cursor cursor
100local static read_only forward_only
101for
102
103;WITH x AS
104(
105 SELECT
106 rn = ROW_NUMBER() OVER (PARTITION BY r.AccountNo, r.Supplier, r.Commodity, r.RepID
107 ORDER BY r.ActualEndDate DESC),
108 r.RepID,
109 r.AccountNo,
110 r.Supplier,
111 r.CompanyName,
112 StartDate = r.[Supplier Start Date],
113 EndDate = r.[Supplier End Date],
114 r.Product,
115 r.Commodity,
116 ContractEnd = r.[customer end date],
117 EstUsage = r.[Expected Usage],
118 r.EnrollStatus,
119 EnrollDate = r.ActualStartDate,
120 r.ActualEndDate,
121 c.MeterStart,
122 c.MeterEnd,
123 ActualUsage = c.Volume
124 FROM dbo.commission AS c
125 INNER JOIN dbo.Records AS r
126 ON c.AccountNo = r.AccountNo
127 AND c.Supplier = r.Supplier
128 AND c.Commodity = r.Commodity
129 AND c.RepID = r.RepID
130 WHERE
131 c.PaymentDate >= DATEADD(MONTH, @Month-1, CONVERT(CHAR(4), @Year) + '0101')
132 AND c.PaymentDate < DATEADD(MONTH, 1, CONVERT(CHAR(4), @Year) + '0101')
133 AND r.RepID = @RepID
134)
135SELECT RepID, AccountNo, Supplier, CompanyName, StartDate, EndDate,
136 Product, Commodity, ContractEnd, EstUsage, EnrollStatus, EnrollDate,
137 ActualEndDate, MeterStart, MeterEnd, ActualUsage
138FROM x
139WHERE rn = 1 --ORDER BY something;