求优化

Question

 sCmd = "Select A.InventoryID, A.BarCode, A.InventoryName, A.Description, A.StockCode, A.Class, A.Unit, "
    sCmd = sCmd & " A.Price, A.Price2, A.Price3, A.Price4, "
    sCmd = sCmd & " A.NoPriceShift, A.AllowDecimalValue, A.Group1, A.Group2, A.Group3, A.Group4, A.Group5, "
    sCmd = sCmd & " A.Active, B.CategoryNumber, A.PriceShiftID, "
    sCmd = sCmd & " A.Flag1, B.Flag1 As DeptFlag1, A.Flag2, B.Flag2 As DeptFlag2 "
    sCmd = sCmd & " From Inventory A INNER JOIN InventoryCategories B ON A.CategoryID = B.CategoryID "
    sCmd = sCmd & " Where A.Class<>2 And A.Active "
    Set oRs = oCmn.ExecRS(sCmd, DCN)
    Do While Not oRs.EOF
        Set oPL = New dPRODUCTLIST
        oPL.InventoryID = oRs!InventoryID
        oPL.BarCode = Trim(oRs!BarCode)
        oPL.name = IIf(IsNull(oRs!InventoryName), "", Trim(oRs!InventoryName))
        oPL.Description = IIf(IsNull(oRs!Description), "", Trim(oRs!Description))
        oPL.StockCode = IIf(IsNull(oRs!StockCode), "", Trim(oRs!StockCode))
        oPL.Class = IIf(IsNull(oRs!Class), 0, oRs!Class)
        oPL.Unit = IIf(IsNull(oRs!Unit), "", Trim(oRs!Unit))
        oPL.Price = IIf(IsNull(oRs!Price), 0, oRs!Price)
        oPL.Price2 = IIf(IsNull(oRs!Price2), 0, oRs!Price2)
        oPL.Price3 = IIf(IsNull(oRs!Price3), 0, oRs!Price3)
        oPL.Price4 = IIf(IsNull(oRs!Price4), 0, oRs!Price4)
        oPL.NoPriceShift = IIf(IsNull(oRs!NoPriceShift), False, oRs!NoPriceShift)
        oPL.AllowDecimalValue = IIf(IsNull(oRs!AllowDecimalValue), False, oRs!AllowDecimalValue)
        oPL.Group1 = IIf(IsNull(oRs!Group1), "", oRs!Group1)
        oPL.Group2 = IIf(IsNull(oRs!Group2), "", oRs!Group2)
        oPL.Group3 = IIf(IsNull(oRs!Group3), "", oRs!Group3)
        oPL.Group4 = IIf(IsNull(oRs!Group4), "", oRs!Group4)
        oPL.Group5 = IIf(IsNull(oRs!Group5), "", oRs!Group5)
        oPL.CategoryNumber = IIf(IsNull(oRs!CategoryNumber), "", oRs!CategoryNumber)
        oPL.PriceShiftID = IIf(IsNull(oRs!PriceShiftID), 0, oRs!PriceShiftID)
        If Left(oRs!Flag1, 1) = "1" Then
            oPL.TaxFlag = Right(oRs!Flag1, 6)
        Else
            oPL.TaxFlag = Right(oRs!DeptFlag1, 6)
        End If
        If Left(oRs!Flag2, 1) = "1" Then
            oPL.PrinterFlag = Right(oRs!Flag2, 7)
        Else
            oPL.PrinterFlag = Right(oRs!DeptFlag2, 7)
        End If
        dPRODCOLL.Add CStr(UCase(Trim(oPL.BarCode))), oPL
        oRs.MoveNext    
    Loop
mysql> explain inventory;
+-------------------+---------------+------+-----+----------+-------+
| Field             | Type          | Null | Key | Default  | Extra |
+-------------------+---------------+------+-----+----------+-------+
| InventoryID       | int(11)       | NO   | PRI | 0        |       |
| BarCode           | varchar(16)   | NO   | UNI | NULL     |       |
| InventoryName     | varchar(16)   | NO   |     | NULL     |       |
| Description       | varchar(50)   | YES  |     | NULL     |       |
| CategoryID        | int(11)       | NO   | MUL | 0        |       |
| Class             | int(11)       | NO   | MUL | 0        |       |
| Unit              | varchar(10)   | NO   |     | PCS      |       |
| Price             | decimal(19,4) | YES  |     | 0.0000   |       |
| Price2            | decimal(19,4) | YES  |     | 0.0000   |       |
| Price3            | decimal(19,4) | YES  |     | 0.0000   |       |
| Price4            | decimal(19,4) | YES  |     | 0.0000   |       |
| Onhand            | double        | YES  |     | 0        |       |
| ReorderLevel      | double        | YES  |     | 0        |       |
| MaxiumLevel       | double        | YES  |     | 0        |       |
| SafetyLevel       | double        | YES  |     | 0        |       |
| DateCreated       | datetime      | YES  |     | NULL     |       |
| Group1            | varchar(16)   | YES  | MUL | NULL     |       |
| Group2            | varchar(16)   | YES  | MUL | NULL     |       |
| Group3            | varchar(16)   | YES  | MUL | NULL     |       |
| Group4            | varchar(16)   | YES  | MUL | NULL     |       |
| Group5            | varchar(16)   | YES  | MUL | NULL     |       |
| NoPriceShift      | tinyint(4)    | YES  |     | 0        |       |
| Active            | tinyint(4)    | YES  |     | 0        |       |
| Updated           | tinyint(4)    | YES  |     | 0        |       |
| Photograph        | longblob      | YES  |     | NULL     |       |
| Notes             | text          | YES  |     | NULL     |       |
| BranchUpdated     | tinyint(4)    | YES  |     | 0        |       |
| StockCode         | varchar(16)   | YES  |     | NULL     |       |
| Flag1             | varchar(8)    | YES  |     | 00000000 |       |
| Flag2             | varchar(8)    | YES  |     | 00000000 |       |
| PriceShiftId      | int(11)       | NO   |     | 0        |       |
| AllowDecimalValue | tinyint(4)    | YES  |     | 0        |       |
+-------------------+---------------+------+-----+----------+-------+
32 rows in set (0.00 sec)当IVENTORYID>5W的时候，系统需要20-30秒才能进，看看还需要什么优化？

Answer 1

你的慢是指整个VB程序慢？还是指某个SQL查询语句慢？建议楼主先直接在MYSQL中执行一下sCmd 中的SQL语句，看一下速度。如果需要优化，则贴出你的以 下信息以供分析。show create table Inventory ;
show create table InventoryCategories ;
show index from InventoryCategories ;
show index from Inventory ;
explian Select A.InventoryID, A.BarCode, A.InventoryNa.........

Answer 2

mysql> show create table inventory;
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------+
| Table     | Create Table                                                                               |+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------+
| inventory | CREATE TABLE `inventory` (
  `InventoryID` int(11) NOT NULL default '0',
  `BarCode` varchar(16) NOT NULL,
  `InventoryName` varchar(16) NOT NULL,
  `Description` varchar(50) default NULL,
  `CategoryID` int(11) NOT NULL default '0',
  `Class` int(11) NOT NULL default '0',
  `Unit` varchar(10) NOT NULL default 'PCS',
  `Price` decimal(19,4) default '0.0000',
  `Price2` decimal(19,4) default '0.0000',
  `Price3` decimal(19,4) default '0.0000',
  `Price4` decimal(19,4) default '0.0000',
  `Onhand` double default '0',
  `ReorderLevel` double default '0',
  `MaxiumLevel` double default '0',
  `SafetyLevel` double default '0',
  `DateCreated` datetime default NULL,
  `Group1` varchar(16) default NULL,
  `Group2` varchar(16) default NULL,
  `Group3` varchar(16) default NULL,
  `Group4` varchar(16) default NULL,
  `Group5` varchar(16) default NULL,
  `NoPriceShift` tinyint(4) default '0',
  `Active` tinyint(4) default '0',
  `Updated` tinyint(4) default '0',
  `Photograph` longblob,
  `Notes` text,
  `BranchUpdated` tinyint(4) default '0',
  `StockCode` varchar(16) default NULL,
  `Flag1` varchar(8) default '00000000',
  `Flag2` varchar(8) default '00000000',
  `PriceShiftId` int(11) NOT NULL default '0',
  `AllowDecimalValue` tinyint(4) default '0',
  PRIMARY KEY  (`InventoryID`),
  UNIQUE KEY `BarCode` (`BarCode`),
  UNIQUE KEY `InventoryID` (`InventoryID`),
  KEY `Class` (`Class`),
  KEY `CategoryID` (`CategoryID`),
  KEY `Group1` (`Group1`),
  KEY `Group2` (`Group2`),
  KEY `Group3` (`Group3`),
  KEY `Group4` (`Group4`),
  KEY `Group5` (`Group5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------+
1 row in set (0.00 sec)mysql>

Answer 3

mysql> show create table InventoryCategories;
| InventoryCategories | CREATE TABLE `inventorycategories` (
  `CategoryID` int(11) NOT NULL,
  `CategoryNumber` varchar(4) NOT NULL default '0',
  `CategoryName` varchar(16) NOT NULL,
  `Description` varchar(50) default NULL,
  `DateCreated` datetime default NULL,
  `Active` tinyint(4) default '0',
  `Updated` tinyint(4) default '0',
  `BranchUpdated` tinyint(4) default '0',
  `Flag1` varchar(8) default '00000000',
  `Flag2` varchar(8) default '00000000',
  PRIMARY KEY  (`CategoryID`),
  UNIQUE KEY `CategoryID` (`CategoryID`),
  UNIQUE KEY `CategoryNumber` (`CategoryNumber`),
  UNIQUE KEY `CategoryName` (`CategoryName`),
  KEY `Description` (`Description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.06 sec)
mysql> show index from Inventory;
+-----------+------------+-------------+--------------+-------------+-----------
+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name    | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------+--------------+-------------+-----------
+-------------+----------+--------+------+------------+---------+
| Inventory |          0 | PRIMARY     |            1 | InventoryID | A
|         121 |     NULL | NULL   |      | BTREE      |         |
| Inventory |          0 | BarCode     |            1 | BarCode     | A
|         121 |     NULL | NULL   |      | BTREE      |         |
| Inventory |          0 | InventoryID |            1 | InventoryID | A
|         121 |     NULL | NULL   |      | BTREE      |         |
| Inventory |          1 | Class       |            1 | Class       | A
|           2 |     NULL | NULL   |      | BTREE      |         |
| Inventory |          1 | CategoryID  |            1 | CategoryID  | A
|          11 |     NULL | NULL   |      | BTREE      |         |
| Inventory |          1 | Group1      |            1 | Group1      | A
|           1 |     NULL | NULL   | YES  | BTREE      |         |
| Inventory |          1 | Group2      |            1 | Group2      | A
|           1 |     NULL | NULL   | YES  | BTREE      |         |
| Inventory |          1 | Group3      |            1 | Group3      | A
|           1 |     NULL | NULL   | YES  | BTREE      |         |
| Inventory |          1 | Group4      |            1 | Group4      | A
|           1 |     NULL | NULL   | YES  | BTREE      |         |
| Inventory |          1 | Group5      |            1 | Group5      | A
|           1 |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+-------------+--------------+-------------+-----------
+-------------+----------+--------+------+------------+---------+
10 rows in set (0.00 sec)

Answer 4

mysql> show index from InventoryCategories ;
+---------------------+------------+----------------+--------------+------------
----+-----------+-------------+----------+--------+------+------------+---------
+
| Table               | Non_unique | Key_name       | Seq_in_index | Column_name
    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|
+---------------------+------------+----------------+--------------+------------
----+-----------+-------------+----------+--------+------+------------+---------
+
| InventoryCategories |          0 | PRIMARY        |            1 | CategoryID
    | A         |          11 |     NULL | NULL   |      | BTREE      |
|
| InventoryCategories |          0 | CategoryID     |            1 | CategoryID
    | A         |          11 |     NULL | NULL   |      | BTREE      |
|
| InventoryCategories |          0 | CategoryNumber |            1 | CategoryNum
ber | A         |          11 |     NULL | NULL   |      | BTREE      |
|
| InventoryCategories |          0 | CategoryName   |            1 | CategoryNam
e   | A         |          11 |     NULL | NULL   |      | BTREE      |
|
| InventoryCategories |          1 | Description    |            1 | Description
    | A         |          11 |     NULL | NULL   | YES  | BTREE      |
|
+---------------------+------------+----------------+--------------+------------
----+-----------+-------------+----------+--------+------+------------+---------
+
5 rows in set (0.02 sec)mysql> explain Select A.InventoryID, A.BarCode, A.InventoryName, A.Description,
A.StockCode, A.Class, A.Unit,  A.Price, A.Price2, A.Price3, A.Price4,  A.NoPrice
Shift, A.AllowDecimalValue, A.Group1, A.Group2, A.Group3, A.Group4, A.Group5,  A
.Active, B.CategoryNumber, A.PriceShiftID,  A.Flag1, B.Flag1 As DeptFlag1, A.Fla
g2, B.Flag2 As DeptFlag2  From Inventory A INNER JOIN InventoryCategories B ON A
.CategoryID = B.CategoryID  Where A.Class<>2 And A.Active
    -> ;
+----+-------------+-------+------+--------------------+------------+---------+-
----------------+------+-------------+
| id | select_type | table | type | possible_keys      | key        | key_len |
ref             | rows | Extra       |
+----+-------------+-------+------+--------------------+------------+---------+-
----------------+------+-------------+
|  1 | SIMPLE      | B     | ALL  | PRIMARY,CategoryID | NULL       | NULL    |
NULL            |   11 |             |
|  1 | SIMPLE      | A     | ref  | Class,CategoryID   | CategoryID | 4       |
c1.B.CategoryID |   11 | Using where |
+----+-------------+-------+------+--------------------+------------+---------+-
----------------+------+-------------+
2 rows in set (0.00 sec)mysql>mysql>

Answer 5

InventoryCategories这个的表结构呢

Answer 6

总共才100多条记录，你的 Select A.InventoryID, A.BarCode, A.InventoryName, A.Description,
A.StockCode, A.Class, A.Unit,  A.Price, A.Price2, A.Price3, A.Price4,  A.NoPrice
Shift, A.AllowDecimalValue, A.Group1, A.Group2, A.Group3, A.Group4, A.Group5,  A
.Active, B.CategoryNumber, A.PriceShiftID,  A.Flag1, B.Flag1 As DeptFlag1, A.Fla
g2, B.Flag2 As DeptFlag2  From Inventory A INNER JOIN InventoryCategories B ON A
.CategoryID = B.CategoryID  Where A.Class<>2 And A.Active
在MYSQL中直接运行需要多久？应该不超过1秒。直接测试一下，如果MYSQL中速度不慢的话，你需要优化的就不是MYSQL表了，而你的VB代码。

Answer 7

不好意思，狼头，拿错数据了。
这个DATA是数据比较少，运行的快，当数据超过5W的时候就比较慢了。
程序里面是把所有的数据COLLECTION一次，所以就比较慢了。

Answer 8

10W数据在MYSQL里查询是2.37S，其实是很快的，就是在VB中COLLECTION这些数据用的时间多。

Answer 9

就是这段里面有数据在10W的时候，COLLECTION的时候会慢，大概要20-30秒吧。

Answer 10

不要用iif，这个极低效...不如if .... then ... else ....end if，或者如下： 
oPL.name = Trim(oRs!InventoryName) & vbnullstring
最好trim也不要用，这个应该是数据写入时处理的

Answer 11

Set oRs = oCmn.ExecRS(sCmd, DCN)
慢？还是循环慢？（你查询出来数据记录条数很多吗？）

Answer 12

循环慢了，在MYSQL查询出来10W纪录用了2.37S。

Answer 13

没指示，我又不是你的领导。
Do循环内，每一次循环都要Set oPL=New dPRODUCTLIST，对象没有释放。能否把Set oPL = New dPRODUCTLIST放在Do外面，循环结束后，释放对象。放在DO内也是可以，在LOOP前面加上一句Set oPL=Nothing。

Answer 14

oPL是一个对象，它还要加入到一个集合对象中，不能放在循环外，这方面没问题...
要知道,iif函数一定是要测试二个条件的，而if ... then ... end 语句只要满足了条件，就不会测试else以下的语句，所以在10w数据下建议你用if语句....

Answer 15

oPL.name = Trim(oRs!InventoryName) & vbnullstring
这样isnull也省了...不过和if语句比较谁更快，你要测试，因为串接字符串也不快，10w数据不好说...

Answer 16

iif函数一定是要测试二个条件的这句是何解？不懂

Answer 17

'简单测试
Dim a As Long, b As Longa = 15: b = 0If a > 10 Then 
    Debug.Print a 
Else
    Debug.Print a / b
End If
'以上不会执行else语句'以下满足a>10，但还会测试a<=10的情况下的表达式，b=0所以报错
Debug.Print IIf(a > 10, a, a / b)

Answer 18

iif不是主要原因,重点在你为什么要循环5万条数据

Answer 19

刚才测试了一番，发现10W条数据COLLECT了15-16秒，还好，好像问题不是出在这里，用了IF ELSE 好像也提升了不到1秒（也有存在同步误差的可能）。
而是出现是另外一张表CUSTOMERS，只有将近7000条数据，居然COLLECT了25秒，情何以堪呀！！！

Answer 20

iif还真有这个问题，如果输出表达式都换成函数 居然这两个函数都去执行一遍，汗...
谢谢 vbman2003

Answer 21

你从业务逻辑来看看吧什么东东要10万条数据都取出来，然后你都扔到recordset里面了，然后还要给一个collection,其实recordset本身就是一个collection阿。为什么一定要扔到一个collection里面呢。解决思路
1.不要一次数据都取出来，根据需要取
2.取出来后不要扔到collection里面，保留在recordset随用随分析
3.Set oRs = oCmn.ExecRS(sCmd, DCN,1,1)
试试看

Answer 22

知道有差别,因为INVENTORY是主表,CUSTOMERS只是副表,根本没那么多数据,但是COLLECT时间很长.