SQL QUERY Running 6 hours

I think that main killers and the subqueries, I would rewrite it using joins if possible.

I had changed query bit.
hope it worked in sybase.

check out this link also..

http://www.sybase.be/files/White_Papers/ASE15-Optimizer-Best-Practices-v1-051209-wp.pdf


http://manuals.sybase.com/onlinebooks/group-as/asg1250e/ptallbk/@Generic__BookTextView/33584


it may helps

SELECT DISTINCT trade.trade_id, trade.version_num 
FROM  trade trade , product_desc product_desc  
WHERE product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp') 
AND trade.trade_status = 'VERIFIED' 
AND trade_id not in (select trade_id from bo_transfer where transfer_status != 'SETTLED' and trade_id !=0)
											
and trade_id not in (select case when netted_transfer_id != 0 then netted_transfer_id else case when trade_id != 0 then trade_id end end
					from bo_transfer 
					where transfer_id in (select transfer_id 
										  from bo_message 
										  where message_type in ('PAYMENTMSG','RECEIPTMSG') 
										  and message_status != 'ACKED') 
					) 
					
AND product_desc.product_id = trade.product_id  
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

Select allOpen in new window

Could assist with this perhaps because I not sure clued up with sql

I changed following two filter in one.


and trade_id not in (select case when netted_transfer_id != 0 then netted_transfer_id else case when trade_id != 0 then trade_id end end
                                        from bo_transfer
                                        where transfer_id in (select transfer_id
                                                                                  from bo_message
                                                                                  where message_type in ('PAYMENTMSG','RECEIPTMSG')
                                                                                  and message_status != 'ACKED')
                                        )

and trade_id not in (select netted_transfer_id from bo_transfer where transfer_id in (select transfer_id from bo_message where message_type in ('PAYMENTMSG','RECEIPTMSG') and message_status != 'ACKED') and netted_transfer_id != 0) 

and trade_id not in (select trade_id from bo_transfer where transfer_id in (select transfer_id from bo_message where message_type in ('PAYMENTMSG','RECEIPTMSG') and message_status != 'ACKED') and netted_transfer_id = 0)

Select allOpen in new window

I'm feeling JOIN might not be appropriate as query is with NOT IN Condition.

check one more pdf

http://download.sybase.com/pdfdocs/asg1250e/ptoptab.pdf

I run this query provided. But it has been running for 8 minutes now

SELECT DISTINCT trade.trade_id, trade.version_num
FROM  trade trade , product_desc product_desc  
WHERE product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp')
AND trade.trade_status = 'VERIFIED'
AND trade_id not in (select trade_id from bo_transfer where transfer_status != 'SETTLED' and trade_id !=0)
                                                                  
and trade_id not in (select case when netted_transfer_id != 0 then netted_transfer_id else case when trade_id != 0 then trade_id end end
                              from bo_transfer
                              where transfer_id in (select transfer_id
                                                              from bo_message
                                                              where message_type in ('PAYMENTMSG','RECEIPTMSG')
                                                              and message_status != 'ACKED')
                              )
                              
AND product_desc.product_id = trade.product_id  
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

why have you used so many no in for the same id

you can try

SELECT DISTINCT trade.trade_id, trade.version_num FROM  trade trade , product_desc product_desc  
WHERE product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp')
AND trade.trade_status = 'VERIFIED'
and trade_id not in (select netted_transfer_id from bo_transfer where transfer_id in (select transfer_id from bo_message where message_type in ('PAYMENTMSG','RECEIPTMSG') and message_status != 'ACKED') and netted_transfer_id != 0)
and trade_id not in (select trade_id from bo_transfer where transfer_status != 'SETTLED' and trade_id !=0 and netted_transfer_id = 0 and  transfer_id in (select transfer_id from bo_message where message_status != 'ACKED' and message_type in ('PAYMENTMSG','RECEIPTMSG')))
AND product_desc.product_id = trade.product_id  
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

As already mentioned in this thread, sub-queries is the main issue. You can try with JOIN as other experts suggestion.

In addition, I suggest you to make sure you have INDEXING enabled for those columns that are used in your query to compare.
For eg:- trade_id, netted_transfer_id, transfer_id

I am not an expert in Sybase Database. Just giving a clue based on my experience with SQL Server database.

Raj

Check whether Primary Key, Foreign Key, Indexing everything are perfect especially for those tables and fields in this query.

Raj

Raj,

Query is containing NOT IN, so is it suitable to use JOIN??

Will this one give any different?

SELECT DISTINCT trade.trade_id, trade.version_num 
FROM  trade trade , product_desc product_desc  
WHERE
    product_desc.product_id = trade.product_id  
AND product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp')
AND trade.trade_status = 'VERIFIED' 
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

AND trade_id not in (
	select trade_id
		from bo_transfer
		where transfer_status != 'SETTLED' and trade_id !=0
	UNION ALL
	SELECT CASE WHEN bo_transfer.netted_transfer_id != 0 THEN bo_transfer.netted_transfer_id ELSE bo_transfer.trade_id END
		FROM bo_transfer, bo_message
		WHERE bo_transfer.transfer_id = bo_message.transfer_id
			AND bo_message.message_type in ('PAYMENTMSG','RECEIPTMSG') 
			and bo_message.message_status != 'ACKED'
	)

Select allOpen in new window

Sory,

I forget to remove one condition.

SELECT DISTINCT trade.trade_id, trade.version_num 
FROM  trade trade , product_desc product_desc  
WHERE product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp') 
AND trade.trade_status = 'VERIFIED' 
and trade_id not in (select case when netted_transfer_id != 0 then netted_transfer_id else case when trade_id != 0 then trade_id end end
                                        from bo_transfer 
                                        where transfer_id in (select transfer_id 
                                                                                  from bo_message 
                                                                                  where message_type in ('PAYMENTMSG','RECEIPTMSG') 
                                                                                  and message_status != 'ACKED') 
                                        ) 
                                        
AND product_desc.product_id = trade.product_id  
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

Select allOpen in new window

Secondly,

Generally NOT IN slower then IN so i changed the query.
first verify the query and then check

SELECT DISTINCT trade.trade_id, trade.version_num 
FROM  trade trade , product_desc product_desc  
WHERE product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp') 
AND trade.trade_status = 'VERIFIED' 
and trade_id in (select case when netted_transfer_id != 0 then netted_transfer_id else case when trade_id != 0 then trade_id end end
                                        from bo_transfer 
                                        where transfer_id in (select transfer_id 
                                                                                  from bo_message 
                                                                                  where message_type not in ('PAYMENTMSG','RECEIPTMSG') 
                                                                                  and message_status = 'ACKED') 
                                        ) 
                                        
AND product_desc.product_id = trade.product_id  
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

Select allOpen in new window

Just notice one thing.

AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

Data containing from when ?

From query, it seems this data is somthning related to STOCK MARKET then there should be some start date condition.

like trade_date_time >= {ts '2010-04-01 00:00:00.0'}

well i tried this query. The results was 0

SELECT DISTINCT trade.trade_id, trade.version_num
FROM  trade trade , product_desc product_desc  
WHERE product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp')
AND trade.trade_status = 'VERIFIED'
and trade_id in (select case when netted_transfer_id != 0 then netted_transfer_id else case when trade_id != 0 then trade_id end end
                                        from bo_transfer
                                        where transfer_id in (select transfer_id
                                                                                  from bo_message
                                                                                  where message_type not in ('PAYMENTMSG','RECEIPTMSG')
                                                                                  and message_status = 'ACKED')
                                        )
                                       
AND product_desc.product_id = trade.product_id  
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

Brichsoft : is it intention to remove the condition of this part in new query?
trade_id not in (select trade_id from bo_transfer where transfer_status != 'SETTLED' and trade_id !=0)

abooysen2: How about my one?

no we need

trade_id not in (select trade_id from bo_transfer where transfer_status != 'SETTLED' and trade_id !=0)

How was this query performance?

SELECT DISTINCT trade.trade_id, trade.version_num
WHERE
AND product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp')
AND trade.trade_status = 'VERIFIED'
AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}

AND trade_id IN (select trade_id from bo_transfer where transfer_status = 'SETTLED' OR trade_id = 0)
AND trade_id NOT IN (
     )

>> Query is containing NOT IN, so is it suitable to use JOIN??

Yes. You can use LEFT JOIN / LEFT OUTER JOIN

SELECT * FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.ID = B.ID
WHERE B.ID IS NULL

This is like
SELECT * FROM TABLE1 WHERE ID NOT IN (SELECT ID FROM TABLE2)

Raj

it's still running for about 10 minutes now

can you provide index structure and also, how many records on your following table

bo_transfer
bo_message
trade
product_desc

JoeNuvo
That part i was missed,thanks.

Raj.
Thanks for info

I have a suggestion.

Note that in the query, there you used 3 sub-queries with again sub-query.
I used a temp table variable that will stored the contents of these sub-queries and it is called in the final query.

Is there any difference in its execution ?

Raj

-- TEMP TABLE 1
DECLARE @Transfer TABLE
(
	netted_transfer_id	INT,
	trade_id			INT
)

INSERT INTO @Transfer
	select netted_transfer_id, trade_id 
	from bo_transfer 
	where transfer_id in 
		(select transfer_id from bo_message where message_type in ('PAYMENTMSG','RECEIPTMSG') 
	and message_status != 'ACKED'

-- TEMP TABLE 2
DECLARE @bo_transfer TABLE
(
	trade_id	INT
)

INSERT INTO @bo_transfer
	select trade_id from bo_transfer where transfer_status != 'SETTLED' and trade_id !=0

-- MAIN QUERY USING TEMP TABLES IN SUB-QUERIES
-- QUERY #1 ----------------------------------------------
SELECT DISTINCT 
	trade.trade_id, 
	trade.version_num 
FROM  trade trade , product_desc product_desc  
WHERE product_desc.product_id = trade.product_id  
	AND product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp') 
	AND trade.trade_status = 'VERIFIED' 
	AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}
	AND trade_id not in (select trade_id from @bo_transfer)
	and trade_id not in (SELECT netted_transfer_id from @Transfer where netted_transfer_id != 0) 
	and trade_id not in (select trade_id           from @Transfer WHERE netted_transfer_id  = 0) 

-- QUERY #2 ----------------------------------------------
SELECT DISTINCT 
	trade.trade_id, 
	trade.version_num 
FROM  trade t 
	INNER JOIN product_desc product_desc  ON product_desc.product_id = t.product_id
WHERE product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp') 
	AND t.trade_status = 'VERIFIED' 
	AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}
	AND trade_id not in (select trade_id from @bo_transfer)
	and trade_id not in (SELECT netted_transfer_id from @Transfer where netted_transfer_id != 0) 
	and trade_id not in (select trade_id           from @Transfer WHERE netted_transfer_id  = 0)

Select allOpen in new window

Oh. There was an alias issue in my Query #2. Here is the corrected one.
Raj

-- QUERY #2 ----------------------------------------------
SELECT DISTINCT 
	t.trade_id, 
	t.version_num 
FROM  trade t 
	INNER JOIN product_desc pd  ON pd.product_id = t.product_id
WHERE pd.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp') 
	AND t.trade_status = 'VERIFIED' 
	AND trade_date_time <= {ts '2010-09-22 21:59:00.0'}
	AND trade_id not in (select trade_id from @bo_transfer)
	and trade_id not in (SELECT netted_transfer_id from @Transfer where netted_transfer_id != 0) 
	and trade_id not in (select trade_id           from @Transfer WHERE netted_transfer_id  = 0)

Select allOpen in new window

Also what about my comment - http:#33805466 ?
Raj

If these fields have INDEX, it would result in faster execution of your query

trade.trade_id
trade.product_id
trade.trade_date_time
bo_transfer.trade_id
bo_transfer.netted_transfer_id
product_desc.product_id
product_desc.product_type

Some of them may be primary key or foreign keys.

Raj

BrichSoft : a little fix just in case you going to edit your query for make it as "IN"
NOT (A AND B) = (Not A) OR (Not B)

abooyen2: minimum required index to help performance of this query is

table bo_message  : 1 index for message_type and/or message_status
table bo_transfer    : 1 index for transfer_status
table trade              : 1 index for trade_id
table product_desc : 1 index for product_type

Hi RajkumarGS

The query is still running for 30 minutes now

So it reduced from 6 hours to 30 minutes ?

What about INDEX ?

Raj

Ok I got it. :)

The queries with temp table will show some difference only if the tables you used in sub-queries are having very large data.

Check INDEX for those fields
Raj

Try this -- simplified

SELECT DISTINCT
      trade.trade_id,
      trade.version_num
FROM
      trade trade,
      product_desc product_desc
WHERE
    product_desc.product_id   = trade.product_id
AND product_desc.product_type IN ('Cash','FX','FXForward','FXOptionForward','FXSwap','FXTakeUp')
AND trade.trade_status        = 'VERIFIED'
AND trade.trade_date_time     <= {ts '2010-09-22 21:59:00.0'}
AND trade.trade_id NOT IN  (
                           SELECT CASE WHEN transfer_status != 'SETTLED' and trade_id !=0 THEN trade_id
                                       WHEN netted_transfer_id = 0 THEN trade_id
                                       ELSE netted_transfer_id
                                  END
                           FROM bo_transfer
                           WHERE (transfer_status != 'SETTLED' and trade_id !=0)
                           OR (transfer_id in (select transfer_id from bo_message where message_type in ('PAYMENTMSG','RECEIPTMSG') and message_status != 'ACKED'))
                           )

we fixed the query but I will award the points because it gave us some direction