Copy orm automatically generates paged SQL shares

  • 2020-06-01 10:55:13
  • OfStack

Take a look at the current four database paging writing methods:


-- Oracle
SELECT * FROM ( 
    SELECT ROWNUM RN,  PageTab.* FROM  
                ( 
                SELECT * FROM User_Tables order by id desc 
                ) PageTab  where ROWNUM <= 3010 
            ) Where RN>= 3001 

-- SQLite    
select * from User_Tables order by id desc limit 3001,10
-- SQL2000
SELECT TOP 100 PERCENT  * FROM (
    SELECT TOP 10 * FROM ( 
        SELECT TOP 3010 * from User_Tables  order by id desc ) PageTab order by id ASC  
) PageTab2 order by id desc
-- SQL2005+    
Select PageTab.* from (  
    Select top 3010 ROW_NUMBER() over (order by id desc) RN , * from User_Tables  
) PageTab Where RN >= 3001

There is an explanation for the paging writing method of Oracle and Sql2005+.

Oracle USES ROWNUM faster than Row_Number(). In the example of sql, all the data in the interval [3001,3010] are queried. In the Sql statement, the number of rows in the result set of the query is reduced as much as possible in the subquery, and then conditional filtering is done in the outer query for the sorted row number. For example, the subquery in Oracle has ROWNUM < = 3010,Sql2005 has top 3010 *.

Of course, today's discussion is not about the performance of paging statements, but if you know a better and faster way to write them, you're welcome to talk.

The above paging method is based on the query sql statement:


select * from User_Tables order by id desc

First, I need to analyze the behavior from the Sql statement. I broke the Sql into the n part, and then completed the splicing function above. According to the mold to the inside of the data, not difficult.

The reverse page

Let's describe another scenario. The sql just demonstrated is to query the data with the descending number between [3001,3010] that satisfies the condition. If the total number of rows is only 3500, then the result is that 3010 rows of data need to be queried, and the last 10 rows need to be taken out.

So learn from the previous experience, let's call it reverse paging. Knowing the total number of lines, we can analyze whether reverse paging is needed, because reverse paging takes time to get the paging Sql statement, which is not necessary in all cases. Previously, we assumed that the data only had 3500 rows, and we expected to extract [3001,3010] data sorted by id flashback. In other words, if we followed the ascending order of id, we expected to extract the data in the interval of [491,500], and then sorted by id flashback, which is the data we needed.

Theoretical knowledge is almost finished, need to know more, baidu 1, you know. Here is the code, a bit long, expand carefully:


public enum DBType
    {
        SqlServer2000,
        SqlServer,
        Oracle,
        SQLite
    }
    public class Page
    {
        /// <summary>
        ///  Database category 
        /// </summary>
        public DBType dbType = DBType.Oracle;
        /// <summary>
        ///  The total number of rows in reverse order is greater than MaxRow , will generate reverse paging SQL
        /// </summary>
        public int MaxRow = 1000;// Temporary test, make the value a little bit smaller 
        /// <summary>
        ///  matching SQL In the statement Select field 
        /// </summary>
        private Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        /// <summary>
        ///  matching SQL In the statement Order By field 
        /// </summary>
        private Regex rxOrderBy = new Regex(@"\b(?<ordersql>ORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+)(?:\s+(?<order>ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        /// <summary>
        ///  matching SQL In the statement Distinct
        /// </summary>
        private Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        private string[] SplitSqlForPaging(string sql)
        {
            /* Storage analyzed SQL information   In the order :
             * 0.countsql
             * 1.pageSql( Leave the location for analysis here )
             * 2. removed select the sql
             * 3.order by  field  desc
             * 4.order by  field 
             * 5.desc
             */
            var sqlInfo = new string[6];
            // Extract the columns from "SELECT <whatever> FROM"
            var m = rxColumns.Match(sql);
            if (!m.Success)
                return null;
            // Save column list and replace with COUNT(*)
            Group g = m.Groups[1];
            sqlInfo[2] = sql.Substring(g.Index);
            if (rxDistinct.IsMatch(sqlInfo[2]))
                sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
            else
                sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);

            // Look for an "ORDER BY <whatever>" clause
            m = rxOrderBy.Match(sqlInfo[0]);
            if (!m.Success)
            {
                sqlInfo[3] = null;
            }
            else
            {
                g = m.Groups[0];
                sqlInfo[3] = g.ToString();
                // The statistical SQL  remove order
                sqlInfo[0] = sqlInfo[0].Substring(0, g.Index) + sqlInfo[0].Substring(g.Index + g.Length);
                // Store sort information 
                sqlInfo[4] = m.Groups["ordersql"].Value;//order by xxx
                sqlInfo[5] = m.Groups["order"].Value;//desc 
                //select Part of the   remove order
                sqlInfo[2] = sqlInfo[2].Replace(sqlInfo[3], string.Empty);
            }
            return sqlInfo;
        }

        /// <summary>
        ///  Generate reverse paging Sql statements 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqls"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        public void CreatePageSqlReverse(string sql,ref string[] sqls, int start, int limit, int total = 0)
        {
            // There is no need to reverse page if the total number of lines is small or the number of paged bars is in the first half 
            if (total < 100 || start <= total / 2)
            {
                return;
            }
            //sql The array after regular analysis has 5 Value, if not analyzed, analyzed here 
            if (sqls == null || sqls.Length == 6)
            {
                sqls = SplitSqlForPaging(sql);
                if (sqls == null)
                {
                    // unresolvable SQL statements 
                    throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
                }
            }
            // If you do not define a collation rule, you do not need to do reverse paging calculations 
            if (string.IsNullOrEmpty(sqls[5]))
            {
                return;
            }
            // Reverse paging check 
            string sqlOrder = sqls[3];
            int end = start + limit;
            // Gets the reverse ordered sql
            string sqlOrderChange = string.Compare(sqls[5], "desc", true) == 0 ?
                string.Format("{0} ASC ", sqls[4]) :
                string.Format("{0} DESC ", sqls[4]);
            /* The theory of 
             * total:10000 start:9980 limit:10 
             *  the  end:9990  The paging condition is  RN >= 9980+1 and RN <= 9990
             *  After the reverse order adjustment  
             * start = total - start = 20
             * end = total - end = 10
             *  exchange start and end , the paging condition is  RN >= 10+1 and RN<= 20
             */
            // recalculate start and end
            start = total - start;
            end = total - end;
            // exchange start end
            start = start + end;
            end = start - end;
            start = start - end;
            // Define the paging SQL
            var pageSql = new StringBuilder();
            if (dbType == DBType.SqlServer2000)
            {
                pageSql.AppendFormat("SELECT TOP @PageLimit * FROM ( SELECT TOP @PageEnd {0} {1} ) ", sqls[2], sqlOrderChange);
            }
            else if (dbType == DBType.SqlServer)
            {
                // Group paging SQL statements 
                pageSql.AppendFormat("SELECT PageTab.* FROM ( SELECT TOP @PageEnd ROW_NUMBER() over ({0}) RN , {1}  ) PageTab ",
                    sqlOrderChange,
                    sqls[2]);
                // If the query is not first 1 Page, you need to determine the starting line number 
                if (start > 1)
                {
                    pageSql.Append("Where RN >= :PageStart ");
                }
            }
            else if (dbType == DBType.Oracle)
            {
                pageSql.AppendFormat("SELECT ROWNUM RN,  PageTab.* FROM  ( Select {0} {1} ) PageTab  where ROWNUM <= :PageEnd ", sqls[2], sqlOrderChange);
                // If the query is not first 1 Page, you need to determine the starting line number 
                if (start > 1)
                {
                    pageSql.Insert(0, "SELECT * FROM ( ");
                    pageSql.Append(" ) ");
                    pageSql.Append(" WHERE RN>= :PageStart ");
                }
            }
            else if (dbType == DBType.SQLite)
            {
                pageSql.AppendFormat("SELECT * FROM ( SELECT {0} {1} limit  @PageStart,@PageLimit ) PageTab ", sqls[2], sqlOrderChange);
            }
            // Restore order 
            pageSql.Append(sqlOrder);
            // Store the generated pages SQL statements   
            sqls[1] = pageSql.ToString();
            // The temporary test 
            sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");
            Console.WriteLine(" 【 count 】 {0}", sqls[0]);
            Console.WriteLine(" 【 page 】 {0}", sqls[1]);
            Console.WriteLine();
        }
        /// <summary>
        ///  Generate regular Sql statements 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqls"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <param name="createCount"></param>
        public void CreatePageSql(string sql, out string[] sqls, int start, int limit, bool createCount = false)
        {
            // outputting sql An array of 
            sqls = null;
            // generate count the SQL statements  SqlServer To generate paging, you must do regular splitting 
            if (createCount || dbType == DBType.SqlServer || dbType == DBType.SqlServer2000)
            {
                sqls = SplitSqlForPaging(sql);
                if (sqls == null)
                {
                    // unresolvable SQL statements 
                    throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
                }
            }
            else
            {
                sqls = new string[2];
            }
            // Group paging SQL statements 
            var pageSql = new StringBuilder();
            var end = start + limit;
            if (dbType == DBType.SqlServer2000)
            {
                pageSql.AppendFormat("SELECT TOP @PageEnd {0} {1}", sqls[2], sqls[3]);
                if (start > 1)
                {
                    var orderChange = string.IsNullOrEmpty(sqls[5]) ? null :
                        string.Compare(sqls[5], "desc", true) == 0 ?
                        string.Format("{0} ASC ", sqls[4]) :
                        string.Format("{0} DESC ", sqls[4]);
                    pageSql.Insert(0, "SELECT TOP 100 PERCENT  * FROM (SELECT TOP @PageLimit * FROM ( ");
                    pageSql.AppendFormat(" ) PageTab {0} ) PageTab2 {1}", orderChange, sqls[3]);
                }
            }
            else if (dbType == DBType.SqlServer)
            {
                pageSql.AppendFormat(" Select top @PageEnd ROW_NUMBER() over ({0}) RN , {1}",
                    string.IsNullOrEmpty(sqls[3]) ? "ORDER BY (SELECT NULL)" : sqls[3],
                    sqls[2]);
                // If the query is not first 1 Page, you need to determine the starting line number 
                if (start > 1)
                {
                    pageSql.Insert(0, "Select PageTab.* from ( ");
                    pageSql.Append(" ) PageTab Where RN >= @PageStart");
                }
            }
            else if (dbType == DBType.Oracle)
            {
                pageSql.Append("select ROWNUM RN,  PageTab.* from ");
                pageSql.AppendFormat(" ( {0} ) PageTab ", sql);
                pageSql.Append(" where ROWNUM <= :PageEnd ");
                // If the query is not first 1 Page, you need to determine the starting line number 
                if (start > 1)
                {
                    pageSql.Insert(0, "select * from ( ");
                    pageSql.Append(" ) Where RN>= :PageStart ");
                }
            }
            else if (dbType == DBType.SQLite)
            {
                pageSql.AppendFormat("{0} limit @PageStart,@PageLimit", sql, start, limit);
            }
            // Store the generated pages SQL statements   
            sqls[1] = pageSql.ToString();
            // The temporary test 
            sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");
            Console.WriteLine(" 【 count 】 {0}", sqls[0]);
            Console.WriteLine(" 【 page 】 {0}", sqls[1]);
            Console.WriteLine();
        }
    }

1. This algorithm is used to swap two integers. Exchange a and b, a=a+b; b = a - b; b = a - b; This is what we learned when we were looking for a job, if we were to swap two integers without using a third variable.

2. Since Sql is used for paging under top 2000, data will be queried when the paging start and limit parameters exceed the total number of rows, unless 1 data cannot be found under the condition.

3. Split the Sql statement, referring to part of the source code of PetaPoco.

4. My application scenario is in the dbhelp class. If a method passes the parameters of sql, start and limit, the results of sql's query can be paginated. Where start: the starting row number of the query result (excluding it), limit: the number of rows to fetch. For example, start:0,limit:15 is to extract the first 15 data.


Related articles: