Essentially two sub queries are soon, 1 s, but when the right join on, after a long time, a great god, please help have a look at
TABLEA data (10 million) and TABLEB (data) of article ten thousand of the ID was made in the two indexes, two separate query time soon, but very slowly, after a join on



SELECT 'ALL' AS 'the Line', e. [ID], E.M odel, e. [Produce Week] AS 'weekly' ISNULL (D.Q TY, 0) AS 'scanned, E.L OT AS' orders' (ISNULL (D.Q TY, 0) - E.L OT) AS 'difference'

The FROM

(select ID, COUNT (*) as' QTY from TABLEA GROUP BY ID) D

RIGHT JOIN

(SELECT [Model], [ID], [a LOT], [Produce Week] FROM TABLEB WHERE [Produce Week]='10 w' and [the Upload Date]='2021') E

ON D.I D=E.I D

The ORDER BY 'completion'

CodePudding user response:

Right after the join result set how many records, into a temporary table
try?
Which is saying 'completion' field

CodePudding user response:

That you have a look at the execution plan have what different

CodePudding user response:

Look the ID column should be the primary key, also added index, condition and ID='?

Query is added D.I D='results and the condition of no overtime is the same, this is don't understand your data and the result is what

And the ORDER BY 'completion', the sorting is useless, and what to do?

CodePudding user response:

AND ID='this code, the result is A ID=ID=' ' ' 'or B?

According to what you have said B table ID is the primary key, impossible=', conditions of ID='should be filtered on A table, and then not A and B table match, that the results of your' scanned columns are equal to zero?

There is no data and the results don't know what circumstance,

If not entangled with these, you can try the two queries child table into a temporary table, and then to query a temporary table, may improve the query speed,

CodePudding user response:

SELECT 'ALL' AS 'the Line', e. [ID], E.M odel, e. [Produce Week] AS 'weekly' ISNULL (D.Q TY, 0) AS 'scanned, E.L OT AS' orders' (ISNULL (D.Q TY, 0) - E.L OT) AS 'difference'
,
CONVERT (varchar, CONVERT (a decimal (10, 2), the CONVERT (a decimal (10, 2), (isnull (D.Q TY, 0)))/CONVERT (a decimal (10, 2), (E.L OT)) * 100)) + '%' as' completion '


The FROM

(select ID, COUNT (*) as' QTY from TABLEA GROUP BY ID) D

RIGHT JOIN

(SELECT [Model], [ID], [a LOT], [Produce Week] FROM TABLEB WHERE [Produce Week]='10 w' and [the Upload Date]='2021') E

ON D.I D=E.I D

The ORDER BY 'completion'

Completion is added it yourself

CodePudding user response:

Now the run down to 13 seconds, too long