奇怪的问题

[hornbills]

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程序里有什么不同，差距如此之大？

[hornbills]

SQL要查每一个用户的登录数，订单个数和订单金额。

感觉写的还行，避免了为每个客户执行子查询

[q107770540]

网站发布的服务器和数据库服务器是同一台机器么
考虑网络传输

[hornbills]

不是网络的问题，在服务器端执行一个没有输出的查询也是要十几秒钟。

[hornbills]

        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

[hornbills]

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