Speed up this Query (1 Viewer)

[Arvidsson]

Hello,

I have this query:

SELECT A.PosNr, A.PriceDate, A.Company, A.Price, (SELECT TOP 1
          B.Price
      FROM
          tblData As B
      WHERE
          B.Company= A.Company
          AND
          B.PosNr < ( A.PosNr - 364 )
      ORDER BY
          B.PosNr DESC                   ) AS PreisYearBefore
FROM tblData AS A;

If I start this query with tblData (220,000 data points), it is calulcated in at least 7 hours.

Does someone have an idea how I can speed up this query?

PS: I set an index in tblData to PosNr (without duplicates), PriceDate (with duplicates) and company (with duplicates)

Thank you very much in advance!
Magnus

 

[namliam]

Quite obvious that this query has pretty shit performance, inline queries always are crap.

I find it kinda hard to believe that you only join on company and on a year ago... but yeah...
Does "a year ago" always exist? i.e. always -365 days or is it sometimes 340 or some other odd number

Assuming it isnt always exactly there... something like:

Select ....
from  tbldata as d
join   ( Select company, max(posnr) as MaxPos from tblData where posnr < 364) as x on  d.Company = x.company
join  tbldata as b on x.company = b.company and x.maxpos = b.posnr

That should do a lot better I think

 

[Arvidsson]

Thank you very much!

If I test ...

Select *
from  tbldata as d
join   ( Select company, max(posnr) as MaxPos from tblData where posnr < 364) as x on  d.Company = x.company
join  tbldata as b on x.company = b.company and x.maxpos = b.posnr

... I get "syntax error in FROM clause".

What I am doing wrong?

Because I search the "Price one year ago" with the PosNr (1-220,000), there is always a Price with the PosNr X-365.

Thank you very much for support!

PS: To clarify, this should be the result:

PosNr     PriceDate      Company        Price      PriceTwoDaysAgo     
1       01.01.2014       Firma A       5                                      
2       02.01.2014       Firma A       7                                   
3       03.01.2014       Firma A       9            5         
4       04.01.2014       Firma A       8            7         
5       06.01.2014       Firma A       6            9         
6       02.01.2014       Firma XY     11                                      
7       03.01.2014       Firma XY      9                                  
8       04.01.2014       Firma XY      7           11         
9       05.01.2014       Firma XY      8           9         
10      06.01.2014       Firma XY      10          7

This is an example with PosNr-2 (instead of 1 year, PosNr-365).

 

[namliam]

If you are sure its always there...

Select *
from  tbldata as a
join   tbldata as b on a.company = b.company and a.maxpos - 365 = b.posnr

 

[Arvidsson]

With this code, I get an "syntax error", too.

Do I have to add something to run this code as a query?

 

[namliam]

I forget that Access requires the INNER prefix i think...

Select *
from  tbldata as a
[U]INNER[/U] join   tbldata as b on a.company = b.company and a.maxpos - 365 = b.posnr

 

[Arvidsson]

If I change

a.maxpos - 365 = b.posnr

in

a.posnr-365=b.posnr

than it works fine!

Thank you!

I would like to test your second code, too.

Select *
from  tbldata as d
INNER join   ( Select company, max(posnr) as MaxPos from tblData where posnr < 364) as x on  d.Company = x.company
INNER join  tbldata as b on x.company = b.company and x.maxpos = b.posnr

But there appears: "Syntax error, missing operator". Do you know why?

Thank you very much in advance!

 

[namliam]

than it works fine!

:banghead: the quirks of access :banghead:

I would like to test your second code, too.

1) not exactly sure whats wrong here
2) The querie I believe is flawed (for your logic) anyways
3) the 'new' query is better / faster anyways

 

[Arvidsson]

Ok, thank you very much namliam!

You seem to be an expert in sql. Maybe you have a solution for a other problem.

I have a price table:

tblPrice
PosNr  PriceDate          Company  Price
1       01.01.2014       Firma A       5                                      
2       02.01.2014       Firma A       7                                   
3       03.01.2014       Firma A       9            
4       04.01.2014       Firma A       8            
5       06.01.2014       Firma A       6            
6       02.01.2014       Firma XY     11                                      
7       03.01.2014       Firma XY      9                                  
8       04.01.2014       Firma XY      7           
9       05.01.2014       Firma XY      8          
10      06.01.2014       Firma XY      10

And I have a table with the data, for which dates I need a price.

tblDates
PosNr  PriceDate          Company    
1       01.01.2014       Firma A                                             
2       02.01.2014       Firma A                                          
3       03.01.2014       Firma A                   
4       04.01.2014       Firma A
5       05.01.2014       Firma A (new)           
6       06.01.2014       Firma A                   
7       02.01.2014       Firma XY                                           
8       03.01.2014       Firma XY                                        
9       04.01.2014       Firma XY                 
10      05.01.2014       Firma XY                
11      06.01.2014       Firma XY

And now I want to combine this tables, and for the date in which I have no price, the last price should be taken.

tblResult    
PosNr  PriceDate          Company  Price
1       01.01.2014       Firma A       5                                      
2       02.01.2014       Firma A       7                                   
3       03.01.2014       Firma A       9            
4       04.01.2014       Firma A       8 
5       05.01.2014       Firma A       8 (new)
6       06.01.2014       Firma A       6            
7       02.01.2014       Firma XY     11                                      
8       03.01.2014       Firma XY      9                                  
9       04.01.2014       Firma XY      7           
10      05.01.2014       Firma XY      8          
11      06.01.2014       Firma XY      10

Do you habe any idea how I can get this?

I have this code, but it need hours, too.

SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1
          B.Price
      FROM
          tblPrices As B
      WHERE
          B.Company = tblDates.Company
          AND
          B.PriceDate <= tblDates.PriceDate
      ORDER BY
          B.PriceDates  DESC                   ) AS Price
FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);