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

Groups > comp.databases.ms-access > #12363

Re: Need to improve speed of some SQL

Show all headers | View raw

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

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

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

csiph-web