很少用,我个人觉得OPENROWSET('SQLNCLI','Server=192.168.1.1;uid=sa;pwd=test;database=testdb', 'select * from tb1 where sendsite > ''2013-01-01''')这个可能不是在sql引擎内做,所以没有优化
try this,select qty from openrowset('SQLOLEDB','Server=192.168.1.1;uid=sa;pwd=test;database=testdb', 'select count(1) ''qty'' from tb1(nolock) where sendsite>''2013-01-01'' ') as AA
这个也是1s内出结果。关键是以前同事写的程序是通过下边的关联其他表取数据,查询要几分钟才出结果。想看看这种写法还能否优化。 select count(1) from OPENDATASOURCE('SQLNCLI','Data Source=192.168.1.1;User ID=sa;Password=test').testdb.dbo.tb1 as AA where sendsite > '2013-01-01'
如果有源代码,建议改为如2楼的写法. 或用链接服务器(link server).
我觉得,第1和2应该是直接从数据源取数据,因为不需要数据,而只需要条数,走的索引,所以速度非常快,这个我写了一个文章: 通过非聚集索引让select count(*) from 的查询速度提高几十倍、甚至千倍 http://blog.csdn.net/sqlserverdiscovery/article/details/12646371然后第3个,是先从表中把符合要求的数据都取出来之后,然后再求条数,所以速度就慢了。
select * from openrowset('SQLOLEDB','Server=192.168.20.101;uid=sa;pwd=200006;database=zqmis北京', ' CREATE TABLE #tab_TempItem ( ObjId varchar(50), yyybh varchar(50), yyymc varchar(50), gysmc varchar(50), spbh varchar (50), sptm varchar(50), spmc varchar(50), hqmc varchar(50), splbbh varchar(50), xsjsl int, Price MONEY ) --------------- DECLARE @ObjId VARCHAR(50) ------------------ Declare @yyybh VARCHAR(50) Declare @yyymc Varchar(50) Declare @gysmc Varchar(50) Declare @spbh Varchar(50) DECLARE @sptm VARCHAR(50) DECLARE @spmc VARCHAR(50) DECLARE @hqmc VARCHAR(50) DECLARE @splbbh VARCHAR(50) DECLARE @xsjsl INT; DECLARE @Price MONEY; DECLARE cr_Item CURSOR FOR SELECT ObjId,yyybh,yyymc, gysmc,spbh,smbm,spmc,hqmc,splbbh, xsjsl FROM posmxxs_201311 where posmxxs_201311.zdrq=''2013-11-28''and yyybh!=0 OPEN cr_Item FETCH NEXT from cr_Item INTO @ObjId,@yyybh,@yyymc, @gysmc,@spbh,@sptm,@spmc,@hqmc,@splbbh,@xsjsl WHILE @@FETCH_STATUS = 0 BEGIN IF @xsjsl = 1 SELECT @Price =(fsje-cbj*a.dwjl*fssl) FROM spdw b,mdspcbj c,posmxxs_201311 a WHERE a.ObjId=@ObjId and sptm=@sptm and b.Objid=c.spbh and sptm=smbm ; ELSE IF @xsjsl = 2 SELECT @Price = (fsje-cbj*a.dwjl*fssl) FROM spdw b,mdspcbj c,posmxxs_201311 a WHERE a.ObjId=@ObjId and sptm=@sptm and b.Objid=c.spbh and sptm=smbm ; ELSE SELECT @Price = (fsje-cbj*a.dwjl*fssl) FROM spdw b,mdspcbj c,posmxxs_201311 a WHERE a.ObjId=@ObjId and sptm=@sptm and b.Objid=c.spbh and sptm=smbm ;
FETCH NEXT from cr_Item INTO @ObjId, @yyybh,@yyymc,@gysmc,@spbh,@sptm,@spmc,@hqmc,@splbbh,@xsjsl
END CLOSE cr_Item DEALLOCATE cr_Item --SELECT * FROM #tab_TempItem where gysmc='''' SELECT yyybh,yyymc,sum(price) FROM #tab_TempItem where gysmc='''' or gysmc='''' or spbh='''' group by yyybh,yyymc order by yyybh,yyymc drop table #tab_TempItem ') 执行结果如下图 小弟刚学sql 望大哥大姐帮助解决 感激涕零!
'select * from tb1 where sendsite > ''2013-01-01''')这个可能不是在sql引擎内做,所以没有优化
from openrowset('SQLOLEDB','Server=192.168.1.1;uid=sa;pwd=test;database=testdb',
'select count(1) ''qty'' from tb1(nolock) where sendsite>''2013-01-01'' ') as AA
select count(1)
from OPENDATASOURCE('SQLNCLI','Data Source=192.168.1.1;User ID=sa;Password=test').testdb.dbo.tb1 as AA
where sendsite > '2013-01-01'
或用链接服务器(link server).
通过非聚集索引让select count(*) from 的查询速度提高几十倍、甚至千倍
http://blog.csdn.net/sqlserverdiscovery/article/details/12646371然后第3个,是先从表中把符合要求的数据都取出来之后,然后再求条数,所以速度就慢了。
from openrowset('SQLOLEDB','Server=192.168.20.101;uid=sa;pwd=200006;database=zqmis北京',
' CREATE TABLE #tab_TempItem
(
ObjId varchar(50),
yyybh varchar(50),
yyymc varchar(50),
gysmc varchar(50),
spbh varchar (50),
sptm varchar(50),
spmc varchar(50),
hqmc varchar(50),
splbbh varchar(50),
xsjsl int,
Price MONEY
)
---------------
DECLARE @ObjId VARCHAR(50)
------------------
Declare @yyybh VARCHAR(50)
Declare @yyymc Varchar(50)
Declare @gysmc Varchar(50)
Declare @spbh Varchar(50)
DECLARE @sptm VARCHAR(50)
DECLARE @spmc VARCHAR(50)
DECLARE @hqmc VARCHAR(50)
DECLARE @splbbh VARCHAR(50)
DECLARE @xsjsl INT;
DECLARE @Price MONEY;
DECLARE cr_Item CURSOR
FOR SELECT ObjId,yyybh,yyymc, gysmc,spbh,smbm,spmc,hqmc,splbbh, xsjsl FROM posmxxs_201311 where posmxxs_201311.zdrq=''2013-11-28''and yyybh!=0
OPEN cr_Item
FETCH NEXT from cr_Item INTO @ObjId,@yyybh,@yyymc, @gysmc,@spbh,@sptm,@spmc,@hqmc,@splbbh,@xsjsl
WHILE @@FETCH_STATUS = 0
BEGIN
IF @xsjsl = 1
SELECT @Price =(fsje-cbj*a.dwjl*fssl) FROM spdw b,mdspcbj c,posmxxs_201311 a WHERE a.ObjId=@ObjId and sptm=@sptm and b.Objid=c.spbh and sptm=smbm ;
ELSE IF @xsjsl = 2
SELECT @Price = (fsje-cbj*a.dwjl*fssl) FROM spdw b,mdspcbj c,posmxxs_201311 a WHERE a.ObjId=@ObjId and sptm=@sptm and b.Objid=c.spbh and sptm=smbm ;
ELSE
SELECT @Price = (fsje-cbj*a.dwjl*fssl) FROM spdw b,mdspcbj c,posmxxs_201311 a WHERE a.ObjId=@ObjId and sptm=@sptm and b.Objid=c.spbh and sptm=smbm ;
INSERT INTO #tab_TempItem
( ObjId,yyybh,yyymc,gysmc,spbh,sptm,spmc,hqmc,splbbh, xsjsl, Price)
VALUES ( @ObjId,@yyybh,@yyymc,@gysmc,@spbh,@sptm, @spmc,@hqmc,@splbbh,
@xsjsl,
@Price
);
FETCH NEXT from cr_Item INTO @ObjId, @yyybh,@yyymc,@gysmc,@spbh,@sptm,@spmc,@hqmc,@splbbh,@xsjsl
END
CLOSE cr_Item
DEALLOCATE cr_Item
--SELECT * FROM #tab_TempItem where gysmc=''''
SELECT yyybh,yyymc,sum(price) FROM #tab_TempItem where gysmc='''' or gysmc='''' or spbh=''''
group by yyybh,yyymc order by yyybh,yyymc
drop table #tab_TempItem
')
执行结果如下图 小弟刚学sql 望大哥大姐帮助解决 感激涕零!