November 11, 2019 at 6:21 pm
?I have written down a query which is quite slow and wants it to be optimized in any way possible.Can anyone please suggest:
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.ra_number = b.ra_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 AND position NOT IN ('RR','rapped','FF') 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 AND position NOT IN ('RR', 'rapped','FF') GROUP BY 1 ) b
ON a.sku = b.sku group by 1
November 11, 2019 at 8:39 pm
I wonder if you are asking for more work from folks than they usually do here.
HOWEVER it would help if you also reformatted your code for readability and included some sample data. Obviously these sample tables won't have the sort of statistics that your real life data has, but folks could experiment with your query and verify that their experiments are valid.
Sample reformatting:
SELECT a.code, sum(a.total_used) as total_used,
sum(b.total_pair) as total_pair
FROM
(
SELECT a.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.ra_number = b.ra_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
AND position NOT IN
('RR','rapped','FF')
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'
-- i have a suspicion that when you are LEFT JOINING two tables
-- BUT THEN test to see if either of the keys are NULL, then
-- you should consider INNER JOINING instead.
AND b.ma_number IS NOT NULL
AND a.ma_number IS NOT NULL
AND position NOT IN ('RR', 'rapped','FF')
GROUP BY 1
) b
ON a.sku = b.sku group by 1
November 11, 2019 at 10:07 pm
for me, that OR statement is something i think i would switch to two separate queries, for each of the two sets.
ON a.ra_number = b.ra_number
and (a.ei = b.ei or a.ei = b.ei_out)
i would create a pair of temp tables with the shape of the data
and insert two different times for each query:
INSERT... SELECT...
LEFT JOIN pair_report b
ON a.ra_number = b.ra_number
and (a.ei = b.ei)
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
AND position NOT IN
('RR','rapped','FF')
INSERT...SELECT
LEFT JOIN pair_report b
ON a.ra_number = b.ra_number
and 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
AND position NOT IN
('RR','rapped','FF')
--repeat for ra_number
i
Lowell
November 13, 2019 at 7:35 pm
Thanks lowell but i dont need to insert data into the table ,its just getting the result by using the below two tables. i would be extremely thankful if you can help me out on this or suggest anything.
USAGE
Code pair date MA_NUMBER EI
A 2019/11/12 1A 1A1
A 2019/11/12 1A 1A2
A 2019/11/12 1B 1B1
B 2019/11/12 1B 1BE
PAIR_REPORT
SKU MA_NUMBER EI Pair_date EI_OUT
FA 1A 1A1 2019/11/12 1A1
FA1 1A 1C1 2019/11/12 1A2
FA3 1B 1BE 2019/11/12 1BE
Final Result
Code total_used total_pair
A 3 2
B 1 1
November 13, 2019 at 7:39 pm
Thanks for the advise .I have reformat the code and here is a data example. what i want is the final output from these two tables Usage and Pair report. appreciate if you suggest anything on it.
USAGE
Code pair date MA_NUMBER EI
A 2019/11/12 1A 1A1
A 2019/11/12 1A 1A2
A 2019/11/12 1B 1B1
B 2019/11/12 1B 1BE
PAIR_REPORT
SKU MA_NUMBER EI Pair_date EI_OUT
FA 1A 1A1 2019/11/12 1A1
FA1 1A 1C1 2019/11/12 1A2
FA3 1B 1BE 2019/11/12 1BE
Final Result
Code total_used total_pair
A 3 2
B 1 1
November 13, 2019 at 9:57 pm
In your first post, I specifically remember seeing an "ra_number" column. I don't see it in your sample data however. I remember this because one of the steps I went looking for to simply things was seeing where ma_number was used and that's when I spotted ra_number.
Also, I can see you reformatted your query (edited your original post), but it sort of looks worse 🙂
If you compare what you posted with the other posts of the same query you can see what I'm talking about.
Not all posts / questions get good answers unfortunately, that's why to be competitive for the experts time, you want to make the experts work easy so that they can manipulate and test your query if they can spare the time. HOWEVER you should try to treat this like a service call, to do everything you can to present the issue to the engineer (or the forum posters here) in as efficient a manner as possible, and this INCLUDES sample data.
For instance, I had reformatted your query to make the "structure" more apparent:
SELECT a.code, sum(a.total_used) as total_used,
sum(b.total_pair) as total_pair
FROM
(
SELECT a.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.ra_number = b.ra_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
AND position NOT IN
('RR','rapped','FF')
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'
-- i have a suspicion that when you are LEFT JOINING two tables
-- BUT THEN test to see if either of the keys are NULL, then
-- you should consider INNER JOINING instead.
AND b.ma_number IS NOT NULL
AND a.ma_number IS NOT NULL
AND position NOT IN ('RR', 'rapped','FF')
GROUP BY 1
) b
ON a.sku = b.sku group by 1
if you can compare the visual appearance of the above block of code with your reformatting work, hopefully you'll see what I mean that it came out less than optimal.
Then you should also format some sample data like so (obviously everyone can use the following for this particular post, just for future reference for any programming forum where you are looking for advice at):
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),
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')
Then folks can take your query as is, prefix it with CTE's to change the temp table reference to actual valid looking permanent table names that can then be used to test your actual query WITHOUT LEAVING PERMANENT OBJECTS ON THEIR OWN TEST SERVERS:
WITH USAGE AS (select * from #usage),
PAIR_REPORT AS (select * from #pair_report)
.... rest of query here ...
(There is also a caution about "select *", commonly refered to as "select star" but in this case, I think its ok, its a good excersize for you to determine why)
Still, I'm not able to proceed because there is no "ra_number" anywhere, and this is a further example of reasons to provide a complete running example of the issue you'd like to get help with, because then you might not have posted your example data that includes a column name mismatch.
I realise this could be more than folks like to read, but I thought maybe I'd post it anyways.
November 13, 2019 at 10:14 pm
Thank you for your suggestion.this is my first post in SQL forum so was not knowing on the way it's done. I will make sure goung onwards to make the code understandable as shown by you and a clear sample data.On the ra_number and ma_number both are same,in a hurry because i cant reveal the business field names i edited it incorrectly.Thank you for taking out time for looking into it and apologies for the inconveniences.Nikita
November 14, 2019 at 2:22 pm
This reply has been reported for inappropriate content.
Theres also a mismatch between the column reference 'code' and 'used_code', and also the column 'position' isn't anywhere to be seen.
I still couldn't get it to work even trying to fix those, course I'm not one of the experts I was talking about LOL
edit: also check out this e-book, its pretty good and free! https://www.red-gate.com/library/inside-the-sql-server-query-optimizer
Also I wouldn't discard the temporary table suggestion, as I've had success coaxing better performance from SQL server using them on occasion.
and finally, LOL at the "reported for inappropriate content"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply