date comparison

Hi there!
I'm having some problems with a query considering it's performance. I
want to make a view of saleslines (table 1) joined with a purchline
(table 2). The purchline should show the last purchamount of an item,
having the latest date before the salesdate.
When running the query I can go get a cup of coffee on the other side
of the world... Any suggestions how to make this query run faster?
At this moment I have made something like this:
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.purchLINE.CREATEDDATE AS datumInkoop,
FROM
dbo.PURCHLINE INNER JOIN
dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
WHERE
dbo.PURCHLINE.RECID =
(SELECT
MAX(dbo.purchline.recid)
FROM
dbo.purchline WITH (nolock)
WHERE
(dbo.PURCHLINE.QTYORDERED <> 0) AND
dbo.purchline.createddate <= dbo.salesline.createddate AND
dbo.purchline.itemid = dbo.salesline.itemid)Can you POST DDL and insert scripts for sample data.
"Q" wrote:

> Hi there!
> I'm having some problems with a query considering it's performance. I
> want to make a view of saleslines (table 1) joined with a purchline
> (table 2). The purchline should show the last purchamount of an item,
> having the latest date before the salesdate.
> When running the query I can go get a cup of coffee on the other side
> of the world... Any suggestions how to make this query run faster?
> At this moment I have made something like this:
> SELECT
> dbo.SALESLINE.SALESID,
> dbo.SALESLINE.ITEMID,
> dbo.SALESLINE.QTYORDERED,
> dbo.SALESLINE.LINEAMOUNT,
> dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
> dbo.purchLINE.CREATEDDATE AS datumInkoop,
> FROM
> dbo.PURCHLINE INNER JOIN
> dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
> WHERE
> dbo.PURCHLINE.RECID =
> (SELECT
> MAX(dbo.purchline.recid)
> FROM
> dbo.purchline WITH (nolock)
> WHERE
> (dbo.PURCHLINE.QTYORDERED <> 0) AND
> dbo.purchline.createddate <= dbo.salesline.createddate AND
> dbo.purchline.itemid = dbo.salesline.itemid)
>|||I did not create the tables myself. You probably want to know something
about datatypes and indexes I suppose...
salesid => varchar 20
itemid => varchar 30
qtyordered => numeric 13(28,12)
lineamount => numeric 13(28,12)
createddate => datetime 8
The length of the datatypes are mostly larger as needed, but are not
allowed to be changed.
All used columns is the purchline are indexed, and the columns salesid,
itemid are indexed.|||Can you try this... Not sure how much improvement this will give !!.
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.ph.CREATEDDATE AS datumInkoop,
FROM
dbo.SALESLINE
INNER JOIN
(
select
sl.salesid ,MAX(pl.recid) as LatestPurchLineId
from
salesline sl
inner join purchline pl on
sl.itemid = pl.itemid and
sl.createddate > pl.createdon and
pl.qtyordered <> 0
group by
sl.SalesId
) plat ON
dbo.salesline.salesid = plat.salesid
INNER JOIN PurchLine pl ON
pl.recid = plat.LatestPurchLineId
- Sha Anand
"Q" wrote:

> I did not create the tables myself. You probably want to know something
> about datatypes and indexes I suppose...
> salesid => varchar 20
> itemid => varchar 30
> qtyordered => numeric 13(28,12)
> lineamount => numeric 13(28,12)
> createddate => datetime 8
> The length of the datatypes are mostly larger as needed, but are not
> allowed to be changed.
> All used columns is the purchline are indexed, and the columns salesid,
> itemid are indexed.
>