October 27, 2017 at 3:25 am
Hi All,
I have 2 tables. Credit Account ,Credit Account relationship. Credit Account has 3M+ rows, Relationship has 4M+ rows
These queries get executed at least 20K times per hour. Could you please review the below select statement if this is fine?
SELECT A.ACCOUNT_NBR
FROM dbo.CREDIT_ACCOUNT_RELATIONSHIP B INNERJOIN dbo.CREDIT_ACCOUNT A
ON B.CREDIT_ACCOUNT_ID = A.CREDIT_ACCOUNT_IDWHERE B.RELATIONSHIP_TYPE_CD = 'ONLINE' AND
B.RELATIONSHIP_ID = ?0 AND B.ACCOUNT_REGISTERED_IND = 'Y'AND (ISNULL(A.ACCOUNT_NBR,''))!=
SELECT A.ACCOUNT_NBR
FROM dbo.CREDIT_ACCOUNT_RELATIONSHIP B INNERJOIN dbo.CREDIT_ACCOUNT A
ON B.CREDIT_ACCOUNT_ID = A.CREDIT_ACCOUNT_IDAND B.RELATIONSHIP_TYPE_CD = 'CRM'
where B.RELATIONSHIP_ID = ?0 AND(ISNULL(A.ACCOUNT_NBR,''))!=''
"More Green More Oxygen !! Plant a tree today"
October 27, 2017 at 4:42 am
Can you provide the execution plan for this query?
Also, what indexes exist on the tables used?
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 27, 2017 at 6:33 am
That query doesn't look like it'll run either. Firstly, in your query, you refer to something not having the value ?0. What is "?0"? It's not a a numeric, but you haven't encapsulated it as a string either.
Secondly, your subquery needs to be inside parenthesis, which it is not. You also then go on to use the same aliases inside that subquery, making it quite hard to understand.
I've reformatted your query, so it's a little more readable (you'd concatenated words in some places, such as "A.CREDIT_ACCOUNT_IDWHERE"; which I assume was meant to be A.CREDIT_ACCOUNT_ID and then a new line for your WHERE), and given more realistic aliases (calling your first table A, your second table B, your third table C, aren't helpful; try to use appropriate Aliases (for example CA for CREDIT_ACCOUNT)). As it stands though, this won't run anyway.:SELECT CA.ACCOUNT_NBR
FROM dbo.CREDIT_ACCOUNT_RELATIONSHIP CAR
INNER JOIN dbo.CREDIT_ACCOUNT CA ON CAR.CREDIT_ACCOUNT_ID = CA.CREDIT_ACCOUNT_ID
WHERE CAR.RELATIONSHIP_TYPE_CD = 'ONLINE'
AND CAR.RELATIONSHIP_ID = ?0 --What is ?0?
AND CAR.ACCOUNT_REGISTERED_IND = 'Y'
AND (ISNULL(CA.ACCOUNT_NBR,'')) != SELECT CAsq.ACCOUNT_NBR --This subquery should start with a left parenthesis
FROM dbo.CREDIT_ACCOUNT_RELATIONSHIP CARsq
INNER JOIN dbo.CREDIT_ACCOUNT CAsq ON CARsq.CREDIT_ACCOUNT_ID = CAsq.CREDIT_ACCOUNT_ID
AND CARsq.RELATIONSHIP_TYPE_CD = 'CRM'
WHERE CARsq.RELATIONSHIP_ID = ?0 --Again what is ?0
AND(ISNULL(CAsq.ACCOUNT_NBR,'')) != '' --And it should end with a right parenthesis
/*
Also, I'm not entirely sure that that subquery would return one result, considering it's not referencing the outside query, as far as I could see.
When checking against one value, you have to ensure that your query returns one result, or your query will fail.
Should you be using an IN rather a != to compare to your subquery?
*/
I wish SSC would fix pasting whitespace already...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 27, 2017 at 9:18 am
This part of the query pattern is going to kill performance dead:AND (ISNULL(A.ACCOUNT_NBR,''))
If nothing else, I'd find a way to restructure your data or whatever you have to do to avoid running functions like this on a column in a WHERE clause, JOIN or HAVING clause. It's going to lead to scans. The more data you have, the worse this is going to perform. You're doing it twice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 27, 2017 at 10:18 pm
Thank you all will check the follow the advice
"More Green More Oxygen !! Plant a tree today"
October 27, 2017 at 10:45 pm
One more thing the above sql statement runs multiple times ( 1000 - 5000 ) times in a hour. I was thinking instead of running this statement can I put this in a proc and pass the parameter which will reuse the same plan reducing CPU over head.
"More Green More Oxygen !! Plant a tree today"
October 28, 2017 at 5:15 am
Minaz Amin - Friday, October 27, 2017 10:45 PMOne more thing the above sql statement runs multiple times ( 1000 - 5000 ) times in a hour. I was thinking instead of running this statement can I put this in a proc and pass the parameter which will reuse the same plan reducing CPU over head.
It can help, but tune the query and structure at the same time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply