本文介绍了SQLServer和Oracle数据库的分页查询方法，包括TOP/NOT IN、ROW_NUMBER() OVER()和OFFSET/FETCH NEXT的方式，并通过性能对比指出在不同情况下的优劣。此外，还展示了如何获取数据库、表和字段的详细信息。在C#中，通过LINQ进行分页查询时，使用AsParallel()可以显著提高查询效率。
一、SQL Server 分页
-
--top
not
in方式
-
select top 条数 *
from tablename
-
where Id not in (select top 条数*页数 Id from tablename)
-
-
-
-
--ROW_NUMBER() OVER()方式
-
select * from (
-
select *, ROW_NUMBER()
OVER(
Order by Id ) AS RowNumber
from tablename
-
) as b
-
where RowNumber BETWEEN 当前页数-1*条数 and 页数*条数
-
-
-
-
--offset fetch next方式
-
--SQL2012以上的版本才支持
-
select * from tablename
-
order by Id offset 页数 row fetch next 条数 row only
-
-
-
分析：在数据量较大时
-
-
top not in方式：查询靠前的数据速度较快
-
-
ROW_NUMBER() OVER()方式：查询靠后的数据速度比上一种较快
-
-
offset fetch next方式：速度稳定，优于前2种，但sql版本限制2012及以上才可使用
模拟测试
-
-
-1、首先创建了一张TestAdmin表，主键为ID字段
int类型且自增
-
create table TestAdmin (
-
Id int identity(1,1) primary Key,
-
Name varchar(Max),
-
Age int
-
)
-
-
--2、接着批量导入1000条模拟数据
-
-
declare @count int
-
--这里定义模拟数据条数
-
set @count=
1000
-
-
-
while(@count>
0)
-
begin
-
insert into TestAdmin (Name,Age) values ('zhuyuan'+convert(varchar,@count),@count)
-
set @count=@count
-1
-
end
-
-
-
-3查询数据集
-
select *
from TestAdmin
c#中linq分页查询
-
最近需要对客户的系统进行升级，在对一张记录只有
7767条记录的表进行分次查询时，每次查询
500条，
16次查询居然使用了
2分钟时间。代码如下：
-
-
public static List<T> GetList(int pageIndex, int pageSize, out int count)
-
{
-
using (
var db =
new ClientDBDataContext(DBHelper.DBConString))
-
{
-
var query = db.DataBase_Table;
-
-
count = query.Count();
-
return query.Skip((pageIndex -
1) * pageSize).Take(pageSize).ToList();
-
}
-
}
-
-
调试结果显示，一次比一次查询的时间长，到第
14次查询时，耗时
20s之多，不忍直视，那还有记录达到百万条的又该如何？？？
-
-
查看DataBase_Table的LINQ扩展时，发现了AsParallel()，查看说明：启用查询的并行化。
-
-
-
-
public static List<T> GetList(int pageIndex, int pageSize, out int count)
-
{
-
using (
var db =
new ClientDBDataContext(DBHelper.DBConString))
-
{
-
var query = db.DataBase_Table.AsParallel();
-
-
count = query.Count();
-
return query.Skip((pageIndex -
1) * pageSize).Take(pageSize).ToList();
-
}
-
}
-
-
运行，整个
16次查询不到
1s。
看来代码的优化在某些时候是非常重要的……
二、SQLServer、Oracle获取数据库、表、表里字段、类型、注释
-
--获取所有非系统数据库
-
select name
from master..
sysdatabases where name not in('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs', 'ReportServer', 'ReportServerTempDB')
-
-
--获取某个表的详细字段信息
-
select c.name,t.name,c.prec,p.value from syscolumns c
-
inner join systypes t on c.xtype= t.xtype
-
inner
join sysobjects o
on c.id= o.id
-
inner
join sys.extended_properties p
on c.id = p.major_id
and c.colid = p.minor_id
-
where o.xtype=
'u'
and t.status=
0
and o.name=
'表名'
-
-
-
--获取所有表信息
-
select *
from sysobjects
where XType=
'U'
-
-
--字段信息，关联表id=syscolumns.id
-
select *
from syscolumns
-
-
--注释信息，关联字段信息major_id=syscolumns.id
-
select *
from sys.extended_properties
-
-
--主键自增信息，关联id =syscolumns.id
-
select *
from sysindexkeys
-
select *
from sysindexes
-
-
-
--表详细信息（字段名，类型，长度，是否是主键，是否是标识，允许空，注释）
-
select a.name N
'字段名', b.name N
'类型',
-
COLUMNPROPERTY(a.id,a.name,
'PRECISION')
as N
'长度',
-
(
case when (select count(*) from sysobjects
-
where (name in (select name from sysindexes
-
where (id = a.id)
AND (
indid in (select indid from sysindexkeys
-
where (id = a.id)
AND (
colid in (select colid from syscolumns
-
where (id = a.id)
AND (
name = a.name)))))))
AND (
xtype = 'PK'))>0 then 'PK'
else '' end) N'主键',
-
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=
1 then
'identity'
else
'' end) N
'标识',
-
(
case
when a.isnullable=
1 then
'true'
else
'false' end) N
'允许空',
-
isnull(g.[
value],
'') AS N
'注释'
-
from syscolumns a
-
left
join systypes b
-
on a.xtype=b.xusertype
-
inner
join sysobjects d
-
on a.id=d.id
and d.xtype=
'U'
and d.name<>
'dtproperties'
-
left
join syscomments e
-
on a.cdefault=e.id
-
left
join sys.extended_properties g
-
on a.id=g.major_id AND a.colid = g.minor_id
-
where d.name=
'表名'
-
order
by a.colorder
三、Oracle：
-
--获取当前用户所有的表
-
SELECT * FROM user_tables
-
-
--获取所有列，列信息
-
SELECT * FROM user_tab_columns
-
-
-
-
-2个视图中分别存储了当前用户的表信息
-
--user_tab_columns：表中列的详情，但是没有是否是主键的标识
-
--user_cons_columns：约束视图。
-
SELECT utc.COLUMN_NAME
as 列名,ucc.COLUMN_NAME AS 主键, utc.DATA_TYPE
as 列类型,utc.DATA_LENGTH
as 类型长度,utc.NULLABLE
as 是否可为空 FROM user_tab_columns utc
-
left
join user_cons_columns ucc
on utc.TABLE_NAME = ucc.TABLE_NAME
-
where POSITION=
1 AND ucc.TABLE_NAME =
'你的表名'
-
ORDER
by utc.COLUMN_ID
-
-
--user_col_comments：注释
扫一扫
1828
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
