SQL Query Help

Nikunj2101 · November 15, 2019, 6:05pm

I have rcently started working in SQL and need some help in query optimization.I am trying to achieve two measures "total_used" and "total_repair" in the final result set from each of the two tables "USAGE" and "Pair_Report" by making use of join but its making the performancy of query quite slow. Any suggestion/help on performance improvement wud be great. I have attached in the image the data example and sample data script is given at the bottom.

final query which i have written is this:

SELECT a.code, sum(a.total_used) as total_used, sum(b.total_pair) as total_pair FROM ( SELECT used_code AS code, b.sku as sku, COUNT(a.used_code) AS total_used FROM usage a LEFT JOIN pair_report b ON a.ma_number = b.ma_number and (a.ei = b.ei or a.ei = b.ei_out)
AND a.pair_date = b.pair_date WHERE a.pair_date >= '2019-10-01' and a.pair_date <= '2019-10-31' AND b.ma_number IS NOT NULL AND a.ma_number IS NOT NULL GROUP BY 1, 2 ) a

LEFT JOIN

( SELECT b.sku, COUNT(DISTINCT concat(b.ma_number, b.ei)) AS total_pair FROM usage a LEFT JOIN pair_report b ON a.ma_number = b.ma_number and (a.ei = b.ei or a.ei = b.ei_out) AND a.pair_date = b.pair_date WHERE a.pair_date >= '2019-10-01'and a.pair_date
<= '2019-10-31' AND b.ma_number IS NOT NULL AND a.ma_number IS NOT NULL GROUP BY 1 ) b

ON a.sku = b.sku group by 1

and here is data sample script:

CREATE TABLE #usage
(
Code VARCHAR(5),
Pair_Date DATETIME,
MA_NUMBER VARCHAR(5)
EI VARCHAR(5). Primary Key
)

CREATE TABLE #pair_report
(
SKU VARCHAR(5),
MA_NUMBER VARCHAR(5),
EI VARCHAR(5) Primary key,
Pair_Date DATETIME,
EI_OUT VARCHAR(5)
)

INSERT INTO #usage
(Code, Pair_Date, MA_NUMBER, EI)
VALUES
('A','20191112','1A','1A1'),
('A','20191112','1A','1A2'),
('A','20191112','1B','1B1'),
('B','20191112','1B','1BE')

INSERT INTO #PAIR_REPORT
(SKU, MA_NUMBER, EI, Pair_Date, EI_OUT)
VALUES
('FA','1A','1A1','20191112','1A1'),
('FA1','1A','1C1','20191112','1A2'),
('FA3','1B','1BE','20191112','1BE')

Any help on this would be great. I am kind of stuck into this issue from long.

yosiasz · November 15, 2019, 7:41pm

Please post indexed on tables columns involved in koins and where filters

Nikunj2101 · November 15, 2019, 7:55pm

Hi Tito,

I have added which field is the Primary key in both the table.

yosiasz · November 15, 2019, 7:57pm

I am referring to the columns in your big query not the temp tables you provided

Nikunj2101 · November 15, 2019, 8:12pm

Well,i haven't used any index in where clause,it's just the primary key ma_number which have already been set while designing usage and pair report table.

femiolan · November 15, 2019, 10:13pm

I'm not sure I understand what you need, but try:

select a.Code
,count(*)  total_used
,sum(b.cnt)  total_pair 
from #usage a
outer apply
(
select b.EI, 1 cnt
from #pair_report b
where b.MA_NUMBER = a.MA_NUMBER 
and a.Pair_Date = b.Pair_Date 
and a.ei = b.ei or a.ei = b.ei_out
group by   b.EI 
)  b

where a.pair_date >= '2019-10-01' and a.pair_date <= '2019-10-31'

group by a.Code

harishgg1 · November 16, 2019, 12:40pm

hi i tried to do this !!!

your SQL looks very very long !!!

i did something very small SQL .. hope it gives you idea !!!
if it helps great

please click arrow to the left for drop create data

drop table #usage 
go 

drop table #pair_report 
go 

CREATE TABLE #usage
(
Code VARCHAR(5),
Pair_Date DATETIME,
MA_NUMBER VARCHAR(5),
EI VARCHAR(5)
)

CREATE TABLE #pair_report
(
SKU VARCHAR(5),
MA_NUMBER VARCHAR(5),
EI VARCHAR(5) Primary key,
Pair_Date DATETIME,
EI_OUT VARCHAR(5)
)

INSERT INTO #usage
(Code, Pair_Date, MA_NUMBER, EI)
VALUES
('A','20191112','1A','1A1'),
('A','20191112','1A','1A2'),
('A','20191112','1B','1B1'),
('B','20191112','1B','1BE')

INSERT INTO #PAIR_REPORT
(SKU, MA_NUMBER, EI, Pair_Date, EI_OUT)
VALUES
('FA','1A','1A1','20191112','1A1'),
('FA1','1A','1C1','20191112','1A2'),
('FA3','1B','1BE','20191112','1BE')
go 

select * from #usage
select * from #pair_report 
go

please click arrow to the left for SQL

SELECT a.*, 
       b.total_pair 
FROM   (SELECT code, 
               Count(*) AS total_used 
        FROM   #usage 
        GROUP  BY code) a 
       JOIN (SELECT ma_number, 
                    Count(*) AS total_pair 
             FROM   #pair_report 
             GROUP  BY ma_number) b 
         ON a.code = Replace(b.ma_number, '1', '')

Nikunj2101 · November 17, 2019, 8:09am

Hi Harish,
Thanks for the suggestion but code and ma number with replace function can't be used as join condition.they are quite different in maximum records

harishgg1 · November 17, 2019, 9:37am

hi please explain the logic .. then i can try the SQL

how you get the numbers !!

Nikunj2101 · November 17, 2019, 10:23am

Thanks for the help Harish.
What i am trying to do is from Usage table i want the calculated field count of used_code by code and from pair_report it's count of total_pair but that by sku field .in final result i just want code,total_used_code which is calculated at code level and total_pair which is calculated at sku level.Also,here both the tables have common field ma_number and EI number.also,the EI number can either be equal to EI from pair report table or EI_OUT from pair report table.for eg-for Code A ,total_used_code will be 3 and the totalpair is 2 wich are 1A1A1 and 1A1C1 concat of ma_number and EI from pair report table.here EI from usage 1A1 is matching with 1A1 from pair report table and second 1A1 is matching with EI_out ie 1A2,thatswhy totalpair count is 2.

Let me know if you understood.

Nikunj2101 · November 17, 2019, 2:57pm

Hi Harish,

Let me put it in a simple manner.
I have 2 tables namely Usage and Pair_report.The usage consist of 4 fields Code,pair_date,MA_Number and EI in which combination of MA_Number and EI column makes the table unique.The other table Pair_report had 5 fields SKU,MA_Numbrr,EI,Pair_date and EI_OUT in which MA_Number and EI columb combination makes this table unique. What i want to achieve is a final result set consisting of fields code,total_used and total_pair column. The total_used is the no of code used in Usage table,for eg-A code is present in usage table 3 times,so total_used for code A will be 3.similarly for code B is 1. Now total pair field is count of concatenation of ma_number and EI.now both the tables are joined on the basis of fields MA_Numer,EI from usage table with MA_Number,EI or EI_Out in some cases.for eg-for Code A ,total_used_code will be 3 and the totalpair is 2 wich are 1A1A1 and 1A1C1 concat of ma_number and EI from pair report table.here EI from usage 1A1 is matching with 1A1 from pair report table and second 1A1 is matching with EI_out ie 1A2,thatswhy totalpair count is 2.

harishgg1 · November 17, 2019, 3:51pm

hi nikunj

seeing what you have written
i have come up with the following

please review

please click arrow to the left for DROP CREATE data

drop table #usage 
go 

drop table #pair_report 
go 

CREATE TABLE #usage
(
Code VARCHAR(5),
Pair_Date DATETIME,
MA_NUMBER VARCHAR(5),
EI VARCHAR(5)
)

CREATE TABLE #pair_report
(
SKU VARCHAR(5),
MA_NUMBER VARCHAR(5),
EI VARCHAR(5) Primary key,
Pair_Date DATETIME,
EI_OUT VARCHAR(5)
)

INSERT INTO #usage
(Code, Pair_Date, MA_NUMBER, EI)
VALUES
('A','20191112','1A','1A1'),
('A','20191112','1A','1A2'),
('A','20191112','1B','1B1'),
('B','20191112','1B','1BE')

INSERT INTO #PAIR_REPORT
(SKU, MA_NUMBER, EI, Pair_Date, EI_OUT)
VALUES
('FA','1A','1A1','20191112','1A1'),
('FA1','1A','1C1','20191112','1A2'),
('FA3','1B','1BE','20191112','1BE')
go 

select 'usage',* from #usage
select 'pair rpeort',* from #pair_report

please click arrow to the left for SQL

select code,count(*) 
from #usage
group by code 
go 

select substring(MA_NUMBER+EI,1,3),count(*)
from #pair_report
group by substring(MA_NUMBER+EI,1,3)

Nikunj2101 · November 17, 2019, 4:02pm

Hi Harish,

Is it possible to get both metric total_used and total_pair like i showed in final result set in one snapshot?

harishgg1 · November 17, 2019, 4:11pm

Hi nikunj

Yes its possible

You can do it
By joining on row number

Or by joining
A = A
B = B

Which one do you say ok

harishgg1 · November 17, 2019, 4:46pm

hi please see if this is OK .. this is using the approach
A = A
B = B

please click arrow to the left for SQL

SELECT a.*, 
       b.total_pair 
FROM   (SELECT code, 
               Count(*) AS total_used 
        FROM   #usage 
        GROUP  BY code) a 
       JOIN (SELECT Substring(ma_number + ei, 1, 3) AS numberEI, 
                    Count(*)                        AS total_pair 
             FROM   #pair_report 
             GROUP  BY Substring(ma_number + ei, 1, 3)) b 
         ON a.code = Substring(b.numberei, 2, 1)

Nikunj2101 · November 17, 2019, 7:35pm

Thank you very much harish.i will try it.