Why does this take so long?

[garethl]

Hello all

Is there any sensible reason why this query should take like 20 minutes to run.. other than that we use Citrix!

I mean I know this is a basic query (just a conditional count) but am I doing something stupid here?

SELECT TBL_Agreements.IDAgent, Count(TBL_MeasuresInstalled.IDMeasure) AS CountOfIDMeasure, TBL_Agents.AgentType
FROM TBL_MeasuresInstalled INNER JOIN TBL_Submissions ON TBL_MeasuresInstalled.IDSubmission = TBL_Submissions.IDSubmission, TBL_Schemes INNER JOIN (TBL_Agents INNER JOIN TBL_Agreements ON TBL_Agents.IDAgent = TBL_Agreements.IDAgent) ON TBL_Schemes.IDScheme = TBL_Agreements.IDScheme
GROUP BY TBL_Agreements.IDAgent, TBL_Agents.AgentType, TBL_Schemes.IDProgram
HAVING (((TBL_Schemes.IDProgram)=3));

 

[JANR]

HAVING (((TBL_Schemes.IDProgram)=3));

Changing this to a WHERE clause migth help some. since a where-clause executes first and thus limit the recordset to be evaluated.

WHERE (((TBL_Schemes.IDProgram)=3));

JR

 

[namliam]

Lets start by 'fixing' your sql

SELECT TBL_Agreements.IDAgent
     , Count(TBL_MeasuresInstalled.IDMeasure) AS CountOfIDMeasure
     , TBL_Agents.AgentType
FROM TBL_MeasuresInstalled 
INNER JOIN TBL_Submissions ON TBL_MeasuresInstalled.IDSubmission = TBL_Submissions.IDSubmission
, TBL_Schemes 
INNER JOIN (TBL_Agents 
INNER JOIN TBL_Agreements  ON TBL_Agents.IDAgent = TBL_Agreements.IDAgent) 
                           ON TBL_Schemes.IDScheme = TBL_Agreements.IDScheme
GROUP BY TBL_Agreements.IDAgent, TBL_Agents.AgentType, TBL_Schemes.IDProgram
HAVING (((TBL_Schemes.IDProgram)=3));

Now this more readable code make it quite clear where your sql is broken, you have a missing join which can potentially make a query VERY slow

Not to mention the fact that it is highly unlikely to return the right result

 

[garethl]

Cheers JANR I tried both ways already (though I didn't realise the WHERE should be faster)

Its still a slow beast

Forgot to say - its returning 56 counts which total to 481054 records and that WHERE clause is selecting the 481054 records out of a table of 730832

So not ridiculous amounts of data.

 

[garethl]

oops! missing join.. yeah thats it cheers

 

[DCrake]

Couple of other things to think about.

Use Count(1) As CountOfMeasure instead of the actual field.

Are you grouping on the index fields in the joined tables or the foreign keys in the main table. Faster to use the former as they should have primary key indexes on them.

 

[namliam]

oops! missing join.. yeah thats it cheers

Keep it readable, that will 9 times out of 10 find your problems

 

[garethl]

Are you grouping on the index fields in the joined tables or the foreign keys in the main table

I was actually using the foreign key as well I've changed that now that helps a bit too.

Keep it readable, that will 9 times out of 10 find your problems

Yeah I should of spotted that it was just the way I'd layed out the SQL I didn't