If you use a database in your project, you'll definitely want to use a paging sort. Before doing database query optimization, I wrote the following code all night to stitch the SQL statement sorted by page
Copy Code code as follows:
<summary>
Single table (view) Get paging SQL statement
</summary>
<param name= "tablename" > table name or view name </param>
<param name= "key" > Unique key </param>
<param name= "Fields" > Obtained fields </param>
<param name= "condition" > Query criteria (not including where) </param>
<param name= "CollatingSequence" > Collation (does not contain order by) </param>
<param name= "pageSize" > Page size </param>
<param name= "PageIndex" > Page number (starting from 1) </param>
<returns> Paging SQL statements </returns>
public static string Getpagingsql (
String TableName,
String key,
String fields,
String condition,
String CollatingSequence,
int PageSize,
int PageIndex)
{
String whereclause = String. Empty;
if (!string. IsNullOrEmpty (condition))
{
Whereclause = string. Format ("WHERE {0}", condition);
}
if (string. IsNullOrEmpty (CollatingSequence))
{
CollatingSequence = string. Format ("{0} ASC", key);
}
StringBuilder sbsql = new StringBuilder ();
Sbsql.appendformat ("Select {0}", Prependtablename (tablename, fields, ', '));
Sbsql.appendformat ("From" (select top {0}), PageSize * pageIndex);
Sbsql.appendformat ("[_rownum_] = Row_number () over (order by {0}),", collatingsequence);
Sbsql.appendformat ("{0}", key);
Sbsql.appendformat ("from {0}", tablename);
Sbsql.appendformat ("{0}", Whereclause);
Sbsql.appendformat (") as [_temptable_]");
Sbsql.appendformat ("INNER JOIN {0} on [_temptable_].{ 1} = {0}. {1} ", TableName, key);
Sbsql.appendformat ("WHERE [_rownum_] > {0}", PageSize * (pageIndex-1));
Sbsql.appendformat ("Order BY" [_temptable_].[ _rownum_] ASC ");
return sbsql.tostring ();
}
<summary>
To add a table name prefix to a field
</summary>
<param name= "tablename" > table name </param>
<param name= "Fields" > Field </param>
<param name= "Separator" > Identify the separator between fields </param>
<returns></returns>
public static string Prependtablename (String tablename, string fields, char separator)
{
StringBuilder sbfields = new StringBuilder ();
string[] Fieldarr = fields. Trim (separator). Split (separator);
foreach (String str in Fieldarr)
{
Sbfields.appendformat ("{0}.{ 1}{2} ", TableName, str. Trim (), separator);
}
Return sbfields.tostring (). TrimEnd (separator);
}
Suppose you have the following Product table:
Copy Code code as follows:
CREATE TABLE [dbo]. [Tbl_product]
(
[ID] [int] IDENTITY (1, 1)
Not NULL,
[ProductId] [varchar] () not NULL,
[ProductName] [nvarchar] () not NULL,
[IsDeleted] [INT] Not NULL
CONSTRAINT [df_tbl_product_isdeleted] DEFAULT ((0)),
CONSTRAINT [pk_tbl_product] PRIMARY KEY CLUSTERED ([ProductId] ASC)
With (Pad_index = off, Statistics_norecompute = off,
Ignore_dup_key = off, Allow_row_locks = ON,
Allow_page_locks = ON) on [PRIMARY]
)
On [PRIMARY]
Tbl_product->id (serial number, non-empty, self-increasing)
Tbl_product->productid (Product ID, primary key)
Tbl_product->productname (product name, not empty)
tbl_product->isdeleted (Virtual delete tag, not null)
Call Basicfunction.getpagingsql ("Tbl_product", "id", "id,productid,productname", "Isdeleted=0", "ProductName ASC, ID DESC ", 5, 5), Basicfunction for the static class where the paging sort method is located, the resulting paging sort SQL statement is as follows (the format has been manually adjusted):
Copy Code code as follows:
SELECT Tbl_product.id,
Tbl_product.productid,
Tbl_product.productname
From (SELECT Top 25
[_rownum_] = Row_number () over (order by ProductName ASC, ID DESC),
Id
From Tbl_product
WHERE isdeleted = 0
) as [_temptable_]
INNER JOIN tbl_product on [_temptable_].id = Tbl_product.id
WHERE [_rownum_] > 20
Order BY [_temptable_]. [_rownum_] Asc
Query the field list, remove the field of concern (here for isdeleted, because the conditions inside the isdeleted=0, detected products are not deleted);
Sort by, when you call the method, you should try to ensure that the basis of the sort can uniquely determine the location of the record in the result set (here added the auxiliary sort basis, ID DESC, if the product has the same name, add the late row in front);
Performance optimization of a little suggestion: if the value of the field is calculated, such as: Total Price = Unit Price * Quantity, and at this time the total price is greater than how much of the record, but also to take the total price increment or descending sort, if not temporary table, the amount of data, waiting to buy a new You ask me why I want to buy a new computer, oh, because you will drop the current computer! O (∩_∩) o~
Another suggestion is that when using row_number, remember to use it with "Top n", n equals Int. MaxValue is faster than "Top N".
Finally, please what kind of person to test performance, please, I database rookie, do not understand the performance of the database test.
All I know is that I have a lot of confidence in the sort of paging I write, (*^__^*)!
Starting: Blog Garden-> Sword without leaving a mark
