奇怪的问题
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程序里有什么不同，差距如此之大？ --------------------编程问答-------------------- SQL要查每一个用户的登录数，订单个数和订单金额。
感觉写的还行，避免了为每个客户执行子查询 --------------------编程问答-------------------- 网站发布的服务器和数据库服务器是同一台机器么
考虑网络传输 --------------------编程问答-------------------- 不是网络的问题，在服务器端执行一个没有输出的查询也是要十几秒钟。 --------------------编程问答--------------------
--------------------编程问答--------------------
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
补充：.NET技术 , ASP.NET
