CODEWITHSUNDEEP
sqlibm-midrangedb2-400
rabeery
rabeery

Reputation: 35

optimizing a sql query with a large number of records being joined and combined

This is my code currently and what is does is: it groups records with the same IDs(KEYVADD) and then it removes them based off of the having statement.

As it stands right now this query take 5-10 minutes to run because the audit file is very large and then joins and select statements. I am hoping to reduce this time but I'm struggling to do it in a way that will still return the same results. When I have tried to but what is in the HAVING into the WHERE statement it just pulls the orders with nulls in the status slots instead of the times instead of getting rid of the ID all together. 

DECLARE @paramdate DATETIME , @paramdatechar varchar(30),@warehouse int
set @warehouse = 711
set @paramdate= '2018-05-17 12:00:00.000'
set @paramdatechar = CONVERT(varchar(30),@paramdate,121)



exec('  select KEYVADD
    ,min(case when VALUADD=0 then timestmp else null end) as "Status0"
    ,min(case when VALUADD=2 then timestmp else null end) as "Status2"
    ,min(case when VALUADD=4 then timestmp else null end) as "Status4"
    ,min(case when VALUADD=5 then timestmp else null end) as "Status5"
    ,min(case when VALUADD=7 then timestmp else null end) as "Status7"
    ,min(case when VALUADD=8 then timestmp else null end) as "Status8"
    ,min(case when VALUADD=9 then timestmp else null end) as "Status9"
    ,min(nmdoh) as "Customer"
    ,min(c.scscn) as "Container"
    ,min(whsoh) as "Warehouse"
    ,min(preoh) as "Preorder"
    from Audit a
left outer join orderhp h on left(a.KEYVADD,7) = h.ONHOH
left outer join ordercnhpc on h.onhoh = c.onhcn
WHERE 
    whsoh = '''+@warehouse+'''
    and IMGTADD = ''A''
GROUP BY KEYVADD
HAVING(
     (min(case when VALUADD=2 then timestmp else null end) <= '''+ @paramdatechar +''')
        and (
                (
                min(preoh) = ''Y''
                and(  
                    (min(case when VALUADD=4 then timestmp else null end) IS NOT NULL)
                    or   (min(case when VALUADD=5 then timestmp else null end) IS NOT NULL)
                    or   (min(case when VALUADD=7 then timestmp else null end) IS NOT NULL)
                    or   (min(case when VALUADD=8 then timestmp else null end) IS NOT NULL)
                    or   (min(case when VALUADD=9 then timestmp else null end) IS NOT NULL)
                    )
                )
                or
                (
                min(preoh) = ''N''
                )
            )
        and(
                (
                    min(case when VALUADD=7 then timestmp else null end) IS NULL
                    and  min(case when VALUADD=8 then timestmp else null end) IS NULL
                    and  min(case when VALUADD=9 then timestmp else null end) IS NULL
                )
                or
                (
                    min(case when VALUADD=7 then timestmp else null end) >= '''+ @paramdatechar +'''
                    or   min(case when VALUADD=8 then timestmp else null end) >= '''+ @paramdatechar +'''
                    or   min(case when VALUADD=9 then timestmp else null end) >= '''+ @paramdatechar +'''
                )
            )
        ) ') at IBMAS400

Results:

    793841800 2018-05-16 14:46:24.5720000   2018-05-16 13:20:25.2250000 2018-05-16 14:46:36.8530000 NULL    NULL    2018-05-17 13:57:03.0230000 NULL    name 1                  711 N
    793843700 2018-05-16 14:46:24.6410000   2018-05-16 13:20:27.2830000 2018-05-16 14:46:36.8750000 NULL    NULL    2018-05-17 13:57:03.5800000 NULL    name 2                      711 N
    793847800 2018-05-16 14:46:24.7080000   2018-05-16 14:21:21.8600000 2018-05-16 14:46:36.9820000 NULL    NULL    2018-05-17 13:57:04.0010000 NULL    name 3                  711 N
    793849100 2018-05-16 14:46:24.7400000   2018-05-16 14:21:23.5210000 2018-05-16 14:46:37.0430000 NULL    NULL    2018-05-17 13:57:04.3380000 NULL    name 4                      711 N
    793855500 2018-05-16 15:49:01.7590000   2018-05-16 15:21:18.1300000 2018-05-16 15:49:15.5260000 NULL    NULL    2018-05-17 13:57:05.0660000 NULL    name 5                  711 N
    793856100 2018-05-16 15:49:01.7810000   2018-05-16 15:21:19.2200000 2018-05-16 15:49:15.5520000 NULL    NULL    2018-05-17 13:57:05.5630000 NULL    name 6                  711 N
    793865100 2018-05-16 19:54:46.2840000   2018-05-16 16:19:53.7890000 2018-05-16 19:54:57.1080000 NULL    NULL    2018-05-17 13:57:05.9330000 NULL    name 7                      711 N
    793871500 2018-05-16 19:54:46.3350000   2018-05-16 17:20:24.8500000 2018-05-16 19:54:57.1820000 NULL    NULL    2018-05-17 14:07:04.8690000 NULL    name 8                  711 N

I am hoping there is a way I can reduce the time on this either by changing how the select works or some other way and any help would be greatly appreciated!

Upvotes: 0

Views: 73

Answers (1)

Eric
Eric

Reputation: 267

https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-index-basics/

Can you try this and see if it is faster? Also why do you even need to join to the ordercnhp table if you commented out the field c.scscn in the select?

--,min(c.scscn) as "Container"

DECLARE @paramdate DATETIME , @paramdatechar varchar(30),@warehouse int
set @warehouse = 711
set @paramdate= '2018-05-17 12:00:00.000'
set @paramdatechar = CONVERT(varchar(30),@paramdate,121)



exec('  select KEYVADD
    ,min(case when VALUADD=0 then timestmp else null end) as "Status0"
    ,min(case when VALUADD=2 then timestmp else null end) as "Status2"
    ,min(case when VALUADD=4 then timestmp else null end) as "Status4"
    ,min(case when VALUADD=5 then timestmp else null end) as "Status5"
    ,min(case when VALUADD=7 then timestmp else null end) as "Status7"
    ,min(case when VALUADD=8 then timestmp else null end) as "Status8"
    ,min(case when VALUADD=9 then timestmp else null end) as "Status9"
    ,min(nmdoh) as "Customer"
    --,min(c.scscn) as "Container"
    ,min(whsoh) as "Warehouse"
    ,min(preoh) as "Preorder"
    from Audit a
left outer join orderhp h on left(a.KEYVADD,7) = h.ONHOH
-- Is the ordercnhp needed?
--left outer join ordercnhp c on h.onhoh = c.onhcn
WHERE 
    whsoh = '''+@warehouse+'''
    and IMGTADD = ''A''
GROUP BY KEYVADD
HAVING(
     (min(case when VALUADD=2 then timestmp else null end) <= '''+ @paramdatechar +''')
        and (
                (
                    min(preoh) = ''N''
                )
                or
                (
                    min(preoh) = ''Y''
                    and(  
                            (min(case when VALUADD in (4,5,7,8,9) then timestmp else null end) IS NOT NULL)
                        )
                )
            )
        and(
                (
                    min(case when VALUADD in (7,8,9) then timestmp else null end) IS NULL
                )
                or
                (
                    min(case when VALUADD in (7,8,9) then timestmp else null end) >= '''+ @paramdatechar +'''
                )
            )
        ) ') at IBMAS400

Upvotes: 1

Related Questions