SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help with this query required
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
190 Posts

Posted - 10/09/2012 :  03:42:28  Show Profile  Reply with Quote
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

5155 Posts

Posted - 10/09/2012 :  07:02:17  Show Profile  Reply with Quote
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 
....
Go to Top of Page

barnabeck
Posting Yak Master

Spain
190 Posts

Posted - 10/09/2012 :  07:27:09  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  07:37:54  Show Profile  Reply with Quote
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
Go to Top of Page

barnabeck
Posting Yak Master

Spain
190 Posts

Posted - 10/09/2012 :  08:09:12  Show Profile  Reply with Quote
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
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/09/2012 :  09:05:37  Show Profile  Reply with Quote
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
Go to Top of Page

barnabeck
Posting Yak Master

Spain
190 Posts

Posted - 10/09/2012 :  09:11:48  Show Profile  Reply with Quote
SELECT LASTSETTLEVOUCHER FROM CUSTTRANS WHERE INVOICE ='SOI20003197' returns a scalar and therefore shouldn't cause a problem

Martin
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/09/2012 :  09:29:10  Show Profile  Reply with Quote
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
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/09/2012 :  10:18:20  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000