This SQL offer threw the timeout exception:
select Id, ProductId, WebPath, Image, FullPath, Width, Height
from pdt_ImgFiles where ImageType = 'Icon' and ImageRank in (0,1) and ProductId in
(29556, 25323, 25324, 25330, 25334, 25358, 25373, 25354, 25385, 25389, 25412, 25398, 25436, 25441, 25442, 25443, 25444, 25445, 25471, 25491, 25492, 25493, 25464, 25507, 25514, 25518, 25520, 25527, 25538, 25554, 25523, 25525, 25548, 25579, 25590, 25592, 25603, 25601, 25607, 25610, 25625, 25641, 25653, 25654, 25633, 25638, 25664, 25666, 25684, 25678)order by ImageRank
When we tested it in the database directly. We find it is only slow at the first time of running. When we run it the second time it will be fast.
<select id="GetIconsByProductIds" resultMap="PictureResult">
select Id, ProductId, WebPath, Image, FullPath, Width, Height
from pdt_ImgFiles where ImageType = 'Icon' and ImageRank in (0,1) and ProductId in ($Values$)
order by ImageRank
</select>
I need do find out a way how to increase the performance.Please help me!sql
select Id, ProductId, WebPath, Image, FullPath, Width, Height
from pdt_ImgFiles where ImageType = 'Icon' and ImageRank in (0,1) and ProductId in
(29556, 25323, 25324, 25330, 25334, 25358, 25373, 25354, 25385, 25389, 25412, 25398, 25436, 25441, 25442, 25443, 25444, 25445, 25471, 25491, 25492, 25493, 25464, 25507, 25514, 25518, 25520, 25527, 25538, 25554, 25523, 25525, 25548, 25579, 25590, 25592, 25603, 25601, 25607, 25610, 25625, 25641, 25653, 25654, 25633, 25638, 25664, 25666, 25684, 25678)order by ImageRank
When we tested it in the database directly. We find it is only slow at the first time of running. When we run it the second time it will be fast.
<select id="GetIconsByProductIds" resultMap="PictureResult">
select Id, ProductId, WebPath, Image, FullPath, Width, Height
from pdt_ImgFiles where ImageType = 'Icon' and ImageRank in (0,1) and ProductId in ($Values$)
order by ImageRank
</select>
I need do find out a way how to increase the performance.Please help me!sql
一般几百万数据的话,还是可以瞬时返回的。
把$values$替换后,在SQLServer执行,并生成执行计划贴上来看看
select Id, ProductId, WebPath, Image, FullPath, Width, Height from pdt_ImgFiles where ImageType = 'Icon' and ImageRank in (0,1) and ProductId in ($Values$) order by ImageRank