date comparison

29 Jun 2006 12:33 PM

Q

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)

29 Jun 2006 1:20 PM

Sha Anand

Can you POST DDL and insert scripts for sample data.

Show quote

"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)
>
>

29 Jun 2006 1:38 PM

Q

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.

29 Jun 2006 3:08 PM

Sha Anand

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

Show quote

"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.
>
>