July 7, 2011 at 5:17 am
Hi,
There is a slow running query on one of our servers (See below).
What would be the best way to rewrite this to make it more efficient?
I know that 'IN' is inefficient, and I am going to suggest it is written as a stored procedure instead - but what else can I recommend to make the query more efficient?
SELECTdistinct pay_method,c.username
FROMwarehouse.dbo.dim_customer c (NOLOCK)
INNER JOINwarehouse.dbo.dim_paymentmethods pm (NOLOCK)ON pm.cust_id = c.cust_id
WHEREc.username IN (Name1, Name2, Name3.....)
Thanks,
George
July 7, 2011 at 5:37 am
Doubt the IN is the problem unless there are alot of values. But without having any information regarding datatypes/indexes and so on its more or less impossible to give a good answer.
Have a look at http://www.sqlservercentral.com/articles/Best+Practices/61537/ on how to better post a question like this and add that information to this post and im sure people will be able to help you.
But taking a wild guess... missing an index on "username" maybe?
/T
July 7, 2011 at 5:38 am
Usually the IN predicate is implemented spooling the literals to a temporary internal table, if the input is large enough. Otherwise it is implemented as a set of OR predicates. I would not focus on that point, however.
First of all, get rid of those "NOLOCK" hints if you want to return consistent data. Are you totally aware of the implications of using NOLOCK?
If you want sensible advice on how to tune the query, post the actual execution plan, tables and indexes script and everything we might need to help you.
You can read this article that explains how to post a performance question:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Regards
Gianluca
-- Gianluca Sartori
July 7, 2011 at 5:41 am
Gianluca Sartori (7/7/2011)
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Hrmm interesting... you do know thats not the same link as the one that you have in your signature? ... though they address 2 different things... so nevermind. I guess i should be so fast in grabbing a link 😀
/T
July 7, 2011 at 5:42 am
July 7, 2011 at 5:44 am
Thanks for the links. I am looking through them now, and will post additional information later.
Cheers,
George
July 7, 2011 at 5:46 am
tommyh (7/7/2011)
Gianluca Sartori (7/7/2011)
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Hrmm interesting... you do know thats not the same link as the one that you have in your signature? ... though they address 2 different things... so nevermind. I guess i should be so fast in grabbing a link 😀
/T
Yes, I know they're different links. The one I posted is specifically for performance questions. I have that in my briefcase and I go back to copy it whenever I need to point somebody there. 🙂
-- Gianluca Sartori
July 9, 2011 at 4:38 am
Yes , DDL and Exceution plans are useful.
Two potential problems:
1) rewrite to place as much information of the IN in the JOIN
2) consider the DISTINCT , and how you could rewrite the statement
Jack Vamvas
sqlserver-dba.com
July 12, 2011 at 1:29 pm
I'd bet your biggest issue with this query is simply that there is a large amount of data present, and no indexes to handle them.
Create an index with username, cust_id, and pay_method, and watch the execution time plummet 😛
July 13, 2011 at 2:02 am
kramaswamy (7/12/2011)
I'd bet your biggest issue with this query is simply that there is a large amount of data present, and no indexes to handle them.Create an index with username, cust_id, and pay_method, and watch the execution time plummet 😛
Maybe. Or maybe not.
How can you tell without:
- table script
- existing indexes script
- actual execution plan
- row counts
?
That would be a wild guess IMHO.
However, the OP didn't come back, so it must have been quite easy to tune.
-- Gianluca Sartori
July 13, 2011 at 12:47 pm
You're right, I can't know for sure. But the query he was using was so basic that I'd find it hard to believe that anything else was the reason.
July 13, 2011 at 12:55 pm
...Still waiting on (at the very least) the actual execution plan and index ddl.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply