下面这个SQL执行正常时很快,但加了top 1就很慢,请教高手?select top 1 it.guid
from ins_task it, ins_task_group itg
where it.group_guid = itg.guid
and itg.status = 1
and it.status = 0
and it.is_lock = 0
and exists (select itc.task_group_guid
from ins_task_credit itc
where it.group_guid = itc.task_group_guid
and itc.credit_used_date <= getdate()
and itc.credit_amount > itc.credit_used_amount
and itc.credit_user = 405)
and it.campaign_guid = '091028162800-7944-0271'
order by itg.priority desc
from ins_task it, ins_task_group itg
where it.group_guid = itg.guid
and itg.status = 1
and it.status = 0
and it.is_lock = 0
and exists (select itc.task_group_guid
from ins_task_credit itc
where it.group_guid = itc.task_group_guid
and itc.credit_used_date <= getdate()
and itc.credit_amount > itc.credit_used_amount
and itc.credit_user = 405)
and it.campaign_guid = '091028162800-7944-0271'
order by itg.priority desc
lz,加与不加top 你的sql执行速度相差很大么,你不加top前加了Order by 吗?如果是的话,那真希望高手来分析分析了。
如果数据很大,建议这样,应该会减少判断次数,不过不知道会不会对你的情况有帮助了:if exists(select itc.task_group_guid
from ins_task_credit itc
where it.group_guid = itc.task_group_guid
and itc.credit_used_date <= getdate()
and itc.credit_amount > itc.credit_used_amount
and itc.credit_user = 405)
begin
select top 1 it.guid
from ins_task it, ins_task_group itg
where it.group_guid = itg.guid
and itg.status = 1
and it.status = 0
and it.is_lock = 0
and it.campaign_guid = '091028162800-7944-0271'
order by itg.priority desc
end
1. 加合理的索引
2. 加 WITH(NOLOCK) 在查询语句中
from tb
order by col
option(fast 1)