Subquery performance issue

[bau]

Hi Everybody,

I am working on a rather large table (>1,000,000 rows) containing
houshold level panel data and one of the subqueries is really slow:,

<code>

Select a.CustomerID, a.Brand,

(Select d.PurchaseCounter

From Customertable AS b

Where a.CustID=b.CustID AND a.Brand=b.Brand AND
b.PurchaseCounter=((a.PurchaseCounter)-1)
) AS D1

(Select d.PurchaseCounter

From Customertable AS b

Where a.CustID=b.CustID AND a.Brand=b.Brand AND
b.PurchaseCounter=((a.PurchaseCounter)-2)
) AS D2

FROM Customertable AS a

</code>

What this is supposed to be doing is finding out if the customer chose
the same brand on the last purchase occasion. In the real table, this
is done for the last 30 purchases. Everything works fine except of the
performance. I indexed everything and set all the data types to their
minimum. This whole project will yield one static table - however, the
way it is now i figured it would take about 50 hours to get this
table.

Is there any other way to do this or do you have any hints on how to
improve perfomance? I thought maybe an iff-statement would be better
here, but I cant get to work.

Thanks for your time!

Ray

 

[Douglas J. Steele]

Try doing a self-join.

I'd try and rewrite your query for you, but what you posted is invalid SQL.
You refer to d.PurchaseCOunter twice, but no table or subquery is labelled
as d. I suspect you need something like:

SELECT a.CustomerID, a.Brand
FROM (Customertable AS a INNER JOIN Customertable AS b
ON (b.Brand = a.Brand) AND (b.CustomerID = a.CustomerID) AND
(b.PurchaseCounter = a.PurchaseCounter - 1))
INNER JOIN Customertable AS c
ON (a.Brand = c.Brand) AND (a.CustomerID = c.CustomerID) AND
(c.PurchaseCounter = a.PurchaseCounter - 2)

 

[bau]

Thank you, Doug!

A join seems to be about 20% faster than the subquery.

BTW: I meant to write a.PurchaseCounter, of course - sorry for that!