查詢分頁-----強勢top

來源:互聯網
上載者:User

查詢分頁:語句1效能提升10倍多,只是因為多了個top關鍵字,很不理解啊!!!!

1.查詢時間1s內,r_object_id主鍵

select top 100 * from ( 
select all  doc.r_object_id "objid",doc.name,doc.number, doc.cuid, doc.r_creation_date, doc.security, doc.bordline, doc.twicedline, doc.dline, doc.keynum, doc.creator_name, 
doc.security_name, doc.dline_name, doc.pid, doc.modifier_name, doc.tblcount, doc.r_modify_date, doc.prjdefid, doc.voldefid, doc.tabledefid,  doc.relation_id, 
doc.r_object_type "r_object_type", doc.bcode "bcode", doc.copycount, doc.securityapply, doc.pgholtime, doc.done, doc.disciplineid, doc.disciplineid_name, 
tabdef.fieldp2830b1886150468j, tabdef.fieldb7727l1886150454q, tabdef.fields5243t504621622q, tabdef.fieldt3385x504621623d, tabdef.fieldf3763y504621609s, tabdef.fieldk1536t504621614n, 
tabdef.fieldr1297k504621621z, tabdef.fieldb5089j1350775945q, tabdef.fieldk5874q1350775954t, tabdef.fieldb2206h1350775945a, tabdef.fieldf4308a1350775949v, tabdef.recid, 
CAST(ROW_NUMBER() OVER(ORDER BY doc.r_object_id asc ) as int) as dm_rnum 
from ps_fld_doctree_document_sp  doc 
LEFT OUTER JOIN tabledef00054a9a80001e7d_sp  tabdef ON (doc.r_object_id=tabdef.recid) where (doc.keynum like N'aaaaaaaaaaaaaaa%' and  ((doc.isdel=0) or (doc.isdel=1)) ) 
and (doc.i_has_folder = 1 and doc.i_is_deleted = 0) 
) dm_inline_view_1 
where dm_rnum >= 101   AND dm_rnum <= 200 order by 1 asc


2.查詢時間10s內

select * from ( 
select all  doc.r_object_id "objid",doc.name,doc.number, doc.cuid, doc.r_creation_date, doc.security, doc.bordline, doc.twicedline, doc.dline, doc.keynum, doc.creator_name, 
doc.security_name, doc.dline_name, doc.pid, doc.modifier_name, doc.tblcount, doc.r_modify_date, doc.prjdefid, doc.voldefid, doc.tabledefid,  doc.relation_id, 
doc.r_object_type "r_object_type", doc.bcode "bcode", doc.copycount, doc.securityapply, doc.pgholtime, doc.done, doc.disciplineid, doc.disciplineid_name, 
tabdef.fieldp2830b1886150468j, tabdef.fieldb7727l1886150454q, tabdef.fields5243t504621622q, tabdef.fieldt3385x504621623d, tabdef.fieldf3763y504621609s, tabdef.fieldk1536t504621614n, 
tabdef.fieldr1297k504621621z, tabdef.fieldb5089j1350775945q, tabdef.fieldk5874q1350775954t, tabdef.fieldb2206h1350775945a, tabdef.fieldf4308a1350775949v, tabdef.recid, 
CAST(ROW_NUMBER() OVER(ORDER BY doc.r_object_id asc ) as int) as dm_rnum 
from ps_fld_doctree_document_sp  doc 
LEFT OUTER JOIN tabledef00054a9a80001e7d_sp  tabdef ON (doc.r_object_id=tabdef.recid) where (doc.keynum like N'aaaaaaaaaaaaaaa%' and  ((doc.isdel=0) or (doc.isdel=1)) ) 
and (doc.i_has_folder = 1 and doc.i_is_deleted = 0) 
) dm_inline_view_1 
where dm_rnum >= 101   AND dm_rnum <= 200 order by 1 asc


相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

Tags Index: