读书人
栏目▼

如何优化这样的SQL让执行更快

发布时间： 2012-12-26 14:39:28 作者： rapoo

怎么优化这样的SQL让执行更快？

 
        Custom_Money = curArrearage
End Select

End Function

Private Sub RefillList(ByVal lv As ListView, ByVal strType As String)
    Dim clm As ColumnHeader
    Dim itm As ListItem
    Dim i As Long, j As Long
    
    Dim nSecondBuy As String
    Dim nArr As String
    
    lv.ListItems.Clear
    
    lv.View = lvwReport
    lv.LabelEdit = lvwManual

    Call objConn
    Dim rsCustom As New ADODB.Recordset
    Dim strListSql As String
    
    rsCustom.CursorLocation = adUseClient
    If strType = "Search" Then
        strListSql = "SELECT * FROM Custom WHERE SubCorp='" & CurrentSubCorp & "' AND (CustomNO Like '%" & SafeSQL(Trim(txtSearch(0).Text)) & "%') AND (M_Name Like '%" & SafeSQL(Trim(txtSearch(1).Text)) & "%') AND (F_Name Like '%" & SafeSQL(Trim(txtSearch(2).Text)) & "%') AND (CardNO Like '%" & SafeSQL(Trim(txtSearch(3))) & "%') ORDER BY ID DESC"
    Else
        strListSql = "SELECT * FROM Custom WHERE SubCorp='" & CurrentSubCorp & "' ORDER BY ID DESC"
    End If
    rsCustom.Open strListSql, conn, 1, 1
    If rsCustom.EOF = True And rsCustom.BOF = True Then Exit Sub
    rsCustom.MoveLast
    nItems = rsCustom.RecordCount
    
    If nItems > 30 Then PanelReadDatabase.Visible = True: MDIMain.MousePointer = 11: DoEvents
    
    rsCustom.MoveFirst
    
    For i = 1 To nItems
        
        nSecondBuy = Custom_Money(rsCustom.Fields("CustomNO"), "二次消费")
        nArr = Custom_Money(rsCustom.Fields("CustomNO"), "欠款")
        
        nCustomNO$ = "" & rsCustom.Fields("CustomNO")
        nSeriesType$ = "" & rsCustom.Fields("SeriesType")
        nCustomName = "" & rsCustom.Fields("M_Name") & " " & rsCustom.Fields("F_Name")
        nCustomMobile = "" & rsCustom.Fields("M_Mobile") & " " & rsCustom.Fields("F_Mobile")
        nCustomWork = "" & rsCustom.Fields("M_Work") 
 
        nSeries = "" & rsCustom.Fields("Series")
        nSeriesPrice = "" & rsCustom.Fields("Receivable")
        
        
        nCardNO = "" & rsCustom.Fields("CardNO")
        nReturnMoney = "" & rsCustom.Fields("InReturnMoney")
        
        Set itm = lv.ListItems.Add(, "Row" & i, rsCustom.Fields(0))
        itm.SubItems(1) = Trim(nCustomNO)
        itm.SubItems(2) = Trim(nSeriesType)
        itm.SubItems(3) = Trim(nCustomName)
        itm.SubItems(4) = Trim(nCustomMobile)
        itm.SubItems(5) = Trim(nCustomWork)
        itm.SubItems(6) = Trim(nSeries)
        itm.SubItems(7) = Trim(nSeriesPrice)
        itm.SubItems(8) = nSecondBuy
        itm.SubItems(9) = nArr
        itm.SubItems(10) = Trim(nCardNO)
        itm.SubItems(11) = nReturnMoney
        
        rsCustom.MoveNext
    Next i
    
    PanelReadDatabase.Visible = False
    MDIMain.MousePointer = 0
    rsCustom.Close
    Set rsCustom = Nothing
    
    TabSeries.Tabs(1).Caption = "共搜索相关顾客资料 " & lstCustom.ListItems.count & " 个"
    'Set conn = Nothing
End Sub

Private Sub Form_Load()

'列取顾客资料

Call RefillList(lstCustom, "")

End Sub



这样读取出数据特别慢。请问大师怎么样优化？
[最优解释]
引用:
顶上来。解决问题发分啦。
顶一下，这个我不太会呢，建议你发到sql基础版块，找发愤哥给你看看
[其他解释]
在开始添加数据之前, 先把lv设置为不可见, 添加完毕之后再设为可见
[其他解释]
那样程序就会出现假死状态，我觉得应该还是我的SQL语句有问题。
[其他解释]
觉得这三个SQL语句可以合起来写成一句与执行
[其他解释]
Leftie老大，能费费心帮我看看怎么合怎么改吧。我是新手，正好可以学习一下，类似这样的语句怎么样正确的去写。谢谢
[其他解释]
为什么不一个sql、
写出结构 提供表结构 给你写个
[其他解释]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Custom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
 
drop table [dbo].[Custom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Money]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Money]
GO

CREATE TABLE [dbo].[Custom] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[CustomNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SubCorp] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SeriesType] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SeriesGroup] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Series] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Amount] [int] NULL ,
[SeriesPrice] [money] NULL ,
[TotalPrice] [money] NULL ,
[M_Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Birthday] [datetime] NULL ,
[M_BirthdayCN] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Phone] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Mobile] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Zip] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Work] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Birthday] [datetime] NULL ,
[F_BirthdayCN] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Phone] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Mobile] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Zip] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Work] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderDate] [datetime] NULL ,
[PhotoDate] [datetime] NULL ,
[IsPhotoExt] [bit] NULL ,
[PhotoExtPlace] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoExtDate] [datetime] NULL ,
[SelectDate] [datetime] NULL ,
[DigitalSelectDate] [datetime] NULL ,
[TakeOrderDate] [datetime] NULL ,
[TakeDate] [datetime] NULL ,
[MarryDate] [datetime] NULL ,
[MarryDateCN] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Exigence] [bit] NULL ,
[VIP] [bit] NULL ,
[Abate] [money] NULL ,
[Receivable] [money] NULL ,
[OrderPay] [money] NULL ,
[SelectAdd] [money] NULL ,
[Arrearage] [money] NULL ,
[Source] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CardNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Zone] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[InNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[InMale] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[InFemale] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[InReturnMoney] [int] NULL , 
 
[ReturnMoney] [int] NULL ,
[CustomType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Sales] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Dress] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[DressExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Prink] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkMale] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkMaleExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkAssistant] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkAssistantExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Photo] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoProcExt] [int] NULL ,
[PhotoAssistant] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoAssistantExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Selected] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[DigitalSelected] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[SelectProcExt] [int] NULL ,
[Design] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[DigitalDesign] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Take] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoEnd] [int] NULL ,
[PhotoExtEnd] [int] NULL ,
[SelectEnd] [int] NULL ,
[DesignEnd] [int] NULL ,
[DigitaSelectEnd] [int] NULL ,
[TakeEnd] [int] NULL ,
[Operator] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Remark] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Photo_Supply] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Design_First_Accept] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Design_First_Accept_Date] [datetime] NULL ,
[P_Design_First_Date] [datetime] NULL ,
[P_Photo_Amount] [int] NULL ,
[P_Design_First_EndDate] [datetime] NULL ,
[P_Select_Details] [nvarchar] (3000) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Select_Amount] [int] NULL ,
[P_Select_Remark] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[P_DigitalSelect_Remark] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[ProcessEnd] [bit] NULL ,
[Custom_Type_Flag] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Money] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Money] [money] NOT NULL ,
[Shroff] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sales] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Assistant] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Date] [datetime] NOT NULL , 
 
[Type] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PayType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CardNo] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RobeID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Operator] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO



这是这两上表的脚本。
[其他解释]
顶一下。沉下去了。
[其他解释]
请问你refillist里面那句sql取了多少条记录出来？

要想快就给table加index就好了


如果你觉得用户体验的问题呢，就加progressbar


[其他解释]
like查询速度是很慢的，你又用了那么多的like
like是不使用索引的，要傻傻的一个一个的检查数据是否包含特征串
[其他解释]
引用:
请问你refillist里面那句sql取了多少条记录出来？

要想快就给table加index就好了


如果你觉得用户体验的问题呢，就加progressbar


一共才5000多条就得花将近10分钟。
[其他解释]
关键问题是出现在
nAllBuy = Custom_Money(rsCustom.Fields("CustomNO"), "总消费")
nArr = Custom_Money(rsCustom.Fields("CustomNO"), "欠款")
如果没有这两个的话，5000条的速度就是不到4秒，要是加上这个的话那么就是不到10分钟。
[其他解释]
把所有的查询语句合成为一条,直接查出所有顾客的统计数据,然后对记录集进行循环处理.
没有像你这样,把几个查询放到一个函数里,再用一个循环去调用这个函数,想把数据库搞死啊!
[其他解释]
引用:
把所有的查询语句合成为一条,直接查出所有顾客的统计数据,然后对记录集进行循环处理.
没有像你这样,把几个查询放到一个函数里,再用一个循环去调用这个函数,想把数据库搞死啊!

老大，关键是我不会整合一条。能给个整合的语句学习一下吗？
[其他解释]
不知道你一个表的结构,也不太清楚表的关系是什么,不过,大致是这样吧:

select a.customNo,a.receivable-sum(b.Price),sum(c.Money)
from custom a inner join customdetails b on a.custoNO=b.customSN
inner join money c on a.customNO=c.customNO
where 条件
group by a.customNO,a.Receivable
查询获得记录集后,循环处理各客户的应收款,已收款,欠款.
[其他解释]
现在的问题，关键是在Custom_Money函数中有计算总消费和总付款。这个在SQL里怎么实现呢。
[其他解释]
引用:
不知道你一个表的结构,也不太清楚表的关系是什么,不过,大致是这样吧:

select a.customNo,a.receivable-sum(b.Price), --总消费
sum(c.Money) --总付款
from custom a inner join customdetails b on a.custoNO=b.customSN
inner join money c on a.customNO=c.cu……

不是在上面么.
[其他解释]
你的表加索引了没有啊，没有就加上
[其他解释]
出来的结果不对。如果只有
SELECT SUM(c.Money) AS Expr1
FROM Custom a INNER JOIN
Money c ON a.CustomNO = c.CustomNO
WHERE (a.CustomNO LIKE '%07125%')


GROUP BY a.CustomNO
的话，出来的结果是对的。但是加上sum(b.Price), 就不对了。
[其他解释]

引用:
不知道你一个表的结构,也不太清楚表的关系是什么,不过,大致是这样吧:

select a.customNo,a.receivable-sum(b.Price),sum(c.Money)
from custom a inner join customdetails b on a.custoNO=b.customSN
inner join money c on a.……


晴天老大。我现在测试用

SELECT Custom.CustomNO, Custom.Receivable AS Expr1, CustomDetails.Price AS Expr3, 
      SUM(Money.Money) AS Expr2
FROM Custom INNER JOIN
      CustomDetails ON Custom.CustomNO = CustomDetails.CustomSN INNER JOIN
      Money ON Custom.CustomNO = Money.CustomNO
WHERE (Custom.CustomNO = '0008950') AND (CustomDetails.Class = 'Retail')
GROUP BY Custom.CustomNO, Custom.Receivable, CustomDetails.Price


结果是：

CustomNO Expr1 Expr3 Expr2
0008905 2980 180 3735
0008905 2980 200 3735
0008905 2980 375 3735
SUM(Money.Money)结果对。应该CustomDetails.Price 也用SUM计算一下，但是这样的话出来的结果就不对了。Expr3的结果就错了。如果算出Expr3 的结果相加就对了。然后是一行。
[其他解释]
怎么加索引？加在哪里？
[其他解释]
顶上来。解决问题发分啦。

读书人网 >VB

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net