这一条是连接数据库A中的自动生成的sql
select g.passdate as fcdate ,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode, g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,
g.quotedfcy,g.fcy, (select top 1 g2.ordcode from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and
g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordcode2, (select top 1 g2.ordicode from invoice i2,invoiceg g2 where
i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordicode2,
(select sum(g2.fcy ) from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex
and g2.invgidx = g.invgidx and g2.keyflags >= 4 group by g2.invgidx) as fcy2 from invoiceg g,invoice i where i.invicode = g.invicode and
g.passdate >= '2006-06-01' and (i.sheetcode in('1211321','1211323','1211324')) order by g.passdate,g.ccode asc执行了14秒
这一条是连接数据库B中的自动生成的sql，也是程序自动生成的select g.passdate as fcdate ,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode, g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,
g.quotedfcy,g.fcy, (select top 1 g2.ordcode from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and
g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordcode2, (select top 1 g2.ordicode from invoice i2,invoiceg g2 where
i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordicode2,
(select sum(g2.fcy ) from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex
and g2.invgidx = g.invgidx and g2.keyflags >= 4 group by g2.invgidx) as fcy2 from invoiceg g,invoice i where i.invicode = g.invicode and
g.passdate >= '2006-06-01' and (i.sheetcode in('1211321','1211323','1211324')) order by g.passdate,g.ccode asc执行了好几分钟。
就算把它们COPY到查询分析器中，在同一数据库中运行也会是一样的时间差。
真是完全一模一样的SQL，是什么原因呢？请教高手？
select g.passdate as fcdate ,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode, g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,
g.quotedfcy,g.fcy, (select top 1 g2.ordcode from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and
g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordcode2, (select top 1 g2.ordicode from invoice i2,invoiceg g2 where
i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordicode2,
(select sum(g2.fcy ) from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex
and g2.invgidx = g.invgidx and g2.keyflags >= 4 group by g2.invgidx) as fcy2 from invoiceg g,invoice i where i.invicode = g.invicode and
g.passdate >= '2006-06-01' and (i.sheetcode in('1211321','1211323','1211324')) order by g.passdate,g.ccode asc执行了14秒
这一条是连接数据库B中的自动生成的sql，也是程序自动生成的select g.passdate as fcdate ,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode, g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,
g.quotedfcy,g.fcy, (select top 1 g2.ordcode from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and
g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordcode2, (select top 1 g2.ordicode from invoice i2,invoiceg g2 where
i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordicode2,
(select sum(g2.fcy ) from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = '1211535' and g.invicodex = g2.invicodex
and g2.invgidx = g.invgidx and g2.keyflags >= 4 group by g2.invgidx) as fcy2 from invoiceg g,invoice i where i.invicode = g.invicode and
g.passdate >= '2006-06-01' and (i.sheetcode in('1211321','1211323','1211324')) order by g.passdate,g.ccode asc执行了好几分钟。
就算把它们COPY到查询分析器中，在同一数据库中运行也会是一样的时间差。
真是完全一模一样的SQL，是什么原因呢？请教高手？
然后执行SQL语句，从执行计划中看一下2个对比
--我自己给你做了实验,这两条语句完全相等
declare @Sql1 varchar(4000)
set @sql1 =
'select g.passdate as fcdate ,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode, g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,g.quotedfcy,g.fcy,(select top 1 g2.ordcode from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = ''1211535'' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordcode2, (select top 1 g2.ordicode from invoice i2,invoiceg g2where i2.invicode = g2.invicode and i2.sheetcode = ''1211535'' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordicode2,(select sum(g2.fcy ) from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = ''1211535'' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 group by g2.invgidx) as fcy2 from invoiceg g,invoice i where i.invicode = g.invicode and g.passdate >= ''2006-06-01'' and (i.sheetcode in(''1211321'',''1211323'',''1211324'')) order by g.passdate,g.ccode asc'
declare @i int
set @i =200
while @i>1
begin
select @sql1 =replace(@sql1 ,' ','')
set @i =@i -1
end
print @sql1declare @Sql2 varchar(4000)
set @sql2 =
'select g.passdate as fcdate ,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode, g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,g.quotedfcy,g.fcy, (select top 1 g2.ordcode from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = ''1211535'' andg.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordcode2, (select top 1 g2.ordicode from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode =''1211535'' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 ) as ordicode2, (select sum(g2.fcy ) from invoice i2,invoiceg g2 where i2.invicode = g2.invicode and i2.sheetcode = ''1211535'' and g.invicodex = g2.invicodex and g2.invgidx = g.invgidx and g2.keyflags >= 4 group by g2.invgidx) as fcy2 from invoiceg g,invoice i where i.invicode = g.invicode and g.passdate >=''2006-06-01'' and (i.sheetcode in(''1211321'',''1211323'',''1211324'')) order by g.passdate,g.ccode asc'
set @i =200
while @i>1
begin
select @sql2 =replace(@sql2 ,' ','')
set @i =@i -1
end
print @sql2if @sql1 =@sql2
print '1'
else
print '0'
--结果selectg.passdateasfcdate,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode,g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,g.quotedfcy,g.fcy,(selecttop1g2.ordcodefrominvoicei2,invoicegg2wherei2.invicode=g2.invicodeandi2.sheetcode='1211535'andg.invicodex=g2.invicodexandg2.invgidx=g.invgidxandg2.keyflags>=4)asordcode2,(selecttop1g2.ordicodefrominvoicei2,invoicegg2wherei2.invicode=g2.invicodeandi2.sheetcode='1211535'andg.invicodex=g2.invicodexandg2.invgidx=g.invgidxandg2.keyflags>=4)asordicode2,(selectsum(g2.fcy)frominvoicei2,invoicegg2wherei2.invicode=g2.invicodeandi2.sheetcode='1211535'andg.invicodex=g2.invicodexandg2.invgidx=g.invgidxandg2.keyflags>=4groupbyg2.invgidx)asfcy2frominvoicegg,invoiceiwherei.invicode=g.invicodeandg.passdate>='2006-06-01'and(i.sheetcodein('1211321','1211323','1211324'))orderbyg.passdate,g.ccodeasc
selectg.passdateasfcdate,g.chauffeur,i.predate,g.ccode,g.invicode,i.invcode,g.carnum,g.invtype,g.waycode,g.wayicode,g.ccodetrust,g.ordcode,g.ordicode,g.quotedfcy,g.fcy,(selecttop1g2.ordcodefrominvoicei2,invoicegg2wherei2.invicode=g2.invicodeandi2.sheetcode='1211535'andg.invicodex=g2.invicodexandg2.invgidx=g.invgidxandg2.keyflags>=4)asordcode2,(selecttop1g2.ordicodefrominvoicei2,invoicegg2wherei2.invicode=g2.invicodeandi2.sheetcode='1211535'andg.invicodex=g2.invicodexandg2.invgidx=g.invgidxandg2.keyflags>=4)asordicode2,(selectsum(g2.fcy)frominvoicei2,invoicegg2wherei2.invicode=g2.invicodeandi2.sheetcode='1211535'andg.invicodex=g2.invicodexandg2.invgidx=g.invgidxandg2.keyflags>=4groupbyg2.invgidx)asfcy2frominvoicegg,invoiceiwherei.invicode=g.invicodeandg.passdate>='2006-06-01'and(i.sheetcodein('1211321','1211323','1211324'))orderbyg.passdate,g.ccodeasc
1