I think this was one of her tests... it was on a downloadable USDA database I
think.
DECLARE @Start DATETIME,
@End DATETIME,
@Total1 BIGINT,
@Total2 BIGINT,
@Total3 BIGINT,
@LoopCount INT,
@LoopTotal INT,
@Efficiency1 FLOAT,
@Efficiency2 FLOAT
SET @LoopCount = 1
SET @Total1 = 0
SET @Total2 = 0
SET @Total3 = 0
SET @LoopTotal = 3
WHILE @LoopCount <= @LoopTotal
BEGIN
-- Select *
SET @Start = GetDate()
SELECT *
FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd
SET @End = GetDate()
SET @Total1 = @Total1 + DateDiff(ms, @Start, @End)
-- Select all columns:
SET @Start = GetDate()
SELECT FD.NDB_No, FD.FdGrp_Cd, FD.[Desc], FD.Shrt_Desc, FD.ComName,
FD.ManufacName, FD.Survey,
FD.Ref_Desc, FD.Refuse, FD.SciName, FD.N_Factor, FD.Pro_Factor,
FD.Fat_Factor,
FD.CHO_Factor, FG.FdGrp_Desc
FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd
SET @End = GetDate()
SET @Total2 = @Total2 + DateDiff(ms, @Start, @End)
-- Select needed columns
SET @Start = GetDate()
SELECT FD.NDB_No, FD.FdGrp_Cd, FD.[Desc], FD.Shrt_Desc, FG.FdGrp_Desc
FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd
SET @End = GetDate()
SET @Total3 = @Total3 + DateDiff(ms, @Start, @End)
SET @LoopCount = @LoopCount + 1
END
SET @Efficiency1 = (CAST(@Total2/@LoopTotal AS FLOAT)) /
(CAST(@Total1/@LoopTotal AS FLOAT))
SET @Efficiency2 = (CAST(@Total3/@LoopTotal AS FLOAT)) /
(CAST(@Total1/@LoopTotal AS FLOAT))
PRINT 'Select Star: ' + CAST(@Total1/@LoopTotal AS VARCHAR)
PRINT 'Select All Columns: ' + CAST(@Total2/@LoopTotal AS VARCHAR)
PRINT 'Efficiency: ' + CAST(100 - (@Efficiency1 * 100) AS VARCHAR) + '%'
PRINT ''
PRINT 'Select Needed Columns: ' + CAST(@Total3/@LoopTotal AS VARCHAR)
PRINT 'Efficiency: ' + CAST(100 - (@Efficiency2 * 100) AS VARCHAR) + '%'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246244
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
