SQL 优化问题? ************ 页面打开特慢, 帮看看其中的SQL 有什么好的方案?
<%
do while not rs.eof
cigbrandcode=rs( "cigbrand ")
printnum1=rs( "printnum1 ")
%>
<%
'--------------------- 通过卷烟代码，查询卷烟名称；
sql1= "select * from qjcf31_barcodeprinter.dbo.CIGBRAND where CIGBRANDCODE= ' "+cigbrandcode+ " ' "
rs1.open sql1,conn,1,1
if not rs1.eof then
cigbrand=rs1( "cigbrand ")
end if
rs1.close
%>
<%
'--------------------- 通过卷烟代码，查询排产数/读码数；
sql1= "SELECT sum(arrangenum) as arrangenum1,sum(downnum) as downnum1 "
sql1=sql1+ " FROM ( "
sql1=sql1+ " SELECT * FROM qjcf31_barcodeprinter.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF32_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF33_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF34_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF35_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF36_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF37_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF38_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " UNION SELECT * FROM QJCF39_BARCODEPRINTER.dbo.arrange where cigbrandcode= ' "+cigbrandcode+ " ' and (convert(char(10),producedate,120)> = ' "+begindate+ " ' and convert(char(10),producedate,120) <= ' "+enddate+ " ') "
sql1=sql1+ " ) X "
rs1.open sql1,conn,1,1
arrangenum1=rs1( "arrangenum1 ")
downnum1=rs1( "downnum1 ")
col_printnum3=col_printnum3+arrangenum1
col_printnum4=col_printnum4+downnum1
rs1.close
%>
[解决办法]
尝试将QJCF31_BARCODEPRINTER至QJCF39_BARCODEPRINTER这多个数据库实例中同名表建为一个索引视图，然后在程序中从视图检索数据。
[解决办法]
子陌,你的方案不行吧,帮助里说索引试图引用的表必须在同一数据库中,而且不能有union等运算符的
[解决办法]
设计有问题吧
在现有的情况下,想办法通过连接查询代替循环减少查询次数应该是增加效率的好方法
[解决办法]
用存储过程，减少前台程序与数据库的交互次数
