Query page: Statement 1 has more than 10 times better performance, but it is hard to understand because it has more than one top keyword !!!!
1. Primary Key r_object_id within 1 s
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. Digest, tabdef. fieldt3104x504621623d, tabdef. fieldf3763y504621609s, tabdef. Large,
Tabdef. Digest, tabdef. Digest, 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. the query time is within 10 s.
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. Digest, tabdef. fieldt3104x504621623d, tabdef. fieldf3763y504621609s, tabdef. Large,
Tabdef. Digest, tabdef. Digest, 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