help with this query required - 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

General SQL Server Forums

New to SQL Server Programming

help with this query required

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-10-09 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 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

236 Posts

Posted - 2012-10-09 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 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')

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-10-09 : 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

264 Posts

Posted - 2012-10-09 : 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

236 Posts

Posted - 2012-10-09 : 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

264 Posts

Posted - 2012-10-09 : 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

264 Posts

Posted - 2012-10-09 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources