ios - SQLite运行 'some'查询，在iOS9下，查看比iOS8慢很多

Question

刚刚开始使用 Xcode 版本 7.0 (7A220) 全面测试我们的应用程序。它大量使用 SQLite，大量查询、插入、更新和 View 的使用等。它是一个产品定价工具，有很多行迭代，因此它可以提取报价及其各种规则和依赖项。当被问到时，它确实做了很多。 Xcode 6下，支持iOS8，性能还行。没问题，立竿见影。当运行同一个应用程序时，实际上是为 iOS8 编译的，并且已经在 iOS9 之前的 iPad 上安装，它运行起来就像蜗牛一样。

所以肯定是 iOS9。这是唯一不同的地方。所以，回到 Xcode 7，我开始调试。在代码的哪个位置，它是坚持还是性能受到影响。我已经隔离了查询的粘滞和缓慢。这是一个 View ，一个非常复杂的 View ，但是，它在 iOS8 上运行得非常快。它相当大，如果有人问我会发布它，但我的主要问题是……关于 iOS9 和 SQLite 性能，有什么我需要注意的吗？实际上，我对问题出在哪里感到困惑。我尝试删除:

sqlite3.dylib

并将其替换为似乎随 Xcode 7 一起提供的库:

libsqlite3.0.tbd

然后

libsqlite3.tbd

但都没有帮助。

仍然运行得很慢。

有人有什么想法吗？

Answer 1

好的 - 谢谢，以上建议。我查看了 Instruments Trace，但这并不是真正必要的，因为调试告诉我运行速度较慢的确切查询。

但我的问题是为什么从 iOS8 迁移到 iOS9 会导致查询优化器以不同方式评估 SAME 查询。确实如此。为了完整起见，我自己回答了这个问题，因为我已经解决了这个问题。但是，只是因为我不得不稍微重新处理 SQL 以适应优化器现在正在做的事情。

有趣的是，使用中的同一个 SQLite 库并没有改变。所以，我仍然不知道为什么迁移到 iOS9 会给我带来这个问题。下面，查询。我不指望任何人都能理解它，只要说语法是可以理解的就够了。但是，我更改查询的方法是从其中一个嵌套选择中取出一个 SELECT(或替换)。

这几乎就像 SQLite 喜欢 iOS9 下嵌套查询的深度一样。当然这是没有意义的。但这是我观察到的。

NSString *view = @" CREATE VIEW IF NOT EXISTS OFF_VIEW_5_FIXED_AMT_OFF AS "
        " SELECT OTV_OOL_OOH_SEQ_NO          AS R_OOL_OOH_SEQ_NO, "
        "        OTV_OOL_SEQ_NO              AS R_OOL_SEQ_NO, "
        "        OTV_OO_SEQ_NO               AS R_OO_SEQ_NO, "
        "        AMOUNT_DUE                  AS R_OOL_TOTAL_SAVINGS, "
        "        TOTAL_PAYABLE_WITHOUT_OFFER AS R_OOL_TOTAL_PAYABLE_NO_OFFER, "
        "        CASE "
        "          WHEN (100.00 / TOTAL_PAYABLE_WITHOUT_OFFER * AMOUNT_DUE) > 100.00 THEN "
        "            100.00 "
        "          ELSE "
        "           (100.00 / TOTAL_PAYABLE_WITHOUT_OFFER * AMOUNT_DUE) "
        "        END                         AS R_OOL_PICKED_PERC_OFF_ALL_ORD, "
        "        CASE "
        "          WHEN SUM(OFFER_PAYABLE_BY_LINE) < 0 THEN "
        "            0.00 "
        "          ELSE "
        "            SUM(OFFER_PAYABLE_BY_LINE) "
        "        END                         AS R_OFFER_TOTAL_PAYABLE_BY_LINE "
        "   FROM (SELECT OTV_OOL_SEQ_NO, "
        "                OTV_OO_SEQ_NO, "
        "                OTV_OOL_OOH_SEQ_NO, "
        "               (CASE "
        "                 WHEN OO_ORD_VAL_BASED_ON_PERC_YN = 'Y' THEN "
        "                   TOTAL_PAYABLE_WITHOUT_OFFER - (TOTAL_PAYABLE_WITHOUT_OFFER / 100.00 * ORDER_TOTAL_TRIGGER) "
        "                 ELSE "
        "                   CASE "
        "                     WHEN OO_DISCOUNT_ENTIRE_ORDER_YN = 'Y' THEN "
        "                       ORDER_TOTAL_TRIGGER " // -- * -1 -- When actioned, this needs to be taken off the order, perhaps defined as a negative amount.
        "                     ELSE "
        "                       TOTAL_PAYABLE_WITHOUT_OFFER - ORDER_TOTAL_TRIGGER "
        "                   END "
        "                END) * (100.00 / TOTAL_PAYABLE_WITHOUT_OFFER * OTV_OOL_UNIT_PRICE) / 100.00 AS OFFER_PAYABLE_BY_LINE, "
        "                TOTAL_PAYABLE_WITHOUT_OFFER, "
        "                ORDER_TOTAL_TRIGGER AS AMOUNT_DUE "

        "           FROM (SELECT OTV_OOL_SEQ_NO, "
        "                        OTV_OO_SEQ_NO, "
        "                        OTV_OO_OFFER_ID, "
        "                        OTV_OOL_OOH_SEQ_NO, "
        "                        OTV_OOL_UNIT_PRICE, "
        "                        TOTAL_PAYABLE_WITHOUT_OFFER, "
        "                        OO_ORD_VAL_BASED_ON_PERC_YN, "
        "                        OO_DISCOUNT_ENTIRE_ORDER_YN, "
        "                        OTV_TRIG_VAL_ON_ENTIRE_ORD_YN, "
        "                        TOTAL_SALES_VALUE_ALL_ORDER, "

        "                       (SELECT OAOBOV_AMOUNT_OFF "
        "                          FROM OFF_AMOUNT_OFF_BY_ORDER_VALUE "
        "                         WHERE OAOBOV_OO_SEQ_NO = OTV_OO_SEQ_NO "
        "                           AND OAOBOV_TRIGGER_VALUE = "

        "                            (SELECT MAX(OAOBOV_TRIGGER_VALUE) "
        "                               FROM OFF_AMOUNT_OFF_BY_ORDER_VALUE "
        "                              WHERE OAOBOV_TRIGGER_VALUE <= CASE "
        "                                      WHEN OTV_TRIG_VAL_ON_ENTIRE_ORD_YN = 'Y' THEN "
        "                                        TOTAL_SALES_VALUE_ALL_ORDER "
        "                                      ELSE "
        "                                        TOTAL_PAYABLE_WITHOUT_OFFER "
        "                                      END "
        "                                AND OAOBOV_OO_SEQ_NO = OTV_OO_SEQ_NO)) AS ORDER_TOTAL_TRIGGER "

        "                    FROM (SELECT OTV_OOL_SEQ_NO, "
        "                                 OTV_OO_OFFER_ID, "
        "                                 OTV_OO_SEQ_NO, "
        "                                 OTV_OOL_OOH_SEQ_NO, "
        "                                 OTV_OOL_UNIT_PRICE, "
        "                                 OO_ORD_VAL_BASED_ON_PERC_YN, "
        "                                 OO_DISCOUNT_ENTIRE_ORDER_YN, "
        "                                 OTV_TRIG_VAL_ON_ENTIRE_ORD_YN, "
        "                                (SELECT SUM(OTV_OOL_UNIT_PRICE) FROM OFF_TEMP_5) AS TOTAL_PAYABLE_WITHOUT_OFFER, "
        "                                 CASE "
        "                                   WHEN OTV_USE_ORD_VAL_AFTER_DISC_YN = 'Y' THEN "
        /*
         "                                     (SELECT SUM(OOL_PICKED_PRICE) "
         "                                        FROM OFF_ORDER_LINES "
         "                                       WHERE OOL_OOH_SEQ_NO = OTV_OOL_OOH_SEQ_NO "
         "                                         AND OOL_SEQ_NO NOT IN "
         "                                           (SELECT OOL_OOL_SEQ_NO "
         "                                              FROM OFF_CHOICE_LOCKED_CORES, "
         "                                                   OFF_ORDER_LINES "
         "                                             WHERE OCLC_OOH_SEQ_NO = OTV_OOL_OOH_SEQ_NO "
         "                                               AND OCLC_OOL_SEQ_NO = OOL_SEQ_NO "
         "                                               AND OOL_DISCOUNT_ENTIRE_ORDER_YN = 'N')) "
        */
        "                                     (SELECT OTAOV_AFTER_OFFER_ORDER_VALUE FROM OFF_TEMP_AFTER_OFFER_VALUE) "

        "                                   ELSE "
        "                                     (SELECT SUM(OOL_UNIT_PRICE * OOL_QTY) "
        "                                        FROM OFF_ORDER_LINES OOL "
        "                                       WHERE OOL_OOH_SEQ_NO = OTV_OOL_OOH_SEQ_NO "
        "                                         AND OOL.OOL_LINE_TYPE_ID = 'S') "
        "                                 END                                             AS TOTAL_SALES_VALUE_ALL_ORDER "
        "                           FROM OFF_TEMP_5)) "
        "                   WHERE ORDER_TOTAL_TRIGGER IS NOT NULL) "

        "        GROUP BY OTV_OOL_OOH_SEQ_NO, "
        "                 OTV_OOL_SEQ_NO, "
        "                 AMOUNT_DUE, "
        "                 TOTAL_PAYABLE_WITHOUT_OFFER, "
        "                 OTV_OO_SEQ_NO ";

因此，/* 和 */之间的灰色部分是我取出的部分。

还有这个……

(SELECT OTAOV_AFTER_OFFER_ORDER_VALUE FROM OFF_TEMP_AFTER_OFFER_VALUE)

... 是我用它替换的。在运行 View 之前，我将一行插入到上表中，一行一列，然后从中进行选择。所以从本质上讲，我不再需要在引号中运行查询。

所以，我想在所有这一切结束时，对于遇到类似问题的任何人，我的问题都归结为查询复杂性。但我仍然不明白为什么它在 iOS8、Xcode 6.x 上完全没问题。

也许它会对其他人有所帮助。