使用 SQL 搜索 DB2 进行分页的最快最有效方法

2022-01-04 00:00:00 sql db2 php pagination

现在我执行两个单独的 SQL 语句，一个执行 SELECT COUNT(*) 与搜索语句基本相同的条件.我不是最擅长做这些陈述的，有时会有点慢，我想知道是否有更好的方法来做我所做的事情.可能只做一个 SQL 语句，并在 PHP 中做更多的工作?这是我有声明的搜索包含"示例.

Right now I do two separate SQL statements, one doing a SELECT COUNT(*) on basically the same criteria as the search statement. I am not the best at making these statements and sometimes are a little slow and I would like to know if there is a better way to be doing what I do. Possibly doing only one SQL statement and some more work in PHP? Here is an example "search contains" I have the statements for.

在第二个语句中，您将看到 Y 之间的 X，它部分由第一个行计数语句的结果计算得出.

On the second statement you will see the X between Y, that's partially calculated by the result from the first row count statement.

SQL 行数:

SELECT COUNT(*) 
FROM itemmast 
LEFT OUTER JOIN itemweb 
ON iline = line 
AND iitem = item 
JOIN linemst 
ON iline = lline 
LEFT OUTER JOIN custord 
ON opline = iline 
AND opitem = iitem 
AND opcust = '12345' 
LEFT OUTER JOIN ordwdtl 
ON owline = iline 
AND owitem = iitem 
AND owusr ='user' 
AND owcust ='12345' 
WHERE ico = 01 
AND iecomm = 'Y'  
AND (UPPER(ITEMDESC) || UPPER(PRODDESC)) LIKE '%FOO%' 
     OR LINE LIKE '%FOO%' 
     OR UPPER(MFGNAME) LIKE '%FOO%' 
     OR UPPER(ITEM) LIKE '%FOO%' 
     OR UPPER(PRODNAME) LIKE '%FOO%' 
     OR UPPER(IDESC1 || IDESC2) LIKE '%FOO%' 
     OR UPPER(IMFGNO) LIKE '%FOO%' 
     OR UPPER(IITEM) LIKE '%FOO%')

SQL 搜索:

SELECT * 
FROM (SELECT iline AS line, iitem AS item, rownumber() OVER (ORDER BY item) AS ROW_NUM 
      FROM itemmast 
      LEFT OUTER JOIN itemweb 
      ON iline = line 
      AND iitem = item 
      JOIN linemst 
      ON iline = lline 
      LEFT OUTER JOIN custord 
      ON opline = iline 
      AND opitem = iitem 
      AND opcust = '12345' 
      LEFT OUTER JOIN ordwdtl 
      ON owline = iline 
      AND owitem = iitem 
      AND owusr = 'user' 
      AND owcust = '12345' 
      WHERE ico = 01 
      AND iecomm = 'Y' 
      AND (UPPER(ITEMDESC) || UPPER(PRODDESC)) LIKE '%FOO%' 
           OR LINE LIKE '%FOO%' 
           OR UPPER(MFGNAME) LIKE '%FOO%' 
           OR UPPER(ITEM) LIKE '%FOO%' 
           OR UPPER(PRODNAME) LIKE '%FOO%' 
           OR UPPER(IDESC1 || IDESC2) LIKE '%FOO%' 
           OR UPPER(IMFGNO) LIKE '%FOO%' 
           OR UPPER(IITEM) LIKE '%FOO%')) 
      AS TEMP 
WHERE ROW_NUM BETWEEN 0 AND 25

推荐答案

如果您尝试在分页计数旁边显示结果的总数(即 38 个中的 0 到 25 个)，则可能需要单独的语句你最好的选择.我尝试了很多方法来获取单个行旁边的计数，但性能(即使在中等测试数据库上)也很糟糕.

If you're trying to display a total count of the results alongside the paginated counts (so '0 to 25 out of 38), a separate statement may be your best bet. I've tried a number of things to get the counts alongside the individual rows, but the performance (even over a moderate test database) is terrible.

您可能应该做的是创建一个您可以查询的视图，其中包含您的所有选择标准，然后用必要的行为包装它:
计数:

What you probably ought to do is create a view you can query against, which contains all your selection criteria, then just wrap it with the necessary behaviour:
Count:

SELECT COUNT(*)
FROM view

排名行:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY item) as RANK
      FROM view) as TEMP
WHERE RANK BETWEEN 0 AND 25

您当然需要添加相对的 where 条件，但这是视图要处理的类型.

You will of course need to add the relative where conditions, but this is the type of thing views are meant to handle.

如果您实际上不需要提前知道总行数，您可以简单地将结束排名设置为开始排名加上一些偏移量.然后，当您使用 PHP 显示结果时，只需编辑结束显示值即可.

If you don't actually need to know the total rows ahead of time, you can simply set the end-rank as the start-rank plus some offset. Then, when you display your results with PHP, simply edit the ending display value.

一些随机笔记:1) 是否有理由 line 不是 upper()d?
2) 无论你做什么，这个查询的性能几乎都会受到影响，仅仅因为所有的字符串操作/比较.是否可以消除或忽略某些条件?除非在各种字符串列上使用的索引已将 upper 应用于它们(DB2 的某些更高版本允许将某些标量函数应用于索引键)，否则大多数索引将完全无用(毕竟，您正在寻找 %ANYTHING% 并没有帮助).

Some random notes: 1) Is there are reason that line isn't upper()d?
2) The performance of this query is going to suffer almost no matter what you do, simply because of all the string manipulation/comparisons. Is it possible to eliminate or ignore some of the conditions? Unless the indicies used over the various string columns have had upper applied to them (some later versions of DB2 allow certain scalar functions to be applied to the index key), most indicies are going to be completely useless (it doesn't help that you're looking for %ANYTHING% after all).

好的，有一种棘手"的方法来做这样的事情，而且似乎表现还不错......尝试这样的事情(首先定义的视图会很有帮助):

Okay, there is a 'tricky' way to do something like this, and seems to get okay performance... Try something like this (a view defined first will really help): 

SELECT TEMP.*, CASE WHEN RANK = 0 THEN (SELECT COUNT(*)
                                        FROM view)
                    ELSE 0 END
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY item) as RANK
      FROM view) as TEMP
WHERE RANK BETWEEN 0 AND 25

当然，您仍然必须在子选择中定义 where 子句...

Of course, you'll still have to have your where clause defined in the subselect too...

相关文章