Subquery very slow (any Tips)

[LordLoki]

Hi Friends,

I am Back with a possible Problem, and hope you can help me to get a better Result.

I have a Table with some Information (how obvious )
The Relevant Data is this

Table: ShareData
Service --> Name of a Queue
Intervall --> Time Intervall like 8,9,10,11,12....22
Sprache --> Language like DE,FR,IT,EN
Partner --> Text like ParnerA,PartnerB...
Offered --> Amount of Calls

I want to build a Query now that gives me the Share that each Partner had for a Service,Intervall and Language based on all the data in the ShareData Table

So what i did is build a Query that sums all the data by Partner and a Subquery that gets the Same information without the Partner.

The Query gives me the Result that i want but it is horribly Slow.
now the Questions is if that is to be expected or is my query just horribly bad Designed.

Thanks for Feedback

SELECT SD1.Service, SD1.Intervall, SD1.Sprache, SD1.Partner, Sum(SD1.Offered) / 
(SELECT SUM(SD2.Offered) FROM ShareData as SD2 
WHERE SD1.Service = SD2.Service AND SD1.Intervall=SD2.Intervall AND SD1.Sprache= SD2.Sprache) AS Share
FROM ShareData AS SD1
GROUP BY SD1.Service, SD1.Intervall, SD1.Sprache, SD1.Partner;

 

[spikepl]

For starters, did you put index on all the fields in where and group clauses?

 

[LordLoki]

Was long time not online just wanna let you know that i put indexes in and got a much better result.

Thanks for that tip

 

[namliam]

Just FYI this isnt a subquery or subselect or ... it is a inline select and this is executed PER LINE that your query returns, this is a performance nightmare....

A subselect is/should be much more efficient...
Something like so?

SELECT SD1.Service, SD1.Intervall, SD1.Sprache, SD1.Partner, SD1.PerPartner / sd2.PerSprache AS Share
FROM ( SELECT Service, Intervall, Sprache, Partner, Sum(SD1.Offered) PerPartner
       from sharedata 
       GROUP BY SD1.Service, SD1.Intervall, SD1.Sprache, SD1.Partner) AS SD1
Inner join ( Select Intervall, Sprache, sum(Offered) as PerSprache
             from ShareData
             group by Intervall, Sprache ) as SD2 on sd1.intervall = sd2.intervall
                                                 and sd1.sprache = sd2.sprache

 

[LordLoki]

Hi Namliam,

Thanks for your input, still struggling with the right terms.
in the last 4 weeks i was reading a bit about the whole Query things and found that a join like in your example is much more efficient.

I will rebuild my querys step by step and surely come back here soon when i get the next problem

Thanks and Greets
Loki