看你的执行计划: set showplan_text on or set statistics profile on
like '%青%岛%' 估计问题出于此。你这个like 查询干嘛这么写呢,。正常你一个链表查询 我查10多万的记录也不至于如此。 再之如果你并不是特别要求一次性查处所有记录的话,考虑分页查询。select top 50 * from table where table.id not in(select top * 50*page from table where tiaojian ) where tiaojian ='
试下从小集合里找交集~大数据才明显~ select DISTINCT c.archivesNo,c.registrationNo,c.paperProjectSeqNo,c.volNo,c.archivesTitle,c.paijiaNo,c.textMaterial,c.drawing,c.firstResponsible,c.transferUnit,c.licenseNo,c.archiveThickness,c.kaigongTime,c.jungongTime from (select * from ProjectInfo where status='1') a, (select * from ArchivesDetail where archivesTitle like '%青%?%') c, PaperArchives d where a.projectID=d.projectID and d.paperProjectSeqNo=c.paperProjectSeqNo order by c.paperProjectSeqNo,c.ArchivesNo
archivesTitle like '%青%岛%' 此句不会使用索引,而直接进行全表扫描!建议为archivesTitle建立全文索引!关于全文索引,很简单,请自行百度。
6W数据, 数据量太小了。 慢到什么程序?distinct 确定要加吗?
SELECT DISTINCT c.archivesNo, c.registrationNo, c.paperProjectSeqNo, c.volNo, c.archivesTitle, c.paijiaNo, c.textMaterial, c.drawing, c.firstResponsible, c.transferUnit, c.licenseNo, c.archiveThickness, c.kaigongTime, c.jungongTime FROM ProjectInfo AS a CROSS JOIN ArchivesDetail AS c CROSS JOIN PaperArchives AS d WHERE (a.status = '1') AND (c.archivesTitle LIKE '%青%岛%') AND (a.projectID = d.projectID) AND (d.paperProjectSeqNo = c.paperProjectSeqNo) ORDER BY c.paperProjectSeqNo, c.archivesNo 根据sql执行顺序,你sql中先执行三个table的笛卡尔连接,这三个表各有多少数据呢? 后面的条件非常耗时的是like条件,别的没法优化了。 建议先建立相关索引,再看看执行计划(ctrl +L)。
启动sql server 2005 的查询优化工具,把语句贴到里面运行优化工具,按照给出的提示操作!
估計是你建表或者SQL效率低造成的。
set showplan_text on
or
set statistics profile on
估计问题出于此。你这个like 查询干嘛这么写呢,。正常你一个链表查询 我查10多万的记录也不至于如此。 再之如果你并不是特别要求一次性查处所有记录的话,考虑分页查询。select top 50 * from table where table.id not in(select top * 50*page from table where tiaojian ) where tiaojian ='
select DISTINCT c.archivesNo,c.registrationNo,c.paperProjectSeqNo,c.volNo,c.archivesTitle,c.paijiaNo,c.textMaterial,c.drawing,c.firstResponsible,c.transferUnit,c.licenseNo,c.archiveThickness,c.kaigongTime,c.jungongTime
from (select * from ProjectInfo where status='1') a,
(select * from ArchivesDetail where archivesTitle like '%青%?%') c,
PaperArchives d
where a.projectID=d.projectID
and d.paperProjectSeqNo=c.paperProjectSeqNo
order by c.paperProjectSeqNo,c.ArchivesNo
此句不会使用索引,而直接进行全表扫描!建议为archivesTitle建立全文索引!关于全文索引,很简单,请自行百度。
慢到什么程序?distinct 确定要加吗?
c.archivesNo, c.registrationNo, c.paperProjectSeqNo, c.volNo,
c.archivesTitle, c.paijiaNo, c.textMaterial, c.drawing,
c.firstResponsible, c.transferUnit,
c.licenseNo, c.archiveThickness, c.kaigongTime, c.jungongTime
FROM ProjectInfo AS a CROSS JOIN
ArchivesDetail AS c CROSS JOIN
PaperArchives AS d
WHERE (a.status = '1') AND (c.archivesTitle LIKE '%青%岛%')
AND (a.projectID = d.projectID)
AND (d.paperProjectSeqNo = c.paperProjectSeqNo)
ORDER BY c.paperProjectSeqNo, c.archivesNo
根据sql执行顺序,你sql中先执行三个table的笛卡尔连接,这三个表各有多少数据呢?
后面的条件非常耗时的是like条件,别的没法优化了。
建议先建立相关索引,再看看执行计划(ctrl +L)。