Speed up this Query

Arvidsson

Registered User.
Local time
Today, 10:27
Joined
Jun 12, 2014
Messages
54
Hello,

I have this query:

Code:
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
 
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:
Code:
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
 
Thank you very much!

If I test ...
Code:
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:
Code:
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).
 
If you are sure its always there...
Code:
Select *
from  tbldata as a
join   tbldata as b on a.company = b.company and a.maxpos - 365 = b.posnr
 
With this code, I get an "syntax error", too. :confused:

Do I have to add something to run this code as a query?
 
I forget that Access requires the INNER prefix i think...

Code:
Select *
from  tbldata as a
[U]INNER[/U] join   tbldata as b on a.company = b.company and a.maxpos - 365 = b.posnr
 
If I change
Code:
a.maxpos - 365 = b.posnr
in
Code:
a.posnr-365=b.posnr
than it works fine!

Thank you! :)

I would like to test your second code, too.
Code:
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!
 
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:

Code:
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.

Code:
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.

Code:
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.

Code:
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);
 

Users who are viewing this thread

Back
Top Bottom