不好意思,最近比较忙我的SQL如下: select ta.*,tb.BillNo as BillNoCX from (select GoodID,ScanCode as ScanCodID,GoodName,Spec,Unit,FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID from jcv_goodbaseinfo where (ScanCode='0101010003' or goodid='0101010003' or goodname like '%0101010003%')) ta left join jcpscxinfo tb on ta.goodid=tb.goodid
TRY: select TA.GoodID,TA.ScanCode as ScanCodID,TA.GoodName,TA.Spec,TA.Unit,TA.FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID ,tb.BillNo as BillNoCX from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid where (ScanCode='0101010003' or goodid='0101010003' or goodname like '%0101010003%')
TRY:select TA.GoodID,TA.ScanCode as ScanCodID,TA.GoodName,TA.Spec,TA.Unit,TA.FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID ,tb.BillNo as BillNoCX from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid where ScanCode='0101010003' UNION select TA.GoodID,TA.ScanCode as ScanCodID,TA.GoodName,TA.Spec,TA.Unit,TA.FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID ,tb.BillNo as BillNoCX from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid WHERE goodid='0101010003' UNION select TA.GoodID,TA.ScanCode as ScanCodID,TA.GoodName,TA.Spec,TA.Unit,TA.FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID ,tb.BillNo as BillNoCX from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid WHERE goodname like '%0101010003%'
估计这些修改没什么作用,关键goodname like '%0101010003%'是否可以去掉
to NBDBA: 我在程序中实验过了,去掉键goodname like '%0101010003%' 后检索时间还是1.40秒 和不去差不多
换硬件索引是很有效的方法
在Management Studio中选中查询语句,然后按“ctrl + L” 查看执行计划,尽量避免出现全表扫描(通过建索引),哪种操作占用的资源比重大就重点对这种操作进行优化。
http://topic.csdn.net/u/20080716/11/2317d040-48e7-42da-822e-040b4c55b46d.htmlSQL语句优化汇总
(http://topic.csdn.net/u/20080716/11/2317d040-48e7-42da-822e-040b4c55b46d.html)
select ta.*,tb.BillNo as BillNoCX from (select GoodID,ScanCode as ScanCodID,GoodName,Spec,Unit,FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID from jcv_goodbaseinfo where (ScanCode='0101010003' or goodid='0101010003' or goodname like '%0101010003%')) ta left join jcpscxinfo tb on ta.goodid=tb.goodid
按照你的方法查出来开销最大的是:
http://hi.csdn.net/space-108367-do-album-picid-925700.html然后我该怎么办
select TA.GoodID,TA.ScanCode as ScanCodID,TA.GoodName,TA.Spec,TA.Unit,TA.FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID
,tb.BillNo as BillNoCX
from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid
where (ScanCode='0101010003' or goodid='0101010003' or goodname like '%0101010003%')
,tb.BillNo as BillNoCX
from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid
where ScanCode='0101010003'
UNION
select TA.GoodID,TA.ScanCode as ScanCodID,TA.GoodName,TA.Spec,TA.Unit,TA.FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID
,tb.BillNo as BillNoCX
from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid
WHERE goodid='0101010003'
UNION
select TA.GoodID,TA.ScanCode as ScanCodID,TA.GoodName,TA.Spec,TA.Unit,TA.FactCode as FactCodID,RetailPric,MemPric,WholeSalePric,STBYPricA,STBYPricB,STBYPricC,STBYPricD,STBYPricE,LogPacks,goodtypeid as GoodType,SpeOffFlag,CXRange,MemberBeginLevel,MemberEndLevel,CXPric,CXKL,GoodPrtyID
,tb.BillNo as BillNoCX
from jcv_goodbaseinfo ta left join jcpscxinfo tb on ta.goodid=tb.goodid
WHERE goodname like '%0101010003%'
我在程序中实验过了,去掉键goodname like '%0101010003%' 后检索时间还是1.40秒 和不去差不多
你的表中只有4000条数据,问题:
1、你的表有多大,
2、你查询T-sql语句,
3、你在查询时,电脑是否有其他操作,
4、你的数据库是装在本机还是远程连接,远程连接的话(需要知道网络连接是否正常)。
5、服务器硬件。
1、我的数据库是安装在本机的
2、查询的时候没有其他操作
3、服务器硬件Core i3 2.13G 内存2G