asked on

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

Ashok

Time both and use the one that executes faster.

Ashok

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);

Ashwin_shastry

ASKER

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) ?

Aneesh

run both of them after running these and post the results on the messages part

SET STATISTICS_IO ON
SET STATISTICS_TIME ON
GO

Ashok

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

Ashok

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial