Query of consultants and their expected payments for the month

Question

I have the following SQL query which takes ~3 seconds to run, maybe ~4. This populates a consultants table with their expected payments for the month.

Unfortunately, the page only loads when this query is finished. Is there any way to make it quicker?

Is there anything you notice that I could do better or any best practices I could follow?

DECLARE @lastMonthDate VARCHAR(10)
DECLARE @currentMonthDate VARCHAR(10)
DECLARE @Year VARCHAR(10)

SET @lastMonthDate = CONVERT(VARCHAR(4), DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())))+'-21'
SET @currentMonthDate = CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE()))+'-20'
SET @Year = DATEPART(YEAR, GETDATE())

SELECT Comp_Name, comp_idnr, oppo_date17_1sent,datename(month, oppo_date17_1sent), budg_tempDODate1, CONVERT(DECIMAL(10,2), budg_do1_total) AS budg_do1_total, budg_paymentMethodM_R,  v_ClientTransactionSummary.HyphenStatus, v_ClientTransactionSummary.TransactionDate FROM Company

JOIN Opportunity on Company.Comp_CompanyId = Opportunity.Oppo_PrimaryCompanyId
JOIN Budget on  Opportunity.Oppo_OpportunityId = Budget.budg_OpportunityId
JOIN Users on company.Comp_PrimaryUserId = User_UserId
Join Channel on User_PrimaryChannelId = Channel.Chan_ChannelId
JOIN [Server2].PayTrader.dbo.v_ClientTransactionSummary on Company.comp_idnr COLLATE SQL_Latin1_General_CP1_CI_AS = v_ClientTransactionSummary.IDNumber COLLATE SQL_Latin1_General_CP1_CI_AS AND budg_do1_total = v_ClientTransactionSummary.ExpectedAmount AND CONVERT(Date, budg_tempDODate1, 105) = CONVERT(Date, v_ClientTransactionSummary.TransactionDate, 105)
WHERE CONVERT(DATE, budg_tempDODate1) BETWEEN CONVERT(DATE, @lastMonthDate) AND CONVERT(DATE, @currentMonthDate) AND User_FirstName = @Name AND User_LastName = @Lastname AND Oppo_deleted IS NULL
Order by budg_tempDODate1 ASC

Answer 1

you are doing a lot of CONVERTing in this query.

Instead of converting the Date variables inside the WHERE clause you should do that before you even start the query, otherwise you are running this convert for every row in the SELECT because the WHERE decides if a row should be kept or discarded from the returned table.

---

Are the date fields not already in DateTime format?

get rid of all the Converts that are not needed.

---

also you should format your code with some indentation so that it is easier to read, for you and for others who will maintain the code you write.