Optimize a query with multiple joins on a single table

So your query basically boils down to this (I think):

SELECT
    p1.AR1ID AS Article,
    p1.DAT AS Lot,
    ROUND((SUM(p1.VAL))/(COUNT(p1.VAL)),2) AS [Weight],
    ROUND((SUM(p2.VAL))/(COUNT(p2.VAL)),2) AS [Width],
    ROUND((SUM(p3.VAL))/(COUNT(p3.VAL)),2) AS [Length],
    ROUND((SUM(p4.VAL))/(COUNT(p4.VAL)),2) AS [Thickness]
FROM
    PesoMedioLotto p1
    INNER JOIN PesoMedioLotto p2 ON p2.AR1ID = p1.AR1ID AND p2.DAT = p1.DAT AND p2.DES1 = 'Width'
    INNER JOIN PesoMedioLotto p3 ON p3.AR1ID = p1.AR1ID AND p3.DAT = p1.DAT AND p3.DES1 = 'Length'
    INNER JOIN PesoMedioLotto p4 ON p4.AR1ID = p1.AR1ID AND p4.DAT = p1.DAT AND p4.DES1 = 'Thickness'
WHERE 
    p1.DES1 = 'Weight'
GROUP BY
    p1.AR1ID,
    p1.DAT;

I would be interested to see what happens when you run this:

SELECT DISTINCT
    p1.AR1ID AS Article,
    p1.DAT AS Lot
FROM
    PesoMedioLotto p1
    INNER JOIN PesoMedioLotto p2 ON p2.AR1ID = p1.AR1ID AND p2.DAT = p1.DAT AND p2.DES1 = 'Width'
    INNER JOIN PesoMedioLotto p3 ON p3.AR1ID = p1.AR1ID AND p3.DAT = p1.DAT AND p3.DES1 = 'Length'
    INNER JOIN PesoMedioLotto p4 ON p4.AR1ID = p1.AR1ID AND p4.DAT = p1.DAT AND p4.DES1 = 'Thickness'
WHERE 
    p1.DES1 = 'Weight';

If that doesn't run then could you comment out the JOIN to p4, then the JOIN to p3, etc. Or you could try removing the DISTINCT, to see if you get a partial results set even if the query never completes in a timely manner.

My assumption is that you have a lot more data for lengths and/ or thicknesses in your database than you do for weights or widths?

This might also be useful to know:

SELECT DES1, COUNT(*) AS freq FROM PesoMedioLotto GROUP BY DES1;

Then my next questions would be:

• why is all this data in one table, when it might make more sense to have different tables for different metrics?
• does the data hold rows for other DES1 values, and if so how many?
• are there any orphans, e.g. a thickness for a AR1ID/ DAT that doesn't also have a weight?
• do you have good indexes on this table?

If the table is configured in a way that makes it impossible to query, then the best option might be to run your query into parts:

• load only the data you need into a temporary table;
• apply indexes to the temporary table;
• run your query from the temporary table.

Maybe something like this:

SELECT AR1ID, DAT, DES1, VAL INTO #temp FROM PesoMedioLotto WHERE DES1 IN ('Weight', 'Width', 'Length', 'Thickness');
CREATE INDEX ix$lotto1 ON #temp (DES1);
CREATE INDEX ix$lotto2 ON #temp (AR1ID, DAT);

SELECT
    p1.AR1ID AS Article,
    p1.DAT AS Lot,
    ROUND((SUM(p1.VAL))/(COUNT(p1.VAL)),2) AS [Weight],
    ROUND((SUM(p2.VAL))/(COUNT(p2.VAL)),2) AS [Width],
    ROUND((SUM(p3.VAL))/(COUNT(p3.VAL)),2) AS [Length],
    ROUND((SUM(p4.VAL))/(COUNT(p4.VAL)),2) AS [Thickness]
FROM
    #temp p1
    INNER JOIN #temp p2 ON p2.AR1ID = p1.AR1ID AND p2.DAT = p1.DAT AND p2.DES1 = 'Width'
    INNER JOIN #temp p3 ON p3.AR1ID = p1.AR1ID AND p3.DAT = p1.DAT AND p3.DES1 = 'Length'
    INNER JOIN #temp p4 ON p4.AR1ID = p1.AR1ID AND p4.DAT = p1.DAT AND p4.DES1 = 'Thickness'
WHERE 
    p1.DES1 = 'Weight'
GROUP BY
    p1.AR1ID,
    p1.DAT;