Which one should I use between two based on performance?

Question

Which one should I use between two based on performance?

atikdesai

Mr or Mrs. 500

Points: 517
More actions
May 26, 2010 at 3:05 am

#97376

Option 1
SELECT MM.mobile_number,MM.pbuserid,MM.SWID,MM.[message],MM.characterID,MM.tacticID,MM.destID,
MM.subject_ln,MM.body,MM.campaign_code,
M.mobile_opt_dts,
Row_Num = ROW_NUMBER() OVER(PARTITION BY MM.mobile_number ORDER BY M.mobile_opt_dts DESC)
INTO #TEMP
FROM tbl_Mobile_Messages as MM
Inner Join tbl_Mobile as M
On M.SWID = MM.SWID
WHERE tacticID=6 and mobile_number IS NOT NULL
--SELECT * from #temp
INSERT INTO TEMP_FINAL_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num)
SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num
FROM #TEMP WHERE Row_Num = 1
INSERT INTO TEMP_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code,SWID_dedupe)
SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code,NULL
FROM #TEMP WHERE Row_Num > 1
UPDATE TEMP_DEDUPED
SET SWID_dedupe = (SELECT SWID from TEMP_FINAL_DEDUPED where TEMP_DEDUPED.mobile_number = TEMP_FINAL_DEDUPED.mobile_number)
---------------------------------------------------------------------------------------------------------------------------------------
Option 2
;WITH messages AS
(
SELECT MM.mobile_number,MM.pbuserid,MM.SWID,MM.[message],MM.characterID,MM.tacticID,MM.destID,
MM.subject_ln,MM.body,MM.campaign_code,
M.mobile_opt_dts,
Row_Num = ROW_NUMBER() OVER(PARTITION BY MM.mobile_number ORDER BY M.mobile_opt_dts DESC)
FROM tbl_Mobile_Messages as MM
Inner Join tbl_Mobile as M
On M.SWID = MM.SWID
)
INSERT INTO TEMP_FINAL_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num)
SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num
FROM messages WHERE Row_Num = 1 and tacticID=8 and mobile_number IS NOT NULL
;WITH messages AS
(
SELECT MM.mobile_number,MM.pbuserid,MM.SWID,MM.[message],MM.characterID,MM.tacticID,MM.destID,
MM.subject_ln,MM.body,MM.campaign_code,
M.mobile_opt_dts,
Row_Num = ROW_NUMBER() OVER(PARTITION BY MM.mobile_number ORDER BY M.mobile_opt_dts DESC)
FROM tbl_Mobile_Messages as MM
Inner Join tbl_Mobile as M
On M.SWID = MM.SWID
)
INSERT INTO TEMP_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code,SWID_dedupe)
SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code,NULL
FROM messages WHERE Row_Num > 1 and tacticID=8 and mobile_number IS NOT NULL
UPDATE TEMP_DEDUPED
SET SWID_dedupe = (SELECT SWID from TEMP_FINAL_DEDUPED where TEMP_DEDUPED.mobile_number = TEMP_FINAL_DEDUPED.mobile_number)
May 26, 2010 at 4:18 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

The best way to find out is to run them both and then check the actual execution plan, make sure you clear the cache prior to testing them.

atikdesai Mr or Mrs. 500 Points: 517 More actions · Answer 2

Thanks for replying buddy !!!!

The problem is I don't have so many data. I have just TEST file with 10-20 records.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

In my experience Temp tables can perform better than CTEs, especially if you are referencing the data more than once.

However i would recommened generating some test data, there are a few scripts on here to generate test data, and then run each query and analyse the results.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

The problem is I don't have so many data. I have just TEST file with 10-20 records.

Then you may as well not do any performance testing , it will have zero value in the real world.

You need to test with a real world sized dataset.

In this case the # table route will probably be best due to it only have to do the real work , ie the join and the ranking function once.

Clear Sky SQL
My Blog[/url]