Newbie: how to speed up this simple query

[François Dejaeghere]

Hello dear readers,

I have 2 tables, let's say
first table: "Appointment", which has 3 fields (I've simplified the schema for better understanding)

ID as GUID

(the key)

Length as Float

TaskID as GUID

((external key)

The second table is called "Task" and has 2 fields (again, simplified schema)

ID as GUID (key)

RequiredLength as Float

Now the problem:
In a kind of "Tasks to plane view", I'm pretty sure I can speed things up since I recall a Sum on each criteria .. I'v tried to use alias, but propably missed something because I only get error message on return ("Invalid Column name").

Here is my query

SELECT     Task.ID, Task.RequiredLength, 
                          (SELECT     SUM(Length) AS Expr1
                            FROM          Appointment
                            WHERE      (TaskID = Task.ID)
                            GROUP BY TaskID) AS PlannedLength
FROM         Task 
WHERE     ((SELECT     SUM(Length) AS Expr1
                         FROM         Appointment AS Appointment_1
                         WHERE     (TaskID = Task.ID)
                         GROUP BY TaskID) < RequiredLength)
 OR ((SELECT     SUM(Length) AS Expr1
                         FROM         Appointment AS Appointment_1
                         WHERE     (TaskID = Task.ID)
                         GROUP BY TaskID) ) IS NULL
 
I have tried the following query, but it does not work :  
SELECT     Task.ID, Task.RequiredLength, 
                          (SELECT     SUM(Length) AS Expr1
                            FROM          Appointment
                            WHERE      (TaskID = Task.ID)
                            GROUP BY TaskID) AS PlannedLength
FROM         Task 
WHERE     ((PlannedLength < RequiredLength) OR (PlannedLength IS NULL))

'************************************************* ****************
Any help would be greatly appreciated, I'm a real newbie :
Thank for your answers,
Happy coding

[code green]

A sub query is inherently slow.
They can usually be replaced by a much faster JOIN.
This looks like it could be re-written as a JOIN.
If you are prepared to make an attempt I will help you along.

[François Dejaeghere]

Thank you Code green,

As I said, any help would ge appreciated, so feel free to show me a few guideline or a straight solution for that query

Thank you in advance
Regards

[code green]

Are you really trying to improve the performance of this query?
I doubt it works.
I can't re-write it because I can't work out what you are trying to do.

[François Dejaeghere]

Ok, thank you for your help anyway

[code green]

I would like to have a go but I just don't have time to study your query.
Can you describe what you are trying to achieve?

[pbmods]

Heya, François.

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.