5
\$\begingroup\$

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
\$\endgroup\$
6
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the advice Malachi, this is great - I have edited my post to show you what I have done, if you could provide me with some more guidance, that would be great. I Converted (Lastmonth) and (CurrentMonth) before the where, this shaved off 2 seconds. Unfortunately, those other columns were created by some idiot in VARCHAR. How would I convert those before the where? \$\endgroup\$ – Mike May 15 '14 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.