September 6, 2012 at 7:10 am
I'm trying to improve the performance of this query which is taking a lot of time to run.In the following query, loanhistory is a very large table. Though I'm trying to pull only few days worth of data, is it a good idea to pull the required data of loanhistory into a temp table and then joining this temp table to the loancustomer table or do I leave it as-is?
My assumption is that joining with such a large table will definitely hamper the performance even though there's a where condition.
SELECT a.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loancustomer a
LEFT JOIN loanhistory (NOLOCK) b
ON a.loancustomer=b.loancustomer
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
September 6, 2012 at 7:42 am
sunny.tjk (9/6/2012)
I'm trying to improve the performance of this query which is taking a lot of time to run.In the following query, loanhistory is a very large table. Though I'm trying to pull only few days worth of data, is it a good idea to pull the required data of loanhistory into a temp table and then joining this temp table to the loancustomer table or do I leave it as-is?My assumption is that joining with such a large table will definitely hamper the performance even though there's a where condition.
SELECT a.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loancustomer a
LEFT JOIN loanhistory (NOLOCK) b
ON a.loancustomer=b.loancustomer
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
You might get better performance through declaring a @tempvar = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2) then evaluating b.loanprocesseddate to @tempvar instead of running through your double-nested function list on each iteration.
Have you checked the execution plan?
Could be that an index is what's required on a.loancustomer and/or b.loanhistory with an INCLUDE on b.loanprocesseddate. See what the execution plan says.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
September 6, 2012 at 8:14 am
Actually the functions in your where clause should not render nonSARGable as Derek suggested because it is not a row based call.
It is pretty hard to tell why the performance is poor from just the query. Please see Gail's article on posting performance problems. http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
Given that this appears to be a financial application I shudder that you are using the NOLOCK hint. Are you familiar with what it does and how absolutely horrible it can be when used in the wrong situation?
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2012 at 8:18 am
It was just a thought. Running through multiple transformational functions on each iteration seems intuitively wasteful.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
September 6, 2012 at 8:32 am
sunny.tjk (9/6/2012)
I'm trying to improve the performance of this query which is taking a lot of time to run.In the following query, loanhistory is a very large table. Though I'm trying to pull only few days worth of data, is it a good idea to pull the required data of loanhistory into a temp table and then joining this temp table to the loancustomer table or do I leave it as-is?My assumption is that joining with such a large table will definitely hamper the performance even though there's a where condition.
SELECT a.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loancustomer a
LEFT JOIN loanhistory (NOLOCK) b
ON a.loancustomer=b.loancustomer
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
Just another observation, this query is basically an inner join not a left outer join. Your filter criteria b.loanprocesseddate will eliminate all records where b.loanprocesseddate is null.
I have to agree Sean, we need to see the actual execution plan to start helping.
September 6, 2012 at 8:33 am
derek.colley (9/6/2012)
It was just a thought. Running through multiple transformational functions on each iteration seems intuitively wasteful.
Not sure what you are talking about here, multiple transformational functions on each iteration. This function is only going to be called once when the query is processed: DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2).
September 6, 2012 at 8:38 am
Point taken, it's a join, not processed row-by-row, so not subject to overhead of 'decoding' the date functions then comparing, then doing over again in a loop. So I imagine the optimiser will resolve the DATE stuff internally first before running any comparisons/evaluations?
Not having a good day on the forums today ... can't seem to get my head straight, am posting half-formed thoughts. Apologies for misleading info (on multiple threads).
[resumes lurking for today]
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
September 6, 2012 at 8:42 am
derek.colley (9/6/2012)
Point taken, it's a join, not processed row-by-row, so not subject to overhead of 'decoding' the date functions then comparing, then doing over again in a loop. So I imagine the optimiser will resolve the DATE stuff internally first before running any comparisons/evaluations?Not having a good day on the forums today ... can't seem to get my head straight, am posting half-formed thoughts. Apologies for misleading info (on multiple threads).
[resumes lurking for today]
No worries!!! IF the date inside the function was a column in the table instead of getdate() you would be absolutely spot on. In that case it would render indexing unusable. But since it is a constant it will evaluate that first. This is why getdate() in a select statement will always have the same value for each row no matter how long the query takes. If it processed it row by row the value would change. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2012 at 9:23 am
I'll be sending the execution plan in a bit.
I've a question, I ran the following query and when I looked at the execution plan it's just doing a table scan when there an NC index on loanprocesseddate not sure why. Is there a way to forcefully apply the index?
SELECT b.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loanhistory b
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
September 6, 2012 at 9:27 am
sunny.tjk (9/6/2012)
I'll be sending the execution plan in a bit.I've a question, I ran the following query and when I looked at the execution plan it's just doing a table scan when there an NC index on loanprocesseddate not sure why. Is there a way to forcefully apply the index?
SELECT b.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loanhistory b
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
Would need to see the index definition. If it only has loanprocesseddate then SQL Server believes it more efficient to scan the table since you are pulling back both principalbalanceamount and loanprocesseddate from the loanhistory table.
September 6, 2012 at 9:40 am
Lynn Pettis (9/6/2012)
sunny.tjk (9/6/2012)
I'll be sending the execution plan in a bit.I've a question, I ran the following query and when I looked at the execution plan it's just doing a table scan when there an NC index on loanprocesseddate not sure why. Is there a way to forcefully apply the index?
SELECT b.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loanhistory b
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
Would need to see the index definition. If it only has loanprocesseddate then SQL Server believes it more efficient to scan the table since you are pulling back both principalbalanceamount and loanprocesseddate from the loanhistory table.
There is one NC index on loancustomer and the other on loanprocesseddate.
September 6, 2012 at 9:42 am
sunny.tjk (9/6/2012)
Lynn Pettis (9/6/2012)
sunny.tjk (9/6/2012)
I'll be sending the execution plan in a bit.I've a question, I ran the following query and when I looked at the execution plan it's just doing a table scan when there an NC index on loanprocesseddate not sure why. Is there a way to forcefully apply the index?
SELECT b.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loanhistory b
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
Would need to see the index definition. If it only has loanprocesseddate then SQL Server believes it more efficient to scan the table since you are pulling back both principalbalanceamount and loanprocesseddate from the loanhistory table.
There is one NC index on loancustomer and the other on loanprocesseddate.
As Lynn said, the definition of the index is what matters here, not that there is one. You should post ddl for your tables and all indexes when you post your exec plan.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2012 at 9:46 am
sunny.tjk (9/6/2012)
Lynn Pettis (9/6/2012)
sunny.tjk (9/6/2012)
I'll be sending the execution plan in a bit.I've a question, I ran the following query and when I looked at the execution plan it's just doing a table scan when there an NC index on loanprocesseddate not sure why. Is there a way to forcefully apply the index?
SELECT b.loancustomer ,
b.principalbalanceamount,
b.loanprocesseddate ,
FROM loanhistory b
where b.loanprocesseddate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -2)
Would need to see the index definition. If it only has loanprocesseddate then SQL Server believes it more efficient to scan the table since you are pulling back both principalbalanceamount and loanprocesseddate from the loanhistory table.
There is one NC index on loancustomer and the other on loanprocesseddate.
Doesn't tell me anything. We need to see the index definitions on the tables as well.
The index below may be used, it may not. If this is how your index is currently defined and it isn't being used it is because SQL Server decided that using bookmark lookups to get the principalbalanceamount would be more expensive than doing a table scan.
CREATE INDEX IX_loanprocesseddate on dbo.loanhistory(
loanprocesseddate
);
September 6, 2012 at 9:55 am
Table definitions, index definitions and execution plan please, as per the article that Sean referenced.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply