date comparison in subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

date comparison in subquery

Author

Topic

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-29 : 09:11:47

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)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 09:36:27

I would try this code

SELECT TOP 1 WITH TIES	sl.SALESID,
			sl.ITEMID,
			sl.QTYORDERED,
			sl.LINEAMOUNT,
			sl.CREATEDDATE datumVerkoop,
			pl.CREATEDDATE datumInkoop
FROM			dbo.PURCHLINE pl
INNER JOIN		dbo.SALESLINE sl ON pl.ITEMID = sl.ITEMID
WHERE			pl.QTYORDERED > 0  -- Can QtyOrdered be less than Zero?
			AND pl.CREATEDDATE <= sl.CREATEDDATE
ORDER BY		pl.RECID DESC

for not having a subquery executed for every row in the table.
WITH TIES is not needed if RECID is unique. If so, just drop WITH TIES and write

SELECT TOP 1	sl.SALESID,
		sl.ITEMID,
		sl.QTYORDERED,
		sl.LINEAMOUNT,
		sl.CREATEDDATE datumVerkoop,
		pl.CREATEDDATE datumInkoop
FROM		dbo.PURCHLINE pl
INNER JOIN	dbo.SALESLINE sl ON pl.ITEMID = sl.ITEMID
WHERE		pl.QTYORDERED > 0  -- Can QtyOrdered be less than Zero?
		AND pl.CREATEDDATE <= sl.CREATEDDATE
ORDER BY	pl.RECID DESC

instead. How long time did query take before and how long time now?

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-29 : 10:03:24

RECID is a unique value indeed and
qtyOrdered can be 0 or negative (when returning an item).

TOP 1 is not what I'm looking for. I'm still looking what your code does, so hopefully it's helpfull.

I need to calculate the marge(??? Is this Englisch) between sales and purches. The period is defined later on (using a cube) so I need to use all saleslines, combined with it's last purch.

Top 1 takes about 4 minutes using your suggestion...

Thanx a lot so far!!!

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-29 : 10:23:25

After testing Peso's code I still have no solution.
I have removed the 'top 1' and made a run, which results in multiple purchdates per salesline. I only need the purchdate which was before the salesdate...

Any more suggestions anyone???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 10:33:30

What about this code?

SELECT		sl.SALESID,
		sl.ITEMID,
		sl.QTYORDERED,
		sl.LINEAMOUNT,
		sl.CREATEDDATE datumVerkoop,
		MAX(pl.CREATEDDATE) datumInkoop
FROM		dbo.PURCHLINE pl
INNER JOIN	dbo.SALESLINE sl ON pl.ITEMID = sl.ITEMID
WHERE		pl.QTYORDERED <> 0
		AND pl.CREATEDDATE <= sl.CREATEDDATE
GROUP BY	sl.SALESID,
		sl.ITEMID,
		sl.QTYORDERED,
		sl.LINEAMOUNT,
		sl.CREATEDDATE

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-29 : 10:58:33

This works quite nice! Performance still is not perfect, but this is probably caused by the amount of rows (salesline > 2.500.000 and purchline > 850.000).

But maybe I can fine tune it a bit...
==> is a comparison over a varchar datatype faster as over a datetime datatype???

Thanx a lot Peso!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 13:36:48

I think it depends on indexes and the use of fields. Calculations over fields when in where criteria often renders indexes useless.

How long did your original query take, and how long does it take now?

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-30 : 02:39:51

The code I made originally never ended... After 45 minutes I usually cancelled it, because it would be useless. Now it takes up to 15 to 20 minutes, which is acceptable.

So thanx a lot again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 02:44:35

Seems very slow anyway, even if it is better than original!

Do you have any indexes on the tables? Do you want to return all SALESLINE items, even if there is no corresponding PURCHLINE (which renders NULL as pl.CREATEDDATE)?

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-30 : 03:13:59

Okay... made a run again today. And again it took to long. After 25 minutes I cancelled the run and it made up to almost 1.500.000 rows. So probably another million rows should still be calculated...

If there isn't a match with the purchline, that salesline should not be displayed.
All the columns used from the purchline are indexed. All the columns used from the salesline are indexed except qtyOrdered and createdDate.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 03:41:41

quote:
Originally posted by Q

All the columns used from the purchline are indexed. All the columns used from the salesline are indexed except qtyOrdered and createdDate.

That could be it. What does your execution plan tell you? Since SALESLINE.CREATEDDATE is not indexed, but still used in the query in the WHERE clause, can be the root cause of the slow performance.

This would perform better since it only does a GROUP BY for 1 field, not 5 as before. And since RECID is unique in SALESLINE, there is no performance gain either (Granularity is too low).

SELECT		sl.SALESID,
		sl.ITEMID,
		sl.QTYORDERED,
		sl.LINEAMOUNT,
		sl.CREATEDDATE datumVerkoop,
		z.CDT datumInkoop
FROM		dbo.SALESLINE sl
INNER JOIN	(
			SELECT		y.ITEMID IID,
					MAX(y.CREATEDDATE) CDT
			FROM		dbo.PURCHLINE y
			INNER JOIN	dbo.SALESLINE x ON x.ITEMID = y.ITEMID
			WHERE		y.CREATEDDATE <= x.CREATEDDATE
			GROUP BY	y.ITEMID
		) z ON z.IID = sl.ITEMID

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-30 : 04:04:29

It's running...

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-30 : 04:35:13

Killed the process after almost 30 minutes. The result is over 2.000.000 rows, but the data is incorrect. It displays also salesdates before the purchdate...

Amazing, quite a simple problem is generating so much problems...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 04:51:23

quote:
Originally posted by Q

Amazing, quite a simple problem is generating so much problems...

Yes, I missed the QTYORDERED <> 0 part before.

How long time does this query take?

SELECT		x.ITEMID IID,
		MAX(y.CREATEDDATE) CDT
FROM		dbo.PURCHLINE y
INNER JOIN	dbo.SALESLINE x ON x.ITEMID = y.ITEMID
WHERE		y.CREATEDDATE <= x.CREATEDDATE
		AND x.QTYORDERED <> 0
GROUP BY	x.ITEMID

This query only retrieve the latest CREATEDDATE from PURCHLINE for every ITEMID in SALESLINE where QTYORDERED is <> 0. If it this takes long time, it is due to lack of proper indexes. The query above could be written as an Indexed View for faster retrieval.

It is easy to swap places of the fields.

SELECT		sl.SALESID,
		sl.ITEMID,
		sl.QTYORDERED,
		sl.LINEAMOUNT,
		z.CDT datumInkoop,
		sl.CREATEDDATE datumVerkoop
FROM		dbo.SALESLINE sl
INNER JOIN	(
			SELECT		x.ITEMID IID,
					MAX(y.CREATEDDATE) CDT
			FROM		dbo.PURCHLINE y
			INNER JOIN	dbo.SALESLINE x ON x.ITEMID = y.ITEMID
			WHERE		y.CREATEDDATE <= x.CREATEDDATE
					AND x.QTYORDERED <> 0
			GROUP BY	x.ITEMID
		) z ON z.IID = sl.ITEMID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 05:12:29

quote:
Originally posted by Q

Killed the process after almost 30 minutes. The result is over 2.000.000 rows, but the data is incorrect.

I am amazed that the same query differs in time so much. From 15-20 minutes to 25 minutes and last over 30 minutes.

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-30 : 05:13:51

It seems other users aren't very happy with me executing these queries. CPU usage raises up to 90 percent, so I had to terminate the query.

It ran for 4 minutes and produced 2500 rows. Hopefully this is also usefull information, but I will try it again within a couple of minutes...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 05:16:50

quote:
Originally posted by Q

It ran for 4 minutes and produced 2500 rows. Hopefully this is also usefull information, but I will try it again within a couple of minutes...

Thank you for the information. Four minutes for 2,500 rows is very, very slow.

Users are often happy campers. If you succeed rewriting their query so it takes just some minutes later, compared to forever before, they will forgive you.

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-30 : 05:28:44

quote:

Originally posted by Peso

quote:
Originally posted by Q

Amazing, quite a simple problem is generating so much problems...

Yes, I missed the QTYORDERED <> 0 part before.


SELECT		sl.SALESID,
		sl.ITEMID,
		sl.QTYORDERED,
		sl.LINEAMOUNT,
		z.CDT datumInkoop,
		sl.CREATEDDATE datumVerkoop
FROM		dbo.SALESLINE sl
INNER JOIN	(
			SELECT		x.ITEMID IID,
					MAX(y.CREATEDDATE) CDT
			FROM		dbo.PURCHLINE y
			INNER JOIN	dbo.SALESLINE x ON x.ITEMID = y.ITEMID
			WHERE		y.CREATEDDATE <= x.CREATEDDATE
					AND x.QTYORDERED <> 0
			GROUP BY	x.ITEMID
		) z ON z.IID = sl.ITEMID

Peter Larsson
Helsingborg, Sweden

Okay, I also tried to run this part of code. But again, CPU raised to 90 percent, so I had to abort the run (almost 5 minutes). No result was displayed... and hopefully most users were able to continue their work

Off-Topic: Great quote about the happy campers!

Maybe you are interested in the meaning of the query...
Some persons want some information about the marge between sales and purches. The query I started this post with is simplified query. In real I want the prices (purch-, sales- and costprice) of all the lines. Within the cube a calculation is made to show the percentages...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 05:36:06

I figured that out, about the cube.
The key to success however in this query, is producing the purchdate prior to (or on) salesdate, related to ITEMID.

Peter Larsson
Helsingborg, Sweden

Q
Yak Posting Veteran

76 Posts

Posted - 2006-06-30 : 05:45:55

Do you mean something like selecting the purchline columns and match those with the salesline where itemid is equal, and the salesdate between the purchdate and the next purchdate?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 07:19:49

quote:
Originally posted by Q

Do you mean something like selecting the purchline columns and match those with the salesline where itemid is equal, and the salesdate between the purchdate and the next purchdate?

Yes, for every matching ITEMID in PURCHLINE and SALESLINE, the closest purchdate prior to salesdate should be fetched. That is what this query should do.

SELECT		x.ITEMID IID,
		MAX(y.CREATEDDATE) CDT
FROM		dbo.PURCHLINE y
INNER JOIN	dbo.SALESLINE x ON x.ITEMID = y.ITEMID
WHERE		y.CREATEDDATE <= x.CREATEDDATE
		AND x.QTYORDERED <> 0
GROUP BY	x.ITEMID

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources