使用SQL搜索DB2进行分页的最快/最有效的方式 [英] Fastest most/efficient way to do pagination with SQL searching DB2

问题描述

现在我做了两个单独的SQL语句，一个执行 SELECT COUNT（*）基本上与搜索语句相同的标准。我不是最好的做这些陈述，有时候有点慢，我想知道是否有更好的方式来做我做的事情。在PHP中可能只做一个SQL语句和一些更多的工作？这里是一个例子搜索包含我有的语句。

在第二个语句中，您将看到X之间的X，这部分由第一个行计数语句。

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％'
或LINE LIKE'％FOO％'
或UPPER（MFGNAME）LIKE'％FOO％'
 OR UPPER（ITEM ）LIKE'％FOO％'
 OR UPPER（PRODNAME）LIKE'％FOO％'
 OR UPPER（IDESC1 || IDESC2）比如'％FOO％'
或者上一个（IMFGNO）喜欢'％FOO％'
或者上一个（IITEM）喜欢'％FOO％'）
  pre> 
 
 
 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％'
或LINE LIKE'％FOO％'
或上一级（MFGNAME）LIKE'％FOO％'
或上一级（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和25 
  
 
 
解决方案
如果您试图显示结果总数与分页数（因此38中的0到25） ，单独的声明可能是你最好的选择。我已经尝试了许多事情来获得每个行的计数，但是性能（甚至在一个中等的测试数据库）是可怕的。
 
 
 
你可能应该怎么做要做的是创建一个可以查询的视图，其中包含所有选择条件，然后将其包含在必要的行为中：
 
计数：
  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 
  
当然需要添加相对的条件，但这是事物视图的类型意图处理。
 
 
 
如果您不需要知道前面的行数的时间，您可以简单地将最终排名设置为开始等级加上一些偏移量。然后，当您使用PHP显示结果时，只需编辑结束显示值。
 
 
 
一些随机注释：
 1）是否有理由 line 不是 upper（） d？
 
 2）此查询的性能将受到影响几乎无论你做什么，只是因为所有的字符串操作/比较。是否有可能消除或忽略一些条件？除非在各种字符串列中使用的指示符已应用于它们（某些更新版本的DB2允许将某些标量函数应用于索引键），否则大多数指标是完全没有用（没有帮助，你找到％ANYTHING％毕竟）。
 
 
 < hr /> 
 
 
好的，有一个棘手的方式来做这样的事情，似乎得到好的表现... 
尝试这样的东西（定义的视图首先真的有帮助）：
  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 
  
当然，您仍然必须拥有其中子句在子查询中定义... 
 
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.

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 Row Count: 
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 Search:  
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

解决方案
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
Ranked rows:
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY item) as RANK
      FROM view) as TEMP
WHERE RANK BETWEEN 0 AND 25
You will of course need to add the relative where conditions, but this is the type of thing views are meant to handle.

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.

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
Of course, you'll still have to have your where clause defined in the subselect too...

                        
这篇关于使用SQL搜索DB2进行分页的最快/最有效的方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！