March 6, 2008 at 7:36 am
-- There is obviously a TZ issue here. This was posted after the next 2... Sorry if it gets confusing.
On the contrary - when dealing with large data sets, a little experimentation can give surprising payoffs. After the above, I would suggest replacing the DISTINCT with a GROUP BY in an 'onion' SELECT...
SELECT COUNT(t.TypesCount) As TypesCount, t.CallsTypeFROM (
SELECT RecordedCalls.ID As TypesCount, RecordedCalls.CallType as CallsType
...
GROUP BY RecordedCalls.CallType, RecordedCalls.ID
) t
GROUP BY t.CallsType
A further 'skin' could be added to this 'onion' - if you look at OP's code, there's another query selecting from the result of the first query. The fact that results are coming back in about a minute from a table of a billion rows, or 100 million, or 10 million (whichever it is!) indicates that any useful indexes are most likely already in place.
I can't say for sure that running two group by's is any different than the distinct, but I am not going to say that it isn't possible it performs well in some cases.
As for removing the Left outer join. I agree. There is no benefit.
However, based upon what the OP has stated so far about the wide range of possible combinations in the where clause, I don't see significant improvements in the long run.
I would still like to see an explain plan, DDL and histograms to get a better feel for the data layout.
I would love to be proven wrong, but I still feel that getting a significant decrease in this query will have to be done via hardware (or re-write of the app that is feeding this call), not SQL!
March 6, 2008 at 8:17 am
Bob Fazio (3/5/2008)
The more I look at this, there is almost nothing that you are going to be able to do to help this query. The only suggestion I have for you isan index
Create index xxx on RecordedCalls(CallType,ID)
SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
...
group by RecordedCalls.CallType
That's about the only thing that you can count on being useful since this is obviously a dynamically generated query.
On the contrary - when dealing with large data sets, a little experimentation can give surprising payoffs. After the above, I would suggest replacing the DISTINCT with a GROUP BY in an 'onion' SELECT...
SELECT COUNT(t.TypesCount) As TypesCount, t.CallsType
FROM (
SELECT RecordedCalls.ID As TypesCount, RecordedCalls.CallType as CallsType
...
GROUP BY RecordedCalls.CallType, RecordedCalls.ID
) t
GROUP BY t.CallsType
A further 'skin' could be added to this 'onion' - if you look at OP's code, there's another query selecting from the result of the first query. The fact that results are coming back in about a minute from a table of a billion rows, or 100 million, or 10 million (whichever it is!) indicates that any useful indexes are most likely already in place.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2008 at 8:34 am
What you can do is loose the left join.
No columns of these objects are actualy used in the result.
and the left contains a nested innerjoin, so this will not effect your
resulst.
/*
LEFT OUTER JOIN Tags
INNER JOIN RecordedCallsTags
ON Tags.ID = RecordedCallsTags.TagID
ON RecordedCalls.ID = RecordedCallsTags.CallID
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 7, 2008 at 12:45 am
As the previous posters have said, you need to provide more information (DDL, sample data or at least row count in each table, complete SP) to receive any reasonable help.
You have indicated your query (or at least the filters) is dynamically built by the results of executing another SP, yet the sample provide shows no indication of such activity as you select columns from a single table, and the where clause contains columns from the same table. This indicates that the other table joins are unnecessary, unless they are being used to limit the result set, but based on the table names (I read a lot into these things sometimes) I would suspect that is not the aim as that would mean integrity issues.
If you need to potentially utilise the other tables with clauses not provided, use better dynamic sql, or have different queries that can be executed depending on the population of the return values from the prior SP.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply