November 7, 2011 at 5:09 am
I have a sql query which select the best combination of hours with the heighest weight, It uses a recursive CTE to do find all the combinations of
hours as shown below
Declare @EmpClasses table(Id int identity(1,1),ClassNum int,ClassWeight int,ClassHours int)
Declare @HoursReq int;
SET @HoursReq = 20;
INSERT INTO @EmpClasses VALUES(1001,10,10),(1002,9,5),(1003,8,4),(1004,7,3),(1005,6,2),(1006,5,2),(1007,4,1);
--INSERT INTO @EmpClasses VALUES(1001,2,2),(1002,2,2),(1003,2,2),(1004,2,2),(1005,2,2),(1006,2,2),(1007,2,2),(1008,2,2),(1009,2,2),
(1010,2,2);
--INSERT INTO @EmpClasses VALUES(1011,2,2),(1012,2,2),(1013,2,2),(1014,2,2),(1015,2,2),(1016,2,2),(1017,2,2),(1018,2,2),(1019,2,2),
(1020,2,2);
--INSERT INTO @EmpClasses VALUES(1021,2,2),(1022,2,2),(1023,2,2),(1024,2,2),(1025,2,2),(1026,2,2),(1027,2,2),(1028,2,2),(1029,2,2),
(1030,2,2);
WITH cte (Id,comIds,Total,TotalWeight)
AS
(
SELECT Id
,Cast(ClassNum as varchar(max)) + ','
,ClassHours
,ClassWeight
FROM @EmpClasses
UNION ALL
SELECT ec.Id
,cte.comIds + Cast(ec.ClassNum as varchar(max)) + ','
,cte.Total + ec.ClassHours
,cte.TotalWeight + ec.ClassWeight
FROM @EmpClasses AS ec JOIN cte ON ec.Id < cte.Id
)
SELECT top(1)comids,Total,TotalWeight
FROM cte
Where Total = @HoursReq
order by TotalWeight desc
However the problems lies, when the number of rows increases to iterate. It takes a very long time.
Note:- Please uncomment above lines to check for the issue
Any help regarding this will be greatly appreciated
November 7, 2011 at 7:36 am
Take a look at this article from Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/74118/%5B/url%5D
I realize you are not counting but your join is the same concept. It explains why performance gets awful as the rowcount increases.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply