query optimization - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query optimization

Author

Topic

tushmodgil
Starting Member

2 Posts

Posted - 2014-06-13 : 06:57:22

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

30421 Posts

Posted - 2014-06-13 : 07:10:26

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;