I'm running sql analyzer on the following query
SELECT bl.Invoice_Number, bl.Date_Invoice, ti.TranNo, bt.Description,
CONVERT(decimal(15,2), bl.Invoice_Amount) AS Invoice_Amount, co.Company_ID, co.Account_Nbr,
isnull(bl.Reference,' ') as Reference, bl.Billing_Log_RecID AS BillingKey
FROM [CONN.domain.NET].cwwebapp.dbo.Billing_Log bl
LEFT JOIN [App].dob.tarInvoice ti
ON bl.Invoice_Number = dbo._fnStripLeadZeros(ti.TranNo)
INNER JOIN [CONN.domain.NET].cwwebapp.dbo.Billing_Type bt
ON bl.Billing_Type_ID = bt.Billing_Type_ID
LEFT JOIN [CONN.domain.NET].cwwebapp.dbo.Company co
ON bl.Company_RecID = co.Company_RecID
WHERE bl.Date_Invoice >= '2009-05-05'
AND ti.TranNo IS NULL
AND bl.Invoice_Amount <> 0
AND bl.Billing_Type_ID <> 'D'
AND bl.Billing_Type_ID <> 'P'
-- AND bl.Billing_Type_ID <> 'M'
Order By bl.Invoice_Number
The query runs on the [App] server and connects to [Conn] sql server to do a join and the diagram is telling me
remote query cost : 62%
customered index scan [App].[dbo].tarInvoice.[PK__... Cost : 34%
This query is taking 2 mins to run. Any ideas on how would I go about figuring out how to make this run more efficiently? I"m guessing it has to do with connecting to another sql server on the same network.
thanks in advance.
you are doing a complete scan of the tarInvoice clustered index (touch every index entry), see if you can remove the function call dbo._fnStripLeadZeros(ti.TranNo) so it will use the index.
possibly add leading zeros onto bl.Invoice_Number and join to unaltered ti.TranNo
EDIT
add computed column without leading zeros and add an index:
ALTER TABLE dbo.tarInvoice ADD TranNoZeroFree AS Convert(int,TranNo) PERSISTED
GO
CREATE NONCLUSTERED INDEX IX_tarInvoice_TranNoZeroFree ON dbo.tarInvoice (TranNoZeroFree) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO