Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #12355 > unrolled thread

Need to improve speed of some SQL

Back to article view | Back to comp.databases.ms-access

Contents

  Need to improve speed of some SQL internet.shopping@foobox.com - 2017-02-05 11:05 -0800
    Re: Need to improve speed of some SQL Patrick Finucane <patrickfinucanetx@gmail.com> - 2017-02-05 12:30 -0800
      Re: Need to improve speed of some SQL internet.shopping@foobox.com - 2017-02-06 01:57 -0800
      Re: Need to improve speed of some SQL Patrick Finucane <patrickfinucanetx@gmail.com> - 2017-02-06 10:30 -0800
    Re: Need to improve speed of some SQL Mike P <mikewpayne@tiscali.co.uk> - 2017-02-06 02:55 -0800
      Re: Need to improve speed of some SQL internet.shopping@foobox.com - 2017-02-06 07:35 -0800
        Re: Need to improve speed of some SQL Ron Paii <ron81pai@gmail.com> - 2017-02-06 08:23 -0800
      Re: Need to improve speed of some SQL internet.shopping@foobox.com - 2017-02-07 03:56 -0800
        Re: Need to improve speed of some SQL Ron Weiner <rw@domain.com> - 2017-02-07 11:26 -0500

#12355 — Need to improve speed of some SQL

I have a local table TasksLocal that contains a relevant field TaskID 

I am wishing to insert into another local table some other values for just those with the same TaskID. 

I have the query: 

"INSERT INTO StockRequisitionCostLocal" _ 
& " SELECT StockRequisitionCost.* " _ 
& " FROM StockRequisitionCost INNER JOIN TasksLocal ON StockRequisitionCost.TaskID =TasksLocal.TaskID" 

Now this works correctly but is very slow. 

StockRequistionCost is another query that sums certain values 

SELECT StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID, Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID; 


What I think is happening is that the full StockRequistionCost is being executed and then the selection for the relevant Tasks is made.  Since many items are being summed, this is taking a long time.  There are over 650,00 records being summed to produce 17,500 totals (i.e. Tasks)

I want StockRequistionCost to be executed only for those relevant Tasks. 

I have tried to rewrite my query as 
"INSERT INTO StockRequisitionCostLocal" _ 
                        & " Select StockRequisitionCost.*" _ 
                        & " From StockRequisitionCost" _ 
                        & " Where Exists (Select TaskID From TasksLocal Where TaskID = StockRequisitionCost.TaskID)" 

However this does not speed things up. 

How do I speed up the query?  I want the summations in the StockRequistionCost query to be calculated only for those TaskID in the table TasksLocal 

[toc] | [next] | [standalone]

#12356

On Sunday, February 5, 2017 at 2:05:30 PM UTC-5, internet...@foobox.com wrote:
> I have a local table TasksLocal that contains a relevant field TaskID 
> 
> I am wishing to insert into another local table some other values for just those with the same TaskID. 
> 
> I have the query: 
> 
> "INSERT INTO StockRequisitionCostLocal" _ 
> & " SELECT StockRequisitionCost.* " _ 
> & " FROM StockRequisitionCost INNER JOIN TasksLocal ON StockRequisitionCost.TaskID =TasksLocal.TaskID" 
> 
> Now this works correctly but is very slow. 
> 
> StockRequistionCost is another query that sums certain values 
> 
> SELECT StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID, Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
> FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
> GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID; 
> 
> 
> What I think is happening is that the full StockRequistionCost is being executed and then the selection for the relevant Tasks is made.  Since many items are being summed, this is taking a long time.  There are over 650,00 records being summed to produce 17,500 totals (i.e. Tasks)
> 
> I want StockRequistionCost to be executed only for those relevant Tasks. 
> 
> I have tried to rewrite my query as 
> "INSERT INTO StockRequisitionCostLocal" _ 
>                         & " Select StockRequisitionCost.*" _ 
>                         & " From StockRequisitionCost" _ 
>                         & " Where Exists (Select TaskID From TasksLocal Where TaskID = StockRequisitionCost.TaskID)" 
> 
> However this does not speed things up. 
> 
> How do I speed up the query?  I want the summations in the StockRequistionCost query to be calculated only for those TaskID in the table TasksLocal

Is your sql to sum the recs fast?  Verify.

Is your local table indexed on the field?  how about the source table?

[toc] | [prev] | [next] | [standalone]

#12357

Good idea about indexing the local table.  Forgot to do that. However it does not improve matters.

When I run the query StockRequisitionCost it takes about 20 seconds.  When used in the Insert query where I am trying to select a subset (at the moment a subset of size 1 !! ) it still takes 20 seconds.  This suggests to me that the summations are carried out on all the records before the selection is made.

20 seconds may not seem a long time but this is part of another process and I am trying to speed every stop up.

Jim




 Sunday, 5 February 2017 20:30:48 UTC, Patrick Finucane  wrote:
> On Sunday, February 5, 2017 at 2:05:30 PM UTC-5, internet...@foobox.com wrote:
> > I have a local table TasksLocal that contains a relevant field TaskID 
> > 
> > I am wishing to insert into another local table some other values for just those with the same TaskID. 
> > 
> > I have the query: 
> > 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> > & " SELECT StockRequisitionCost.* " _ 
> > & " FROM StockRequisitionCost INNER JOIN TasksLocal ON StockRequisitionCost.TaskID =TasksLocal.TaskID" 
> > 
> > Now this works correctly but is very slow. 
> > 
> > StockRequistionCost is another query that sums certain values 
> > 
> > SELECT StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID, Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
> > FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
> > GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID; 
> > 
> > 
> > What I think is happening is that the full StockRequistionCost is being executed and then the selection for the relevant Tasks is made.  Since many items are being summed, this is taking a long time.  There are over 650,00 records being summed to produce 17,500 totals (i.e. Tasks)
> > 
> > I want StockRequistionCost to be executed only for those relevant Tasks. 
> > 
> > I have tried to rewrite my query as 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> >                         & " Select StockRequisitionCost.*" _ 
> >                         & " From StockRequisitionCost" _ 
> >                         & " Where Exists (Select TaskID From TasksLocal Where TaskID = StockRequisitionCost.TaskID)" 
> > 
> > However this does not speed things up. 
> > 
> > How do I speed up the query?  I want the summations in the StockRequistionCost query to be calculated only for those TaskID in the table TasksLocal
> 
> Is your sql to sum the recs fast?  Verify.
> 
> Is your local table indexed on the field?  how about the source table?

[toc] | [prev] | [next] | [standalone]

#12361

On Sunday, February 5, 2017 at 3:30:48 PM UTC-5, Patrick Finucane wrote:
> On Sunday, February 5, 2017 at 2:05:30 PM UTC-5, internet...@foobox.com wrote:
> > I have a local table TasksLocal that contains a relevant field TaskID 
> > 
> > I am wishing to insert into another local table some other values for just those with the same TaskID. 
> > 
> > I have the query: 
> > 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> > & " SELECT StockRequisitionCost.* " _ 
> > & " FROM StockRequisitionCost INNER JOIN TasksLocal ON StockRequisitionCost.TaskID =TasksLocal.TaskID" 
> > 
> > Now this works correctly but is very slow. 
> > 
> > StockRequistionCost is another query that sums certain values 
> > 
> > SELECT StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID, Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
> > FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
> > GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID; 
> > 
> > 
> > What I think is happening is that the full StockRequistionCost is being executed and then the selection for the relevant Tasks is made.  Since many items are being summed, this is taking a long time.  There are over 650,00 records being summed to produce 17,500 totals (i.e. Tasks)
> > 
> > I want StockRequistionCost to be executed only for those relevant Tasks. 
> > 
> > I have tried to rewrite my query as 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> >                         & " Select StockRequisitionCost.*" _ 
> >                         & " From StockRequisitionCost" _ 
> >                         & " Where Exists (Select TaskID From TasksLocal Where TaskID = StockRequisitionCost.TaskID)" 
> > 
> > However this does not speed things up. 
> > 
> > How do I speed up the query?  I want the summations in the StockRequistionCost query to be calculated only for those TaskID in the table TasksLocal
> 
> Is your sql to sum the recs fast?  Verify.
> 
> Is your local table indexed on the field?  how about the source table?

Maybe split your query then.  In the first query, something like
  Select CustID from Customer WHere ....
and make that Query1.  That should be quick.

Now run your insert query off of IDs matching ids in query1/

[toc] | [prev] | [next] | [standalone]

#12358

On Sunday, February 5, 2017 at 7:05:30 PM UTC, internet...@foobox.com wrote:
> I have a local table TasksLocal that contains a relevant field TaskID 
> 
> I am wishing to insert into another local table some other values for just those with the same TaskID. 
> 
> I have the query: 
> 
> "INSERT INTO StockRequisitionCostLocal" _ 
> & " SELECT StockRequisitionCost.* " _ 
> & " FROM StockRequisitionCost INNER JOIN TasksLocal ON StockRequisitionCost.TaskID =TasksLocal.TaskID" 
> 
> Now this works correctly but is very slow. 
> 
> StockRequistionCost is another query that sums certain values 
> 
> SELECT StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID, Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
> FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
> GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID; 
> 
> 
> What I think is happening is that the full StockRequistionCost is being executed and then the selection for the relevant Tasks is made.  Since many items are being summed, this is taking a long time.  There are over 650,00 records being summed to produce 17,500 totals (i.e. Tasks)
> 
> I want StockRequistionCost to be executed only for those relevant Tasks. 
> 
> I have tried to rewrite my query as 
> "INSERT INTO StockRequisitionCostLocal" _ 
>                         & " Select StockRequisitionCost.*" _ 
>                         & " From StockRequisitionCost" _ 
>                         & " Where Exists (Select TaskID From TasksLocal Where TaskID = StockRequisitionCost.TaskID)" 
> 
> However this does not speed things up. 
> 
> How do I speed up the query?  I want the summations in the StockRequistionCost query to be calculated only for those TaskID in the table TasksLocal

Hi,  

The selection by TaskID is performed in the top level query. So doesn't that indicate that the GROUP BY clause in the query StockRequistionCost is performed before the TaskID selection, as you suspect?
It is not apparent that there is any selection on TasksLocal.TaskID in the query StockRequistionCost.
It would help if you could show the query, StockRequisitionItemsQuery .

Is there some confusion between StockRequisitionCost and StockRequisition?

Mike P.

6/2/17

[toc] | [prev] | [next] | [standalone]

#12359

If, as a trial, I change the query to be

     CurrentDb.Execute "INSERT INTO StockRequisitionCostLocal" _
                        & " Select StockRequisitionCost.*" _
                        & " From StockRequisitionCost" _
                        & " Where TaskID = 39525"
where I am filtering a specific TaskID, it runs almost instantly.  This suggests to me that it calculates the sum for only that one Task.  Not calculating all the sums and then selecting one.

I need to get it to work for a collection (possible small) of Tasks.  I was hoping the the Where Exists would send just those TaskIDs to the Group By that I need.

I do not know how Access calculates its SQL

There is no confusion between StockRequisitionCost and StockRequisition.  The first is a query and the second a table.

Jim


On Monday, 6 February 2017 10:55:48 UTC, Mike P  wrote:
> On Sunday, February 5, 2017 at 7:05:30 PM UTC, internet...@foobox.com wrote:
> > I have a local table TasksLocal that contains a relevant field TaskID 
> > 
> > I am wishing to insert into another local table some other values for just those with the same TaskID. 
> > 
> > I have the query: 
> > 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> > & " SELECT StockRequisitionCost.* " _ 
> > & " FROM StockRequisitionCost INNER JOIN TasksLocal ON StockRequisitionCost.TaskID =TasksLocal.TaskID" 
> > 
> > Now this works correctly but is very slow. 
> > 
> > StockRequistionCost is another query that sums certain values 
> > 
> > SELECT StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID, Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
> > FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
> > GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID; 
> > 
> > 
> > What I think is happening is that the full StockRequistionCost is being executed and then the selection for the relevant Tasks is made.  Since many items are being summed, this is taking a long time.  There are over 650,00 records being summed to produce 17,500 totals (i.e. Tasks)
> > 
> > I want StockRequistionCost to be executed only for those relevant Tasks. 
> > 
> > I have tried to rewrite my query as 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> >                         & " Select StockRequisitionCost.*" _ 
> >                         & " From StockRequisitionCost" _ 
> >                         & " Where Exists (Select TaskID From TasksLocal Where TaskID = StockRequisitionCost.TaskID)" 
> > 
> > However this does not speed things up. 
> > 
> > How do I speed up the query?  I want the summations in the StockRequistionCost query to be calculated only for those TaskID in the table TasksLocal
> 
> Hi,  
> 
> The selection by TaskID is performed in the top level query. So doesn't that indicate that the GROUP BY clause in the query StockRequistionCost is performed before the TaskID selection, as you suspect?
> It is not apparent that there is any selection on TasksLocal.TaskID in the query StockRequistionCost.
> It would help if you could show the query, StockRequisitionItemsQuery .
> 
> Is there some confusion between StockRequisitionCost and StockRequisition?
> 
> Mike P.
> 
> 6/2/17

[toc] | [prev] | [next] | [standalone]

#12360

If it works faster with a set TaskID, try adding Parameters to the queries and calling with each value. You could then combine them into 1 query.

PARAMETERS lTaskID Long;
INSERT INTO StockRequisitionCostLocal
 SELECT StockRequisitionCost.* 
 FROM StockRequisitionCost;



PARAMETERS lTaskID Long;
SELECT 
StockRequisitions.TaskID, 
StockRequisitionItemsQuery.StockRequisitionID, 
Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, 
Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID
HAVING StockRequisitions.TaskID=[lTaskID];  

[toc] | [prev] | [next] | [standalone]

#12362

You are right in that the summations and Group By are performed before the filtering of TaskIDs.  Except that is when I select a single TaskID.

This query is only part of a larger one that contains other components similar to this but on other tables.  It is very slow and has become increasingly so over the years as more and more data has been entered into the system.

I have very limited knowledge of SQL Server but I remember that it could display the sequence of steps that a particular piece of SQL would take.  And I think that you could sometimes alter this.

I have been trying to deduce the sequence that Access takes when running some SQL.  As you note it appears, in my case, to do the summing and then the selection except then selecting only one.

I was hoping, by trying a couple of approaches, I could find a form of SQL that would make the selection of TaskIDs and then only do the summations for those.

What I have done is to use local tables (not something I prefer to do) and build the end result in a number of steps.  I am pleased to say that this is working and has reduced the delay from a frustrating 80 seconds to about 8 seconds.

Thanks everyone for the suggestions

Jim


On Monday, 6 February 2017 10:55:48 UTC, Mike P  wrote:
> On Sunday, February 5, 2017 at 7:05:30 PM UTC, internet...@foobox.com wrote:
> > I have a local table TasksLocal that contains a relevant field TaskID 
> > 
> > I am wishing to insert into another local table some other values for just those with the same TaskID. 
> > 
> > I have the query: 
> > 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> > & " SELECT StockRequisitionCost.* " _ 
> > & " FROM StockRequisitionCost INNER JOIN TasksLocal ON StockRequisitionCost.TaskID =TasksLocal.TaskID" 
> > 
> > Now this works correctly but is very slow. 
> > 
> > StockRequistionCost is another query that sums certain values 
> > 
> > SELECT StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID, Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) AS TotalCostFromStock 
> > FROM StockRequisitionItemsQuery INNER JOIN StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = StockRequisitions.StockRequisitionID 
> > GROUP BY StockRequisitions.TaskID, StockRequisitionItemsQuery.StockRequisitionID; 
> > 
> > 
> > What I think is happening is that the full StockRequistionCost is being executed and then the selection for the relevant Tasks is made.  Since many items are being summed, this is taking a long time.  There are over 650,00 records being summed to produce 17,500 totals (i.e. Tasks)
> > 
> > I want StockRequistionCost to be executed only for those relevant Tasks. 
> > 
> > I have tried to rewrite my query as 
> > "INSERT INTO StockRequisitionCostLocal" _ 
> >                         & " Select StockRequisitionCost.*" _ 
> >                         & " From StockRequisitionCost" _ 
> >                         & " Where Exists (Select TaskID From TasksLocal Where TaskID = StockRequisitionCost.TaskID)" 
> > 
> > However this does not speed things up. 
> > 
> > How do I speed up the query?  I want the summations in the StockRequistionCost query to be calculated only for those TaskID in the table TasksLocal
> 
> Hi,  
> 
> The selection by TaskID is performed in the top level query. So doesn't that indicate that the GROUP BY clause in the query StockRequistionCost is performed before the TaskID selection, as you suspect?
> It is not apparent that there is any selection on TasksLocal.TaskID in the query StockRequistionCost.
> It would help if you could show the query, StockRequisitionItemsQuery .
> 
> Is there some confusion between StockRequisitionCost and StockRequisition?
> 
> Mike P.
> 
> 6/2/17

[toc] | [prev] | [next] | [standalone]

#12363

on 2/7/2017, internet.shopping@foobox.com supposed :
> You are right in that the summations and Group By are performed before the 
> filtering of TaskIDs.  Except that is when I select a single TaskID.
>
> This query is only part of a larger one that contains other components 
> similar to this but on other tables.  It is very slow and has become 
> increasingly so over the years as more and more data has been entered into 
> the system.
>
> I have very limited knowledge of SQL Server but I remember that it could 
> display the sequence of steps that a particular piece of SQL would take.  And 
> I think that you could sometimes alter this.
>
> I have been trying to deduce the sequence that Access takes when running some 
> SQL.  As you note it appears, in my case, to do the summing and then the 
> selection except then selecting only one.
>
> I was hoping, by trying a couple of approaches, I could find a form of SQL 
> that would make the selection of TaskIDs and then only do the summations for 
> those.
>
> What I have done is to use local tables (not something I prefer to do) and 
> build the end result in a number of steps.  I am pleased to say that this is 
> working and has reduced the delay from a frustrating 80 seconds to about 8 
> seconds.
>
> Thanks everyone for the suggestions
>
> Jim
>
>
> On Monday, 6 February 2017 10:55:48 UTC, Mike P  wrote:
>> On Sunday, February 5, 2017 at 7:05:30 PM UTC, internet...@foobox.com wrote:
>>> I have a local table TasksLocal that contains a relevant field TaskID 
>>> 
>>> I am wishing to insert into another local table some other values for just 
>>> those with the same TaskID. 
>>> 
>>> I have the query: 
>>> 
>>> "INSERT INTO StockRequisitionCostLocal" _ 
>>> & " SELECT StockRequisitionCost.* " _ 
>>> & " FROM StockRequisitionCost INNER JOIN TasksLocal ON 
>>> StockRequisitionCost.TaskID =TasksLocal.TaskID" 
>>> 
>>> Now this works correctly but is very slow. 
>>> 
>>> StockRequistionCost is another query that sums certain values 
>>> 
>>> SELECT StockRequisitions.TaskID, 
>>> StockRequisitionItemsQuery.StockRequisitionID, 
>>> Sum(StockRequisitionItemsQuery.TotalCost) AS SumOfTotalCost, 
>>> Sum(IIf(IsNull([StockRequisitionItemsQuery]![DateFromStock]),0,IIf([StockRequisitionItemsQuery]![DateFromStock]<=Date(),[StockRequisitionItemsQuery]![TotalCost],0))) 
>>> AS TotalCostFromStock  FROM StockRequisitionItemsQuery INNER JOIN 
>>> StockRequisitions ON StockRequisitionItemsQuery.StockRequisitionID = 
>>> StockRequisitions.StockRequisitionID  GROUP BY StockRequisitions.TaskID, 
>>> StockRequisitionItemsQuery.StockRequisitionID; 
>>> 
>>> 
>>> What I think is happening is that the full StockRequistionCost is being 
>>> executed and then the selection for the relevant Tasks is made.  Since many 
>>> items are being summed, this is taking a long time.  There are over 650,00 
>>> records being summed to produce 17,500 totals (i.e. Tasks)
>>> 
>>> I want StockRequistionCost to be executed only for those relevant Tasks. 
>>> 
>>> I have tried to rewrite my query as 
>>> "INSERT INTO StockRequisitionCostLocal" _ 
>>>                         & " Select StockRequisitionCost.*" _ 
>>>                         & " From StockRequisitionCost" _ 
>>>                         & " Where Exists (Select TaskID From TasksLocal 
>>> Where TaskID = StockRequisitionCost.TaskID)" 
>>> 
>>> However this does not speed things up. 
>>> 
>>> How do I speed up the query?  I want the summations in the 
>>> StockRequistionCost query to be calculated only for those TaskID in the 
>>> table TasksLocal
>> 
>> Hi,  
>> 
>> The selection by TaskID is performed in the top level query. So doesn't that 
>> indicate that the GROUP BY clause in the query StockRequistionCost is 
>> performed before the TaskID selection, as you suspect? It is not apparent 
>> that there is any selection on TasksLocal.TaskID in the query 
>> StockRequistionCost. It would help if you could show the query, 
>> StockRequisitionItemsQuery .
>> 
>> Is there some confusion between StockRequisitionCost and StockRequisition?
>> 
>> Mike P.
>> 
>> 6/2/17

You may be able to get to where you want to be by executing your 
Summation query as a Subquery.  Consider:

Select this, that, SomeOtherthing
From MyLocalTable inner join
   (Select ID, Sum(this) as this, Sum(that)...
   From MyOtherTable
   Where ThisCondition and ThatCondition
   Group By whatever
   ) as  drvSummations
On MyLocalTable.ID = drvSummations.ID

A construct like this will limit the summation query to the subset of 
records that you need, which should reduce the amount of time that the 
query takes.

The big upside here is that the whole thing is self contained in one 
sql statement, requiring no temp tables or intermediate queries.

The possible downside is that Access will not be able to cache a query 
execution plan (if it even does this to start with) forcing Access to 
Parse and create a new execution plan each time the query is run.  in 
my experience this does not have a major impact on on the execution 
time.

Rdub

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web