Improving Stored Procedure performance. ( Help me chose the better one )

Hello,

I have two versions of the stored procedure which I have written to insert into a table. I am not sure which one is more efficient. Can you guys help me out.

Version 1:

INSERT INTO CST_CUST_PHONE

SELECT (SELECT MAX(CST_CUST_PHONE.PHONE_SEQ) FROM CST_CUST_PHONE WHERE CUST_ID = LC.CustomerID) + 1 ,
          LC.CustomerID,280,null,null,LC.PhoneNumber,'HOME',0,DATEADD(D,DATEDIFF(D,0,GETDATE()),0),null,1,null,null,GETDATE(),'TW_LCUS',NULL,NULL,1,NULL,NULL
FROM @TableParam LC LEFT JOIN CST_CUST_PHONE CP ON LC.CustomerID = CP.CUST_ID AND LC.PhoneNumber = CP.PHONE_NUM                               
WHERE CP.CUST_ID IS NULL AND LC.PhoneNumber IS NOT NULL


Version 2:


INSERT INTO CST_CUST_PHONE

SELECT  SEQTable.SEQ + 1 ,
          LC.CustomerID,280,null,null,LC.PhoneNumber,'HOME',0,DATEADD(D,DATEDIFF(D,0,GETDATE()),0),null,1,null,null,GETDATE(),'TW_LCUS',NULL,NULL,1,NULL,NULL
FROM @TableParam LC LEFT JOIN CST_CUST_PHONE CP ON LC.CustomerID = CP.CUST_ID AND LC.PhoneNumber = CP.PHONE_NUM
                              LEFT JOIN (SELECT CST_CUST_PHONE.CUST_ID, MAX(CST_CUST_PHONE.PHONE_SEQ)+ 1 as SEQ FROM CST_CUST_PHONE GROUP BY  CST_CUST_PHONE.CUST_ID ) SEQTable ON LC.CustomerID = SEQTable.CUST_ID
WHERE CP.CUST_ID IS NULL AND LC.PhoneNumber IS NOT NULL


Version 1 uses a nested select, where as Version 2 uses the same select as a left join.

Or if you can show me a  better way to do this ... that would be awesome !!

Hoping for some help.
AJ
Ashwin_shastryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AshokSr. Software EngineerCommented:
Time both and use the one that executes faster.
0
AshokSr. Software EngineerCommented:
declare @startproc datetime
declare @endproc datetime
declare @time integer

select @startproc = getdate();

exec <stored procedure>;
select @endproc = getdate();

select @time = DATEDIFF(second, @startproc, @endproc);

print str(@time);
0
Ashwin_shastryAuthor Commented:
Thanks for the reply Ashok111.

I really don't see much of a difference right now as the DB table I am using has only few rows of data.

As a matter of principle, can you tell me which would perform better.

Is recommended to avoid nested selects ( Version 1) ?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Aneesh RetnakaranDatabase AdministratorCommented:
run both of them after running these and post the results on the messages part

SET STATISTICS_IO ON
SET STATISTICS_TIME ON
GO
0
AshokSr. Software EngineerCommented:
In some cases, nested select would be better.
In some cases, without nested select would be better.

It all depends on data, # of tables, indexes, the way the SQL is written, etc.

I would suggest you insert many records for testing then test both.

HTH
Ashok
0
AshokSr. Software EngineerCommented:
Other way is

Query Analyzer will show you the execution plan for SQL statements (choose
Query / Display Estimated Execution Plan)
or
you can use the SET
SHOWPLAN_TEXT ON statement to return the text of the plan when you execute a
query.

HTH
Ashok
0
PortletPaulfreelancerCommented:
if taking a bet, I'd go with version 2.
>> correlated subqueries in the selection list are "generally" things to avoid in my opinion
>> they are executed on a per row basis, whereas the joined derived table is produced as a set

but:

What happens if the table structure is altered? (i.e. you are not specifying the fields you are inserting) - why not specify the fields instead of relying on default table layout.

&
Don't you need ISNULL here?


INSERT
        INTO CST_CUST_PHONE

        SELECT
               ISNULL(SEQTable.SEQ,0) + 1


and aren't you adding one, twice?

        LEFT JOIN (
                SELECT
                        CST_CUST_PHONE.CUST_ID
                        , MAX(CST_CUST_PHONE.PHONE_SEQ) + 1 AS SEQ
                FROM CST_CUST_PHONE
                GROUP BY CST_CUST_PHONE.CUST_ID
                )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.