Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to change full join to left join +union all in sql. The introduction in the article is very detailed and has certain reference value. Interested friends must read it!

Today, I received a request to rewrite the logic of a report. After the modification, I ran it again and found that the operation timed out.

Because of special reasons, unable to access the client server, unable to view sql execution plan, unable to know the index situation in the table, so try to optimize from the rewriting of the statement.

The original sentence is as follows:

select isnull(vv.customer_id,v.customer_id) as customer_id, isnull(vv.business_date,replace(v.business_date,'-','')) as business_date, v.prod_id, v.sales, vv.visit_count, v.all_salesfrom ( SELECT a.customer_id , max(month)+'-01' as business_date, a.PROD_ID , SUM(CAST(VALUE AS NUMERIC(38, 3))) sales, sum(SUM(CAST(VALUE AS NUMERIC(38, 3)))) over(partition by a.customer_id) as all_sales FROM TB_IMPORT_SALES a WHERE a.customer_id IS NOT NULL AND a.PROD_ID IS NOT NULL and a.month='2016-11' GROUP BY a.customer_id , a.PROD_ID)vfull join( SELECT customer_id, max(a.business_date) as business_date, COUNT(*) AS VISIT_COUNT FROM TB_CALL_STORE a WITH(NOLOCK) inner join TB_TIME d on a.business_date = d.t_date where d.section ='2016-11' GROUP BY customer_id)vvon v.customer_id = vv.customer_id

Originally it was left join. Although the query was slow, it could be found in 2 minutes. Now according to the business requirements, you need to see all the data, so it was changed to full join. After changing it, you could not find the result in 5 minutes.

II. Rewritten code

select v.customer_id, replace(max(v.business_date),'-','') as business_date, v.prod_id, max(v.sales_volume) sales_volume , max(v.visit_count) visit_count, max(v.all_sales_volume) all_sales_volumefrom ( SELECT a.customer_id , max(biz_month)+'-01' as business_date, a.PROD_ID , SUM(CAST(VALUE1 AS NUMERIC(38, 8))) sales_volume, sum(SUM(CAST(VALUE1 AS NUMERIC(38, 8)))) over(partition by a.customer_id) as all_sales_volume, null as visit_count FROM TB_IMPORT_SALES a WHERE a.customer_id IS NOT NULL AND a.PROD_ID IS NOT NULL and a.month='2016-11' GROUP BY a.customer_id , a.PROD_ID union all SELECT customer_id, max(a.business_date) as business_date, p.prod_id, null, null, COUNT(*) AS VISIT_COUNT FROM TB_CALL_STORE a WITH(NOLOCK) cross apply ( select top 1 prod_id from TB_PRODUCT with(nolock) )p inner join TB_TIME d on a.business_date = d.t_date where d.section ='2016-11' GROUP BY customer_id,p.prod_id)vgroup by v.customer_id, v.prod_id

Because the code itself is relatively simple, there is no way to simplify it further, and because it cannot connect to the server, other methods are not used, and there is no way to analyze what is causing it to run so slowly.

Think about it, full join is essentially 2 left join+union, nothing more than merging data, so try to use union all to merge data directly, now change to unoin all Finally, there is no need for full join.

However, considering that there is no prod_id field in the second paragraph code, cross apply is added to the second paragraph code to take out the id of a product randomly, so that there is a prod_id field, which can be merged.

After the modification, the speed dropped to 10 seconds.

The above is all the content of this article "how to change full join to left join +union all" in sql, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.