June 4, 2008 at 8:59 pm
I have 2 tables (Customers and Contacts). I want to select several fields from Customers, and also several calculated bit fields based on the contacts associated with each customer. In each case, I want a field to output as true if there exists a contact for that customer for which some expression is true.
I ended up creating a view similar to this one:
select customerid,
convert(bit, max(convert(smallint, isactive))) As hasActiveContact,
convert(bit, max(case when Paid < Owed then 1 else 0 end)) As MoneyIsOwed
from Contacts
group by customerid
and then joined Customers to the view. I'm thinking that there has to be a "more correct" way to do this, but I haven't come up with one. Can someone give feedback on how these types of checks could be done better?
June 5, 2008 at 6:29 am
Not knowing the business needs, it looks like what you're doing is correct. Having a calculation or CASE statement in the SELECT criteria is not a bad thing. The only suggestion I have is that instead of creating a view and joining against it, you can simply make your query into a derived table and join against that instead:
SELECT ...
FROM (select customerid,
convert(bit, max(convert(smallint, isactive))) As hasActiveContact,
convert(bit, max(case when Paid < Owed then 1 else 0 end)) As MoneyIsOwed
from Contacts
group by customerid) AS co
JOIN Customers cu
ON co.CustomerId = cu.CustomerId
WHERE...
"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
June 5, 2008 at 6:43 am
Thanks for the reply...it just felt like a hack to convert to smallint, get max and convert back.
Here's what's odd about the derived table - and originally I did a view because I had 5 different procs that needed this information. When I did the derived table, my proc took 6 seconds to run. When I did the view, it takes 1 second to run, as long as I save the view last. If I save the proc last, it takes 6 seconds to run. I tried using a table variable too, which was also 6 seconds. I asked on another board, and I know it has to do with the execution plan, but I don't understand how the difference can be so great depending on which statement is run last.
June 5, 2008 at 6:51 am
Yeah, I actually posted some comments about that issue at your other post.
Not seeing the code or the execution plan, it's hard to say, but it could be parameter sniffing or just a poor execution plan or you need to make some adjustments to join order & such... Again, without seeing more, it's hard to be specific.
"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
June 5, 2008 at 7:06 am
I shifted the order of the joins and it's now taking 1 second regardless of which was saved last. Thanks for the advice.
June 5, 2008 at 7:27 am
Cool! I'm glad to hear something worked. Thanks for the feedback.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply