Classic SQL Grooming

This post is written based on SQL Server.

1, about pagination code:

SELECT * from TableA select top PageCount * from TableA where ID not in (select Top (pageIndex-1) * Pageco UNT ID from TableA)

Note: Sometimes, we don't want to take all the data out of the database at once, because it allows the customer to wait a long time. So, the above SQL code (segmented fetch data) is a good idea.

PageCount: Indicates a page with several data

PageIndex: This is the first page (starting from 1)

TableA: The name of the table

Core idea: Exclude the Front (top), and then use top to fetch the data---not in is the key

2, about index fragmentation processing:

SELECT * FROM Sys.dm_db_index_physical_stats (db_id (), object_id (' TableA '), NULL, NULL, default) SELECT * FR Om sys.indexes WHERE object_id = object_id (' TableA ')

About these two first send a picture: my test

650) this.width=650; "title=" Qq20150226222534.png "style=" height:356px;width:735px "src=" http://s3.51cto.com/ Wyfs02/m00/59/ec/wkiom1tvlyibq-ohaapppy3ft_a434.jpg "width=" "height=" 382 "alt=" Wkiom1tvlyibq-ohaapppy3ft_ A434.jpg "/>

Key look: Avg_fragmentation_in_percent

When its < 10 o'clock, that is 10%. It is not necessary to process

When its >=, < 30 o'clock, the table is "organized" processing

When it >= 30 o'clock, the table is "rebuilt" for processing

Organization Processing:

Alter index Idx_idcard on TableA REORGANIZE

Re-build Processing:

Alter index Idx_idcard on TableA REBUILD

The key is two words:

650) this.width=650; "title=" Qq20150226222534.png "style=" height:91px;width:502px "src=" http://s3.51cto.com/ Wyfs02/m00/59/ec/wkiom1tvl7bqz7vmaafgoui3zb0060.jpg "width=" 811 "height=" 111 "alt=" Wkiom1tvl7bqz7vmaafgoui3zb0060.jpg "/>

3, about linked object pools

This is actually a C # aspect, write it down here.

After the database connection string, add the

Pooling = true; Connection Lifetime = 50; Min Pool Size = 1; Max Pool Size = 3;

Explain:

Pooling = True Open Object pool

Connection Lifetime = 50 life cycle

This article is from the "Better_power_wisdom" blog, make sure to keep this source http://aonaufly.blog.51cto.com/3554853/1615549

Classic SQL Grooming