hi, thanks for any help with this...
i need to select the first x records from a view ordered by ID descending.
when the view does not contain an ORDER BY statement then the query is rapid.
however when i put an ORDER BY statement in then the query becomes extremely
slow (from 1 sec to nearly 1 min).
i tried to add a descending index on the docs_search_terms table then
recomputed the indexes afterwards with little effect.
i will paste the SQL and the plan below. i would be grateful for any assistance
with this. thanks.
Example Sql
---
select first 500 * from VW_DOCS_BY_SEARCH_TERMS_LIST where terms containing
'invoice';
View
----
CREATE VIEW VW_DOCS_BY_SEARCH_TERMS_LIST(
TERMS,
ID,
ID_BUILDING,
ID_BTCARD,
ID_INSPOLICY,
ID_LOT,
USERCHECKEDOUT_NAME,
USERREGISTEREDBYNAME,
AUTHOR,
OWNER_NAME,
FILENAME,
FILEEXTENSION,
FILE_CREATED_DATE,
DESCRIPTION,
REVISIONNUMBER,
RELATED_MATTER,
MODIFIED_DATE,
RELATED_CONTACTS,
DOCUMENT_CODE,
DATE_MODIFIED,
NOTES,
DATE_REGISTERED,
FOLDERNAME,
LOT_DISPLAY,
CARD_DISPLAY,
COUNT_TASKS,
ARCHIVED,
PURCHASEORDER_NUMBER,
SUPPLIER_INVOICE_NUMBER,
ID_CUSTOM_LIST1,
ID_CUSTOM_LIST2,
CUSTOM_LIST1_DESCRIPTION,
CUSTOM_LIST2_DESCRIPTION,
CUSTOM_DATE1,
CUSTOM_DATE2,
CUSTOM_TEXT1,
CUSTOM_TEXT2,
MATTER_TYPE_ID,
ISTEMPLATEYESNO)
AS
select
t.terms,
d.id,
d.id_building,
id_btcard,
id_inspolicy,
id_lot,
udc.user_name,
udr.user_name,
d.author,
udo.user_name as owner_name,
d.filename,
d.fileextension,
d.file_created_date,
d.description,
d.revisionnumber,
m.matter_display,
d.modified_date,
d.contact_displays,
d.document_code,
md.modified_date,
r.notes,
d.date_registered,
f.description,
l.lot_display,
tm.tablefriendlyname,
d.count_tasks,
d.archived,
poh.order_number,
poh.supplier_invoice_number,
d.id_custom_list1,
d.id_custom_list2,
cl1.description,
cl2.description,
d.custom_date1,
d.custom_date2,
d.custom_text1,
d.custom_text2,
m.matter_type_id,
iif(d.id_type in (2, 6, 7), 'Yes', 'No')
from docs_search_terms t
join dms_document d on d.id = t.id
join matter m on m.matter_id = d.id_building
join user_detail udc on udc.user_id = d.id_usercheckedout
join user_detail udo on udo.user_id = d.id_owner
join user_detail udr on udr.user_id = d.id_userregisteredby
join documentlist_modified_date md on md.id_document = d.id
join dms_document_revision r on r.id = d.id_latest_revision
join dms_folders f on f.id = d.id_folder
join lot l on l.id = d.id_lot
join bt_table_meta tm on tm.id = d.id_btcard
left join purchase_order_header poh on poh.id_document = d.id
join dms_custom_list1 cl1 on cl1.id = d.id_custom_list1
join dms_custom_list2 cl2 on cl2.id = d.id_custom_list2
where d.id <> 0
and d.deleted = 0
order by t.id desc
;
Plan
----
PLAN SORT (JOIN (JOIN (JOIN (VW_DOCS_BY_SEARCH_TERMS_LIST T NATURAL,
VW_DOCS_BY_SEARCH_TERMS_LIST D INDEX (PK_DMS_DOCUMENT),
VW_DOCS_BY_SEARCH_TERMS_LIST F INDEX (PK_DMS_FOLDERS),
VW_DOCS_BY_SEARCH_TERMS_LIST TM INDEX (PK_BT_TABLE_META),
VW_DOCS_BY_SEARCH_TERMS_LIST UDC INDEX (PK_USER_DETAIL),
VW_DOCS_BY_SEARCH_TERMS_LIST UDO INDEX (PK_USER_DETAIL),
VW_DOCS_BY_SEARCH_TERMS_LIST UDR INDEX (PK_USER_DETAIL),
VW_DOCS_BY_SEARCH_TERMS_LIST M INDEX (PK_MATTER), VW_DOCS_BY_SEARCH_TERMS_LIST
L INDEX (PK_LOT), VW_DOCS_BY_SEARCH_TERMS_LIST R INDEX (PK_DMS_REVISION),
VW_DOCS_BY_SEARCH_TERMS_LIST MD INDEX (FK_DOCUMENTLIST_MODIFIED_DATE_1)),
VW_DOCS_BY_SEARCH_TERMS_LIST POH INDEX (FK_PURCHASE_ORDER_HEADER_3)),
VW_DOCS_BY_SEARCH_TERMS_LIST CL2 INDEX (PK_DMS_CUSTOM_LIST2),
VW_DOCS_BY_SEARCH_TERMS_LIST CL1 INDEX (PK_DMS_CUSTOM_LIST1)))
