SQLTeam.com | Weblogs | Forums

SQL Query Help

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.

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

Hi Tito,

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

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

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.

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

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 :slight_smile: :slight_smile:

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', '')

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

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

how you get the numbers !!
image

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.

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.

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)

Hi Harish,

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

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

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)

image

Thank you very much harish.i will try it.