April 7, 2009 at 11:02 am
donato1026 (4/7/2009)
I wish there was a tool out there that analyzed your queries and syntax and made suggestions on how to improve upon it.
This page and others like it are pretty much as close as you're going to get to that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 11:03 am
Try this, Donato:
DECLARE @Startdate DATETIME, @Enddate DATETIME
SELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift,
--
sum(update_qty * m.avg_cubes) as total_cubes,
--
(select sum(itl2.update_qty * m2.avg_cubes)
from rp_sku s2
INNER JOIN rp_inventory_transaction itl2 ON s2.sku_id = itl2.sku_id
INNER JOIN rp_application_user au2 ON au2.[user_id] = itl2.[user_id]
INNER JOIN rp_minor_average m2 ON m2.mnr_cd = s2.v_userdef1
where itl2.dstamp between @Startdate and @Enddate
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au.shift = au2.shift -- CORRELATED TO rp_application_user au
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
) as shift_total_cubes,
--
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
from rp_login_stats rls2
where rls2.login_time between @Startdate and @Enddate
and rls2.shift = au.shift) as shift_login_time,
ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC) AS 'RowNumber', -- CORRELATED TO rp_minor_average m
--
d.loginTime,
--
d.loginTimeSeconds,
--
d.LogoutTime
--
FROM rp_minor_average m
INNER JOIN rp_sku s ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au ON au.user_id = itl.user_id
INNER JOIN (SELECT [user_id],
LogoutTime = convert(varchar, max(logout_time),100),
loginTimeSeconds = sum(datediff(s, login_time, logout_time)),
loginTime = cast(sum(datediff(s, login_time, logout_time))as decimal(9,2)) / 25200
FROM rp_login_stats
WHERE login_time BETWEEN @Startdate AND @Enddate
GROUP BY [user_id]) d
ON d.user_id = itl.user_id
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
and au.shift = '1ST P' and au.shift is not null
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
group by itl.user_id, au.notes, au.shift
This replaces three of the correlated subqueries with a single derived table. I'll stick me neck out and suggest it will cut the time by 30%.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 11:05 am
donato1026 (4/7/2009)
I wish there was a tool out there that analyzed your queries and syntax and made suggestions on how to improve upon it.
Good point.
Anyone have any other ideas in regards to my dilemma here?
April 7, 2009 at 11:07 am
I get: Column 'd.loginTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
April 7, 2009 at 11:11 am
Chris Morris (4/7/2009)
Try this, Donato:
DECLARE @Startdate DATETIME, @Enddate DATETIMESELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift,
--
sum(update_qty * m.avg_cubes) as total_cubes,
--
(select sum(itl2.update_qty * m2.avg_cubes)
from rp_sku s2
INNER JOIN rp_inventory_transaction itl2 ON s2.sku_id = itl2.sku_id
INNER JOIN rp_application_user au2 ON au2.[user_id] = itl2.[user_id]
INNER JOIN rp_minor_average m2 ON m2.mnr_cd = s2.v_userdef1
where itl2.dstamp between @Startdate and @Enddate
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au.shift = au2.shift -- CORRELATED TO rp_application_user au
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
) as shift_total_cubes,
--
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
from rp_login_stats rls2
where rls2.login_time between @Startdate and @Enddate
and rls2.shift = au.shift) as shift_login_time,
ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC) AS 'RowNumber', -- CORRELATED TO rp_minor_average m
--
d.loginTime,
--
d.loginTimeSeconds,
--
d.LogoutTime
--
FROM rp_minor_average m
INNER JOIN rp_sku s ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au ON au.user_id = itl.user_id
INNER JOIN (SELECT [user_id],
LogoutTime = convert(varchar, max(logout_time),100),
loginTimeSeconds = sum(datediff(s, login_time, logout_time)),
loginTime = cast(sum(datediff(s, login_time, logout_time))as decimal(9,2)) / 25200
FROM rp_login_stats
WHERE login_time BETWEEN @Startdate AND @Enddate
GROUP BY [user_id]) d
ON d.user_id = itl.user_id
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
and au.shift = '1ST P' and au.shift is not null
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
group by itl.user_id, au.notes, au.shift
This replaces three of the correlated subqueries with a single derived table. I'll stick me neck out and suggest it will cut the time by 30%.
I get: Column 'd.loginTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
April 7, 2009 at 11:16 am
Change this...
---
d.loginTime,
---
d.loginTimeSeconds,
---
d.LogoutTime
---
to this...
---
MAX(d.loginTime) AS loginTime,
---
MAX(d.loginTimeSeconds) AS loginTimeSeconds,
---
MAX(d.LogoutTime) AS LogoutTime
---
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 11:32 am
Chris Morris (4/7/2009)
Change this...---
d.loginTime,
---
d.loginTimeSeconds,
---
d.LogoutTime
---
to this...
---
MAX(d.loginTime) AS loginTime,
---
MAX(d.loginTimeSeconds) AS loginTimeSeconds,
---
MAX(d.LogoutTime) AS LogoutTime
---
So far, over 14 minutes... :-/
April 7, 2009 at 11:36 am
Chris Morris (4/7/2009)
Change this...---
d.loginTime,
---
d.loginTimeSeconds,
---
d.LogoutTime
---
to this...
---
MAX(d.loginTime) AS loginTime,
---
MAX(d.loginTimeSeconds) AS loginTimeSeconds,
---
MAX(d.LogoutTime) AS LogoutTime
---
19 minutes...
April 7, 2009 at 11:46 am
I can't test this, so I'm not sure if it'll work, much less whether it will be faster, slower, etc., than the current version. Can you test it out, let me know if it blows up or whatever?
DECLARE @Date DATETIME ;
--
SELECT
@Date = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) ;
--
WITH Shifts(Shift, Start, Done)
AS (SELECT
'1st',
DATEADD(hour, 3, @Date),
DATEADD(hour, 20, @Date)
UNION ALL
SELECT
'2nd',
DATEADD(hour, 9, @Date),
DATEADD(hour, 23, @Date)
UNION ALL
SELECT
'3rd',
DATEADD(hour, 18, @Date),
DATEADD(hour, 35, @Date)) ,
ShiftTotals(Shift, Total_Cubes)
AS (SELECT
au2.shift,
SUM(itl2.update_qty * m2.avg_cubes)
FROM
rp_sku s2
INNER JOIN rp_inventory_transaction itl2
ON s2.sku_id = itl2.sku_id
INNER JOIN rp_application_user au2
ON au2.user_id = itl2.user_id
INNER JOIN rp_minor_average m2
ON m2.mnr_cd = s2.v_userdef1
INNER JOIN Shifts
ON itl2.dstamp >= Start
AND itl2.dstamp < Done
WHERE
itl2.code IN ('Putaway', 'Pick', 'Relocate')
AND (itl2.from_loc_id <> 'SUSPENSE'
OR itl2.to_loc_id <> 'SUSPENSE')
AND itl2.from_loc_id NOT IN (
SELECT
location_id
FROM
rp_location
WHERE
loc_type IN ('Stage', 'Marshalling'))
GROUP BY
au2.shift)
SELECT
itl.user_id,
au.notes,
au.shift,
SUM(update_qty * m.avg_cubes) AS total_cubes,
shifttotals.total_cubes AS shift_total_cubes,
(SELECT
SUM(DATEDIFF(s, rls2.login_time, rls2.logout_time))
FROM
rp_login_stats rls2
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls2.shift = au.shift) AS shift_login_time,
ROW_NUMBER() OVER (ORDER BY SUM(update_qty * m.avg_cubes) DESC) AS 'RowNumber',
(SELECT
CAST(SUM(DATEDIFF(s, rls.login_time, rls.logout_time)) AS DECIMAL(9, 2))
/ 25200
FROM
rp_login_stats rls
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls.user_id = itl.user_id) AS loginTime,
(SELECT
SUM(DATEDIFF(s, rls.login_time, rls.logout_time))
FROM
rp_login_stats rls
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls.user_id = itl.user_id) AS loginTimeSeconds,
(SELECT
CONVERT(VARCHAR, MAX(rls.logout_time), 100)
FROM
rp_login_stats rls
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls.user_id = itl.user_id) AS LogoutTime
FROM
rp_minor_average m
INNER JOIN rp_sku s
ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl
ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au
ON au.user_id = itl.user_id
INNER JOIN Shifts
ON itl.dstamp >= Start
AND itl.dstamp < Done
INNER JOIN ShiftTotals
ON au.shift = ShiftTotals.Shift
WHERE
itl.code IN ('Putaway', 'Pick', 'Relocate')
AND au.shift = '1ST P'
AND (itl.from_loc_id <> 'SUSPENSE'
OR itl.to_loc_id <> 'SUSPENSE')
AND from_loc_id NOT IN (SELECT
location_id
FROM
rp_location
WHERE
loc_type IN ('Stage', 'Marshalling'))
AND au.shift IS NOT NULL
GROUP BY
itl.user_id,
au.notes,
au.shift
ORDER BY
au.shift,
RowNumber ;
I'm not sure the Group By clause is correct. If it says it's missing columns, go ahead and add them to it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 11:58 am
GSquared (4/7/2009)
I can't test this, so I'm not sure if it'll work, much less whether it will be faster, slower, etc., than the current version. Can you test it out, let me know if it blows up or whatever?
DECLARE @Date DATETIME ;--
SELECT
@Date = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) ;
--
WITH Shifts(Shift, Start, Done)
AS (SELECT
'1st',
DATEADD(hour, 3, @Date),
DATEADD(hour, 20, @Date)
UNION ALL
SELECT
'2nd',
DATEADD(hour, 9, @Date),
DATEADD(hour, 23, @Date)
UNION ALL
SELECT
'3rd',
DATEADD(hour, 18, @Date),
DATEADD(hour, 35, @Date)) ,
ShiftTotals(Shift, Total_Cubes)
AS (SELECT
au2.shift,
SUM(itl2.update_qty * m2.avg_cubes)
FROM
rp_sku s2
INNER JOIN rp_inventory_transaction itl2
ON s2.sku_id = itl2.sku_id
INNER JOIN rp_application_user au2
ON au2.user_id = itl2.user_id
INNER JOIN rp_minor_average m2
ON m2.mnr_cd = s2.v_userdef1
INNER JOIN Shifts
ON itl2.dstamp >= Start
AND itl2.dstamp < Done
WHERE
itl2.code IN ('Putaway', 'Pick', 'Relocate')
AND (itl2.from_loc_id <> 'SUSPENSE'
OR itl2.to_loc_id <> 'SUSPENSE')
AND itl2.from_loc_id NOT IN (
SELECT
location_id
FROM
rp_location
WHERE
loc_type IN ('Stage', 'Marshalling'))
GROUP BY
au2.shift)
SELECT
itl.user_id,
au.notes,
au.shift,
SUM(update_qty * m.avg_cubes) AS total_cubes,
shifttotals.total_cubes AS shift_total_cubes,
(SELECT
SUM(DATEDIFF(s, rls2.login_time, rls2.logout_time))
FROM
rp_login_stats rls2
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls2.shift = au.shift) AS shift_login_time,
ROW_NUMBER() OVER (ORDER BY SUM(update_qty * m.avg_cubes) DESC) AS 'RowNumber',
(SELECT
CAST(SUM(DATEDIFF(s, rls.login_time, rls.logout_time)) AS DECIMAL(9, 2))
/ 25200
FROM
rp_login_stats rls
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls.user_id = itl.user_id) AS loginTime,
(SELECT
SUM(DATEDIFF(s, rls.login_time, rls.logout_time))
FROM
rp_login_stats rls
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls.user_id = itl.user_id) AS loginTimeSeconds,
(SELECT
CONVERT(VARCHAR, MAX(rls.logout_time), 100)
FROM
rp_login_stats rls
INNER JOIN Shifts
ON login_time >= Start
AND login_time < Done
WHERE
rls.user_id = itl.user_id) AS LogoutTime
FROM
rp_minor_average m
INNER JOIN rp_sku s
ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl
ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au
ON au.user_id = itl.user_id
INNER JOIN Shifts
ON itl.dstamp >= Start
AND itl.dstamp < Done
INNER JOIN ShiftTotals
ON au.shift = ShiftTotals.Shift
WHERE
itl.code IN ('Putaway', 'Pick', 'Relocate')
AND au.shift = '1ST P'
AND (itl.from_loc_id <> 'SUSPENSE'
OR itl.to_loc_id <> 'SUSPENSE')
AND from_loc_id NOT IN (SELECT
location_id
FROM
rp_location
WHERE
loc_type IN ('Stage', 'Marshalling'))
AND au.shift IS NOT NULL
GROUP BY
itl.user_id,
au.notes,
au.shift
ORDER BY
au.shift,
RowNumber ;
I'm not sure the Group By clause is correct. If it says it's missing columns, go ahead and add them to it.
This is weird. It ran fast in comparison to the others, including mine - 3:28, however, it returned 0 results. Blank. Nada. Zip.
Weird.
April 7, 2009 at 12:02 pm
At the top, did you change where I had "getdate()" to the date you actually want to run? Looks like 2 April for your posted query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 1:40 pm
I did and it ran for a LONG time. Over 20 minutes. I had to stop it. :-/
April 7, 2009 at 1:42 pm
Can you upload the execution plan it's using?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 1:58 pm
Can you run this please, with and without the derived table which is currently commented out, and post back the run times:SELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift
--
FROM rp_minor_average m
INNER JOIN rp_sku s ON m.mnr_cd = s.v_userdef1
INNER JOIN rp_inventory_transaction itl ON s.sku_id = itl.sku_id
INNER JOIN rp_application_user au ON au.user_id = itl.user_id
/*
INNER JOIN (SELECT [user_id],
LogoutTime = convert(varchar, max(logout_time),100),
loginTimeSeconds = sum(datediff(s, login_time, logout_time)),
loginTime = cast(sum(datediff(s, login_time, logout_time))as decimal(9,2)) / 25200
FROM rp_login_stats
WHERE login_time BETWEEN @Startdate AND @Enddate
GROUP BY [user_id]) d
ON d.user_id = itl.user_id
*/
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
and au.shift = '1ST P' and au.shift is not null
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
AND NOT EXISTS (SELECT 1 FROM rp_location where location_id = itl.from_loc_id AND loc_type in ('Stage', 'Marshalling'))
group by itl.user_id, au.notes, au.shift
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 2:00 pm
Actually, for right now, I am happy with just being able to INSERT the results into a table for now.
Here is my code that I have right now, but it's not working/parsing:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[AvgCubesByShift]
AS
DELETE FROM AvgCubesByShift
INSERT AvgCubesByShift(user_id,notes,shift,total_cubes,shift_total_cubes)
SELECT itl.user_id, au.notes, au.shift,
sum(update_qty * m.avg_cubes) as total_cubes,
(select sum(itl2.update_qty * m2.avg_cubes) as shift_total_cubes
from
rp_sku s2,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl2.code in ('Putaway', 'Pick', 'Relocate')
and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')
and au2.user_id = itl2.user_id
and m2.mnr_cd = s2.v_userdef1
and au.shift = au2.shift
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s2.sku_id = itl2.sku_id )
INSERT AvgCubesByShift(RowNumber,shift_login_time)
(select sum(datediff(s,rls2.login_time, rls2.logout_time)) as RowNumber)
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls2.shift = au.shift) as shift_login_time, ROW_NUMBER() OVER(ORDER BY sum(update_qty * m.avg_cubes) DESC)
INSERT AvgCubesByShift(loginTime)
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200 as loginTime),
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id ),
INSERT AvgCubesByShift(loginTimeSeconds)
(select sum(datediff(s,rls.login_time,rls.logout_time)) as loginTimeSeconds),
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id ),
INSERT AvgCubesByShift(LogoutTime)
(select convert(varchar, max(rls.logout_time),100) as LogoutTime),
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and rls.user_id = itl.user_id )
-- (select sum(datediff(s,rls2.login_time,rls2.logout_time)) from rp_login_stats rls2 where rls2.login_time between convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 09:00:00' and convert(varchar, dateadd(day, 0, '02-APR-09'), 101) + ' 20:00:00' and au.shift = rls2.shift) as loginTimeSecondsShift
INTO AvgCubesByShift
FROM
rp_minor_average m,
rp_sku s,
rp_application_user au,
rp_inventory_transaction itl
WHERE
itl.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)
and convert(datetime, '02-APR-09' + ' 20:00:00', 101)
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '1ST P'
and (itl.from_loc_id <> 'SUSPENSE' or itl.to_loc_id <> 'SUSPENSE')
and from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
and s.sku_id = itl.sku_id
and m.mnr_cd = s.v_userdef1
and au.user_id = itl.user_id
and au.shift is not null
-- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply