| Author |
 Topic  |
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
 Posted - 10/09/2012 : 03:42:28
|
I have this simplified table CUSTTRANS:
VOUCHER INVOICE AMOUNT LASTSETTLEVOUCHER
SOI20003197 SOI20003197 805.00 EFC00001619
EFC00001619 -805.00 SOI20003197
EFC00001619 805.00 RC_00001619
RC_00001619 -805.00 EFC00001619
All these records together calculate the balance for Invoice SOI20003197.
The algorithm that picks these record works like this:
(1) Get the record where INVOICE is SOI20003197 (in this case)
(2) Get all records where this value appears in VOUCHER and LASTSETTLEVOUCHER
(3) Get all records where the value returned in (1) for LASTSETTLEVOUCHER has matches in VOUCHER, INVOICE and LASTSETTLEVOUCHER
What I do is:select * from CUSTTRANS
where INVOICE ='SOI20003197' OR
VOUCHER ='SOI20003197' OR
LASTSETTLEVOUCHER ='SOI20003197' OR
INVOICE =(SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197') OR
VOUCHER =(SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197') OR
LASTSETTLEVOUCHER =(SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197')
For some reason the query seems to enter in a loop; there is no record returned and I have to abort the query after 10 minutes.
This is only the first stage. In the second stage I have to look for any occurances of the green value in any of the Columns and so on, until any value of LASTSETTLEVOUCHER is crosschecked in the other columns.
Any suggestions.
Martin |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 07:02:17
|
I assume you have some kind of a while loop or cursor that is wrapped around the query you posted. If that is so, the infinite loop will happen because you are not eliminating the records that you have already visited.
If you don't have a loop/cursor, this may be a performance issue - you can test that theory by adding a TOP N clause to your outer select:SELECT TOP 1 * FROM
.... |
 |
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
Posted - 10/09/2012 : 07:27:09
|
Hmmm... I don't have any loop/cursor implemented. The initial query had returned the correct results after 15 minutes!
I now added the TOP 4 expression for the expected 4 records and got the result after 1:30 min!! The query is not that complicated in order to justify such a bad performance, isn't it?
So weird
Martin |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 07:37:54
|
If you have only a few (hundred or thousand) rows in the table, it should not take that long. But if you have millions of rows, and if you don't have proper indexes (index on INVOICE and perhpas VOUCHER, LASTSETTLEVOUCHER) or if you have a large number of rows with the same invoice number, it can take a while. Also, look at the query plan to see what parts of the query are taking up the time.
The reason I was asking about loop/cursor is because I assumed you would want to repeat the process to pick up rows recursively. If that is not a requirement, then no need, of course.
I feel like the query can be rewritten to make it more efficient, but I don't know enough of the requirement to suggest how. See if this comes back quickly:select TOP 1 * from CUSTTRANS
where INVOICE ='SOI20003197' OR
VOUCHER ='SOI20003197' OR
LASTSETTLEVOUCHER ='SOI20003197' OR
INVOICE =(SELECT TOP 1 LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197') OR
VOUCHER =(SELECT TOP 1 LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197') OR
LASTSETTLEVOUCHER =(SELECT TOP 1 LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197') |
Edited by - sunitabeck on 10/09/2012 07:38:20 |
|
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
Posted - 10/09/2012 : 08:09:12
|
there is a total of 25'000 records in that table, and what makes it so damn slow are the subqueries in the where clause.
The query with TOP 1 takes as much as with TOP 4, 1:30 minutes; and yes I have this feeling too that there must be a simpler way to write that query... I even have this feeling that just describing makes it more complicated as it actually is.
Imagine 3 columns. I look for a certain ID that can appear in any of the 3 columns; whenever there are records returned with a value ID* for LASTSETTLEVOUCHE different than ID have, I have to include this in my query, until no new ID shows up. Not more than 3-4 iterations.
What I manualy do is:select * from CUSTTRANS
where
(CUSTTRANS.INVOICE ='SOI20003197' OR CUSTTRANS.VOUCHER ='SOI20003197' OR CUSTTRANS.LASTSETTLEVOUCHER ='SOI20003197'
OR INVOICE= 'EFC00001619' OR CUSTTRANS.VOUCHER ='EFC00001619' OR CUSTTRANS.LASTSETTLEVOUCHER ='EFC00001619'
OR INVOICE= 'RC_00001619' OR CUSTTRANS.VOUCHER ='RC_00001619' OR CUSTTRANS.LASTSETTLEVOUCHER ='RC_00001619'
OR INVOICE= 'LIQ00001543' OR CUSTTRANS.VOUCHER ='LIQ00001543' OR CUSTTRANS.LASTSETTLEVOUCHER ='LIQ00001543')
Each new line in the where clause is added after the one before had brought up a new ID-code.
Rather than an iteration I wanted to hook this up by:
self joins, but didn't succeed.
Martin |
|
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 10/09/2012 : 09:05:37
|
quote:
Originally posted by barnabeck
I have this simplified table CUSTTRANS:
VOUCHER INVOICE AMOUNT LASTSETTLEVOUCHER
SOI20003197 SOI20003197 805.00 EFC00001619
EFC00001619 -805.00 SOI20003197
EFC00001619 805.00 RC_00001619
RC_00001619 -805.00 EFC00001619
All these records together calculate the balance for Invoice SOI20003197.
The algorithm that picks these record works like this:
(1) Get the record where INVOICE is SOI20003197 (in this case)
(2) Get all records where this value appears in VOUCHER and LASTSETTLEVOUCHER
(3) Get all records where the value returned in (1) for LASTSETTLEVOUCHER has matches in VOUCHER, INVOICE and LASTSETTLEVOUCHER
What I do is:select * from CUSTTRANS
where INVOICE ='SOI20003197' OR
VOUCHER ='SOI20003197' OR
LASTSETTLEVOUCHER ='SOI20003197' OR
INVOICE =(SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197') OR
VOUCHER =(SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197') OR
LASTSETTLEVOUCHER =(SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197')
For some reason the query seems to enter in a loop; there is no record returned and I have to abort the query after 10 minutes.
This is only the first stage. In the second stage I have to look for any occurances of the green value in any of the Columns and so on, until any value of LASTSETTLEVOUCHER is crosschecked in the other columns.
Any suggestions.
Martin
The red part causes the loop, and you should delete it.
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
|
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
Posted - 10/09/2012 : 09:11:48
|
SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197' returns a scalar and therefore shouldn't cause a problem
Martin |
|
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 10/09/2012 : 09:29:10
|
quote:
Originally posted by barnabeck
SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197' returns a scalar and therefore shouldn't cause a problem
Martin
It will not cause problem fro one execution. But as I understood your algorithm. you use the previous stage output in the next stage. so this condition will always be true, and the loop will not end.
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
|
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 10/09/2012 : 10:18:20
|
Try something like this:
IF OBJECT_ID(N'tempdb..#IDs', N'U') IS NOT NULL
DROP TABLE #IDs;
SELECT 'SOI20003197' AS ID
INTO #IDs;
DECLARE @count INT = @@ROWCOUNT;
WHILE @count > 0
BEGIN
INSERT INTO #IDs(ID)
SELECT C.LASTSETTLEVOUCHER
FROM #IDs AS P
INNER JOIN
CUSTTRANS AS C
ON C.INVOICE = P.ID
OR C.VOUCHER = P.ID
WHERE NOT EXISTS(SELECT *
FROM #IDs AS I
WHERE I.ID = C.LASTSETTLEVOUCHER);
SET @count = @@ROWCOUNT;
END
SELECT C.VOUCHER, C.INVOICE, C.AMOUNT, C.LASTSETTLEVOUCHER
FROM #IDs AS I
INNER JOIN
CUSTTRANS AS C
ON C.INVOICE = I.ID
OR VOUCHER = I.ID;
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
|
|
| |
Topic |
|
help with this query required
