April 7, 2009 at 6:54 am
Good morning!
I was wondering if I could get some import from the community please? I think this is where I post this sort of inquiry?
I have a query where I am using SELECTS, as well as SUB-SELECTS. Then unioning all of them. I am fairly new to SQL and would like some input. Currently, this query takes over 10 minutes to run. So, my question is: Given this query, is there a way (or more than one way), but the best way/practive of doing this, where I can improve the performance of this query?
Here is the query:
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,
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 ) as shift_total_cubes,
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
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) AS 'RowNumber',
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200
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 ) as loginTime,
(select sum(datediff(s,rls.login_time,rls.logout_time))
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 ) as loginTimeSeconds,
(select convert(varchar, max(rls.logout_time),100)
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 ) as LogoutTime
-- (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
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
-- ORDER BY au.shift, RowNumber
union all
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,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
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 ) as shift_total_cubes,
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and 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 where rls.login_time
between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and rls.user_id = itl.user_id ) as loginTime,
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and rls.user_id = itl.user_id ) as loginTimeSeconds,
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and rls.user_id = itl.user_id ) as LogoutTime
-- (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
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' + ' 09:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 01:00:00'
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '2ND 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
-- ORDER BY au.shift, RowNumber
-----------------------------------------
--3RD SHIFT
-----------------------------------------
union all
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,
rp_inventory_transaction itl2,
rp_application_user au2,
rp_minor_average m2
where itl2.dstamp between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
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 ) as shift_total_cubes,
(select sum(datediff(s,rls2.login_time, rls2.logout_time))
from rp_login_stats rls2
where rls2.login_time between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and 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 where rls.login_time
between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and rls.user_id = itl.user_id ) as loginTime,
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and rls.user_id = itl.user_id ) as loginTimeSeconds,
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls where rls.login_time
between convert(datetime, '02-APR-09' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and rls.user_id = itl.user_id ) as LogoutTime
-- (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
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' + ' 18:00:00', 101)
and dateadd(day, 1, '02-APR-09') + ' 11:00:00'
and itl.code in ('Putaway', 'Pick', 'Relocate')
-- and au.shift in ('1ST P', '2ND P', '3RD P')
and au.shift = '3RD 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
ORDER BY au.shift, RowNumber
I would GREATLY appreciate any feedback. As I had said, I am fairly new to this.
Thank you very much.
April 7, 2009 at 8:19 am
Is this something where you can provide the table create scripts and some insert statements for sample data? Ideally, also include the desired output.
I can see a few things that could probably be improved in the query, but I can't test anything on it.
One question that comes to mind immediately is why have all those string functions on the dates and times? All the April dates, I mean.
- 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 8:41 am
I agree with GSquared. Some sample structures & data will make it easier to test.
However a few things jump out of this right away:
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,
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 ) as shift_total_cubes,
What you have there is a SELECT within the SELECT statement. depending on the structures, the data, etc., you're likely to see some pretty poor performance from this type of querying. In general, I'd recommend changing this to a sub-select as part of the FROM clause and JOIN it to the rest of your query.
You're also likely to run into performance issues around this type of code
and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))
Again, you might be better off, depending on structures, data, etc., moving this into a LEFT JOIN and checking for NULL values in the WHERE clause of the main part of the query, or in this case, the sub-query.
Without execution plans, sample structures, etc., it's hard to make anything more than general statements about how to improve the code. Those functions that GSquared mentioned on your date columns are going to prevent index seek operations which will slow things down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2009 at 8:46 am
Thank you for you response.
This is code left over from the person I replaced. However, I am not an SQL person per se... So, that being said, I'm a bit lost with this. I want to learn of course, but seeking some help and insight first.
To answer the string functions on the date and time, I have no idea. :-/
I can see if I can create some table scripts and get some sample data as well.
Thanks again!
Without that though, can anyone show me what I can do from here as-is?
April 7, 2009 at 8:59 am
Can you get the actual execution plan and post it as a zip file?
Here's a video showing how to do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2009 at 9:02 am
Instead of all the hard-coded dates, it looks like you could use a CTE like this:
declare @Date datetime;
select @Date = dateadd(day, datediff(day, 0, getdate()), 0); -- Removes time from date
;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))
Change the value for @Date to an input parameter (but keep the part that removes the time, just replace getdate() with the parameter), and you can query this for any date you want.
Join the CTE to the login time and logout time data, and you have your shifts, without the more complex unions you're currently using. Will definitely improve performance.
- 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 9:37 am
As Grant and GSquared have pointed out, there's abundant scope for improvement in this query, to make it fast and to make it pretty. Here's the first query of the UNION reformatted to account for dates and to highlight correlated subqueries. Some table joins have been standardised too.
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
--
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as loginTime, -- CORRELATED TO rp_inventory_transaction itl
--
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as loginTimeSeconds, -- CORRELATED TO rp_inventory_transaction itl
--
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as LogoutTime -- CORRELATED TO rp_inventory_transaction itl
--- (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
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
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
--- and au.shift in ('1ST P', '2ND P', '3RD P')
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'))
--- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
The last three correlated subqueries in the SELECT list could be combined into a derived table in the FROM list - this is probably the first thing I'd do.
Are ya really sure you wanna do this? 😎
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 9:44 am
Like I said... Being new at this, I am not sure what the best way is to do this. I just know it runs VERY slow. A little over 11 minutes for 27 rows. :-/
I appreciate all of your help.
I just want to know the best way to do it, and honestly, I am still learning all the lingo.
Thanks!
April 7, 2009 at 9:50 am
donato1026 (4/7/2009)
Like I said... Being new at this, I am not sure what the best way is to do this. I just know it runs VERY slow. A little over 11 minutes for 27 rows. :-/I appreciate all of your help.
I just want to know the best way to do it, and honestly, I am still learning all the lingo.
Thanks!
Trust me, we totally understand where you're coming from. Everyone has to start somewhere if they want to get anywhere.
Just let us know as you come across stuff you don't understand in what we're posting, and we can help you figure it out.
If you can, posting the table definitions and some sample data, will greatly help us to help you. I started rewriting the proc, but I'm running into the problem of not being able to test my code.
- 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 9:58 am
I have made the CREATE TABLE scripts for now... I can post those and then get sample data over. Would that help?
April 7, 2009 at 10:18 am
Chris Morris (4/7/2009)
As Grant and GSquared have pointed out, there's abundant scope for improvement in this query, to make it fast and to make it pretty. Here's the first query of the UNION reformatted to account for dates and to highlight correlated subqueries. Some table joins have been standardised too.
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
--
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as loginTime, -- CORRELATED TO rp_inventory_transaction itl
--
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as loginTimeSeconds, -- CORRELATED TO rp_inventory_transaction itl
--
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as LogoutTime -- CORRELATED TO rp_inventory_transaction itl
--- (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
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
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
--- and au.shift in ('1ST P', '2ND P', '3RD P')
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'))
--- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
The last three correlated subqueries in the SELECT list could be combined into a derived table in the FROM list - this is probably the first thing I'd do.
Are ya really sure you wanna do this? 😎
Believe it or not... This is actually taking longer... 14 minutes for 9 rows.
April 7, 2009 at 10:21 am
Chris Morris (4/7/2009)
As Grant and GSquared have pointed out, there's abundant scope for improvement in this query, to make it fast and to make it pretty. Here's the first query of the UNION reformatted to account for dates and to highlight correlated subqueries. Some table joins have been standardised too.
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
--
(select cast(sum(datediff(s,rls.login_time,rls.logout_time))as decimal(9,2)) / 25200
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as loginTime, -- CORRELATED TO rp_inventory_transaction itl
--
(select sum(datediff(s,rls.login_time,rls.logout_time))
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as loginTimeSeconds, -- CORRELATED TO rp_inventory_transaction itl
--
(select convert(varchar, max(rls.logout_time),100)
from rp_login_stats rls
where rls.login_time between @Startdate and @Enddate
and rls.user_id = itl.user_id ) as LogoutTime -- CORRELATED TO rp_inventory_transaction itl
--- (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
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
WHERE itl.dstamp between @Startdate and @Enddate
and itl.code in ('Putaway', 'Pick', 'Relocate')
--- and au.shift in ('1ST P', '2ND P', '3RD P')
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'))
--- and au.user_id = 'AJJ'
group by itl.user_id, au.notes, au.shift
The last three correlated subqueries in the SELECT list could be combined into a derived table in the FROM list - this is probably the first thing I'd do.
Are ya really sure you wanna do this? 😎
Believe it or not... this took longer... 14:02 for 9 rows.
April 7, 2009 at 10:21 am
Haha that's okay mate, none of the speedy-up stuff has been done yet! 😀
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 10:23 am
OHHH! Ok. Sorry...
April 7, 2009 at 10:59 am
I wish there was a tool out there that analyzed your queries and syntax and made suggestions on how to improve upon it.
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply