January 15, 2014 at 4:14 pm
Hi guys,
I have a query which I'm trying to optmise and I'm sure there's a better way to do it and wanted some opinions. The query is for a CRM Dynamics 2011 report and references account and contact tables (filtered views rather) and some custom entities. The purpose of the report is to return details about the organisation (accounts), it's employees (contacts), any notes (annotation) on the account made by staff here, appointments/visits (appointments) to the organisation and 2 other things.
Currently it's one query on account that left joins to contact and 5 other tables. The problem I'm having is the query is returning 153000 roughly records when really it only needs to return approx 90 (performed a count of each of these related entities individually). It looks like every meeting, note etc records are being returned once for every record returned by the previous left outer join (which I would expect) i.e 1 organisation has 3 employees, there are 10 notes on the account, instead of getting 10 notes I am getting 30, then for each of the 30 notes I'm getting 20 appointments etc.
I hope that makes sense! Is there a neat way to only return only the notes, appointments etc once only? Currently I've made union selects which query account + contact, account + annotation etc and joined the results together. This SEEMS to work but looks ugly as hell.
Is there another way? Or is the method being used to pull out largely unrelated data (except for the account ID) unsuitable? I don't have access to the report itself at the moment to see how it works but I would personally make one report use 5 or 6 separate queries rather than one mega query. Thoughts and or help would be greatly appreciated.
January 15, 2014 at 6:12 pm
Hard to tell you what you should do since we can't see what you see. It would help if you posted your code and the DDL for the table involved.
January 16, 2014 at 7:03 am
UNION is an aggregate function that is basically going to give you unique values. If you want to eliminate them from the query any other way, it's probably through having the right filters in the JOIN criteria.
Beyond that, as was stated, we're just guessing without actual code & structures.
"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 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply