我有一个sql查询,从视图中只获取一条记录。我的视图是由很多函数组成的。它将归还大约6万张唱片。我想从这个视图中获取的最上面的记录是8-9秒。我如何优化我的视图,使它最多需要1-2秒。这是我的视图和sql查询。请帮助!任何建议都将是宝贵的。提前感谢。
CREATE View dbo.OMOrderPrePaymentINT
As
select
dbo.int_payment_customer_number_out('','OMOrderPaymentMasterINT',payment_id)
as customer_number,
dbo.int_customer_name_out('','OMOrderPaymentMasterINT',payment_id)
as customer_name,
dbo.int_FormatDate('','OMOrderPaymentMasterINT',document_date)
as payment_date,
dbo.int_payment_amount_out('','OMOrderPaymentMasterINT',document_amount)
as payment_amount,
dbo.int_checkbook_id_out('','OMOrderPaymentMasterINT',null)
as checkbook_id,
dbo.int_cheque_number_out('','OMOrderPaymentMasterINT',payment_id)
as cheque_number,
dbo.int_cc_type_out ('','OMOrderPaymentMasterINT',payment_id)
as cc_type,
dbo.int_cc_number_out('','OMOrderPaymentMasterINT',payment_id)
as cc_number,
dbo.int_cc_authcode_out('','OMOrderPaymentMasterINT',payment_id)
as cc_authcode,
dbo.int_ach_account_type_out('','OMOrderPaymentMasterINT',payment_id)
as ach_account_type,
dbo.int_ach_account_number_out('','OMOrderPaymentMasterINT',payment_id)
as ach_account_number,
dbo.int_ach_authcode_out('','OMOrderPaymentMasterINT',payment_id)
as ach_authcode,
dbo.int_expiration_date_out('','OMOrderPaymentMasterINT',payment_id)
as creditcard_expiration_date,
dbo.int_order_payment_type_out('','OMOrderPaymentMasterINT',payment_id)
as payment_type,
dbo.int_payment_method_out('','OMOrderPaymentMasterINT',payment_id)
as payment_method,
dbo.int_erp_payment_action_out('','OMOrderPaymentMasterINT',null)
as [action],
dbo.int_modified_user_id_out('','',null)
as modified_user_id,
'NOT MAPPED'
as void_date,
export_completed
as exportcompleted,
dbo.int_ordergroup_id_out('','OMOrderPaymentMasterINT',payment_id)
as ordergroup_id,
'ECOM'
as USRDEFND1,
dbo.int_ResponseToken_out('','OMOrderPaymentMasterINT',payment_id)
as USRDEFND4,
dbo.int_transaction_id_out('','OMOrderPaymentMasterINT',payment_id)
as USRDEFND5
from PaymentLine
where dbo.int_exportPayment(payment_id) = 1
SQL查询select top 1 *
from OMOrderPrePaymentINT
where ordergroup_id = '943177C1-50B6-4E7C-A442-BA90CF2A03F6'
order by payment_date desc
哎哟。
用户定义函数是SQL server中最糟糕的性能破坏者,并且您的视图使用了大量的。UDF以逐行模式工作,因此需要一些时间。
尽量用内联SQL代替它们
内联sql不能对函数做什么?每个函数调用都将使用该给定函数的查询执行计划。如果您使用内联SQL来代替函数调用,那么将使用一个单一的总体(预期的最有效)查询执行计划。