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 CompanyJOIN Opportunity on Company.Comp_CompanyId = Opportunity.Oppo_PrimaryCompanyIdJOIN Budget on  Opportunity.Oppo_OpportunityId = Budget.budg_OpportunityIdJOIN Users on company.Comp_PrimaryUserId = User_UserIdJoin Channel on User_PrimaryChannelId = Channel.Chan_ChannelIdJOIN [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 NULLOrder by budg_tempDODate1 ASC
Query of consultants and their expected payments for the month
performance;sql;datetime;sql server
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.