没有联合的查询优化 [英] Query optimization without union

问题描述

是否有其他方法可以优化此查询。

Is there any other method to do this query optimized.

select ProductId,ImageName,ImageType, ROW_NUMBER() over (order by ProductId desc) RowId from
			(
				select p.id ProductId ,p.pic_image ImageName,'pic_image' ImageType
				from product p
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_image,'') <> '' and isnull(pe.pic_image,0)=0 
				union
				select p.id ProductId,p.pic_bimage ImageName,'pic_bimage' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_bimage,'') <> '' and isnull(pe.pic_bimage,0)=0
				union
				select p.id ProductId,p.pic_limage ImageName,'pic_limage' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_limage,'') <> '' and isnull(pe.pic_limage,0)=0 
				union
				select p.id ProductId,p.pic_blimage ImageName,'pic_blimage' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_blimage,'') <> '' and isnull(pe.pic_blimage,0)=0 
				union
				select p.id ProductId,p.pic_cimage ImageName,'pic_cimage' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage,'') <> '' and isnull(pe.pic_cimage,0)=0
				union
				select p.id ProductId,p.pic_climage ImageName,'pic_climage' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_climage,'') <> '' and isnull(pe.pic_climage,0)=0
				union
				select p.id ProductId,p.pic_cimage_2 ImageName,'pic_cimage_2' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_2,'') <> '' and isnull(pe.pic_cimage_2,0)=0
						
				union
				select p.id ProductId,p.pic_cimage_3 ImageName,'pic_cimage_3' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_3,'') <> '' and isnull(pe.pic_cimage_3,0)=0
				union
				select p.id ProductId,p.pic_cimage_4 ImageName,'pic_cimage_4' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_4,'') <> '' and isnull(pe.pic_cimage_4,0)=0
				union
				select p.id ProductId,p.pic_cimage_5 ImageName,'pic_cimage_5' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_5,'') <> '' and isnull(pe.pic_cimage_5,0)=0
				union
				select p.id ProductId,p.pic_cimage_6 ImageName,'pic_cimage_6' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_6,'') <> '' and isnull(pe.pic_cimage_6,0)=0
				union
				select p.id ProductId,p.pic_cimage_7 ImageName,'pic_cimage_7' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_7,'') <> '' and isnull(pe.pic_cimage_7,0)=0
				union
				select p.id ProductId,p.pic_cimage_8 ImageName,'pic_cimage_8' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_8,'') <> '' and isnull(pe.pic_cimage_8,0)=0
				union
				select p.id ProductId,p.pic_cimage_9 ImageName,'pic_cimage_9' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id]
				where isnull(p.pic_cimage_9,'') <> '' and isnull(pe.pic_cimage_9,0)=0
				union
				select p.id ProductId,p.pic_cimage_10 ImageName,'pic_cimage_10' ImageType
				from product p 
				left outer join iimages_edited pe on p.id = pe.[id] 
				where isnull(p.pic_cimage_10,'') <> '' and isnull(pe.pic_cimage_10,0)=0 
		)t

推荐答案

如果你在dbase中存储图像，然后有可能慢慢执行查询。

即使你发起一个简单的查询也需要时间。



它可能不是Union或Unionall的问题，因为使用的查询并不复杂。

If you are storing Images in dbase, then there is a chance of query executing slowly.
Even if you fire a simple query it would take time.

It may not be the problem of Union or Unionall because the query used is not complicated.

这篇关于没有联合的查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！