同一SQL,在同一数据库中执行时间差很远
这一条是连接数据库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,是什么原因呢?请教高手?