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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 query optimization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tushmodgil
Starting Member

India
2 Posts

Posted - 06/13/2014 :  06:57:22  Show Profile  Reply with Quote
Kindly help to improve the below mentioned query for faster output. Both tables have 70k rows in it.
Create Table tmp_offer_final As
Select Distinct CUSTOMER_MSISDN,  RETAILER_MSISDN, TOPUP_AMOUNT,
TOPUP_DATE,
COM_PER,card_group,b.offer,b.offer2,b.offer3,b.offer_type,b.Count_sms,a.count_RC
From final_RECH_COMM_122 a, tmp_promo_map b
  Where (a.customer_msisdn,a.Retailer_msisdn) In  (Select
b.msisdn,b.retailer From tmp_promo_map)
  And ( a.topup_amount  >= b.offer
  Or a.topup_amount  >= b.offer2
  Or a.topup_amount  >= b.offer3);

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 06/13/2014 :  07:10:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The Microsoft SQL Server 2005 equivalent would be
SELECT DISTINCT	CUSTOMER_MSISDN,
		RETAILER_MSISDN,
		TOPUP_AMOUNT,
		TOPUP_DATE,
		COM_PER,
		card_group,
		b.offer,
		b.offer2,
		b.offer3,
		b.offer_type,
		b.Count_sms,
		a.count_RC
INTO		tmp_offer_final
FROM		final_RECH_COMM_122 AS a
INNER JOIN	tmp_promo_map AS b ON b.retailer = a.Retailer_msisdn
			AND b.msisdn = a.customer_msisdn
WHERE		a.topup_amount >= b.offer
		OR a.topup_amount >= b.offer2
		OR a.topup_amount >= b.offer3;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

tushmodgil
Starting Member

India
2 Posts

Posted - 06/13/2014 :  07:47:42  Show Profile  Reply with Quote
B.retailer,b.msisdn needs to be mapped in pair with a.retailer ,a.customer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 06/13/2014 :  14:13:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
They are!


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.05 seconds. Powered By: Snitz Forums 2000