declare @DateStart datetime
declare @DateEnd Datetime
Set @DateStart='2011-12-21'
Set @DateEnd='2012-05-22'
SELECT ID,City,County,CompanyName,Contact,Mobile,Telephone,LogNum,OrderNum,OrderAmount FROM
(
SELECT tb_user.ID,tb_user.UserTypeID,tb_AreaCity.City,tb_AreaCounty.County,CompanyName,Contact,Mobile,Telephone,ISNULL(b.LogNum,0) as LogNum,ISNULL(a.OrderNum,0) as OrderNum,ISNULL(a.OrderAmount,0) as OrderAmount from
tb_user
INNER JOIN tb_AreaCounty ON CountyID=tb_AreaCounty.ID
INNER JOIN tb_AreaCity ON CityID=tb_AreaCity.ID
LEFT JOIN (select UserID,Count(1) as OrderNum,Sum(Money) as OrderAmount from tb_OrderSale where OrderTime>@DateStart AND OrderTime<@DateEnd AND Status>=10 Group By UserID) a on tb_User.ID=a.UserID
LEFT JOIN (select UserID,Count(1) as LogNum from tb_UserLog where LogTime>@DateStart AND LogTime<@DateEnd Group By UserID) b on tb_User.ID=b.UserID
) c
WHERE UserTypeID=1 and OrderNum>0
order by OrderNum DESC
在服务器端用SQL管理器执行,1秒内结束,cpu占用50%。
写在网页里,居然15秒,CPU占用100%,有时成功,有时超时。
我想问的是用SQL管理器执行跟.net程序里有什么不同,差距如此之大?
Public Class DBTest
Private SQL_TEST As String = "SELECT ID,City,County,CompanyName,Contact,Mobile,Telephone,LogNum,OrderNum,OrderAmount FROM " & _
"( " & _
"SELECT tb_user.ID,tb_user.UserTypeID,tb_AreaCity.City,tb_AreaCounty.County,CompanyName,Contact,Mobile,Telephone,ISNULL(b.LogNum,0) as LogNum,ISNULL(a.OrderNum,0) as OrderNum,ISNULL(a.OrderAmount,0) as OrderAmount from tb_user " & _
"INNER JOIN tb_AreaCounty ON CountyID=tb_AreaCounty.ID " & _
"INNER JOIN tb_AreaCity ON CityID=tb_AreaCity.ID " & _
"LEFT JOIN (select UserID,Count(1) as OrderNum,Sum(Money) as OrderAmount from tb_OrderSale where OrderTime>@DateStart AND OrderTime<@DateEnd AND Status>=10 Group By UserID) a on tb_User.ID=a.UserID " & _
"LEFT JOIN (select UserID,Count(1) as LogNum from tb_UserLog where LogTime>@DateStart AND LogTime<@DateEnd Group By UserID) b on tb_User.ID=b.UserID " & _
") c " & _
"WHERE UserTypeID=1 and OrderNum>0 " & _
"order by OrderNum DESC"
Public Function GetTest(ByVal DateStart As DateTime, ByVal DateEnd As DateTime) As DataTable
Dim dbc As New DBcommon
Dim MyCommand As New SqlCommand
Dim MyAdapter As SqlDataAdapter
Dim dt As New DataTable
Dim strSQL As String strSQL = SQL_TEST MyCommand = dbc.GetSqlCommand()
MyCommand.CommandText = strSQL MyCommand.Parameters.Add(New SqlParameter("@DateStart", SqlDbType.DateTime))
MyCommand.Parameters.Add(New SqlParameter("@DateEnd", SqlDbType.DateTime)) MyCommand.Parameters("@DateStart").Value = DateStart
MyCommand.Parameters("@DateEnd").Value = DateEnd MyAdapter = New SqlDataAdapter(MyCommand)
MyAdapter.Fill(dt)
MyAdapter.Dispose()
MyCommand.Connection.Close()
MyCommand.Dispose()
Return dt
End Function
End Class
Imports System.Data
Imports MyApp.DAL
Partial Class _Default
Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim dbt As New DBTest
Dim dt As New DataTable
dt = dbt.GetTest(Now.AddDays(-30), Now) End Sub
End Class