Need Help to Optimize SQL Query

Question

i'm having a sql master table CHANNEL_PT which i want to fill based on two other master tables CHANNEL and PT.

the CHANNEL_PT cosist of three colums CHANNEL_PT_CD, CHANNEL_CD and PT_CD.

the scenario to insert records into CHANNEL_PT is such that, if i am having two entries in the CHANNEL table,

enter image description here

and two entries in PT table as below,

enter image description here

then the CHANNEL_PT table would be as below

enter image description here

i knew that this can be done with CURSOR but i'm not using it due to performance concern.

i have written the query as below to get the expected result but want to know any other more efficient way or optimized query.

BEGIN TRANSACTION
DECLARE @CH INT;
DECLARE @CH_CNT INT;
DECLARE @CH_MAX INT; 
SELECT @CH_MAX = MAX(CHANNEL_CD) FROM CHANNEL;
SELECT @CH = ISNULL(MIN(CHANNEL_CD),0),@CH_CNT=COUNT(CHANNEL_CD) FROM CHANNEL WHERE CHANNEL_CD > -1

WHILE @CH <= @CH_MAX

BEGIN
    DECLARE @PT INT;
    DECLARE @PT_CNT INT;
    DECLARE @PT_MAX INT; 
    SELECT @PT_MAX = MAX(PT_CD) FROM PT;
    SELECT @PT = ISNULL(MIN(PT_CD),0),@PT_CNT=COUNT(PT_CD) FROM PT WHERE PT_CD > -1
    WHILE @PT <=@PT_MAX
        BEGIN
            DECLARE @CPT INT;
            SELECT @CPT = ISNULL(MAX(CHANNEL_PT_CD),0) FROM CHANNEL_PT
            IF NOT EXISTS(SELECT CHANNEL_CD,PT_CD FROM CHANNEL_PT WHERE CHANNEL_CD=@CH and PT_CD=@PT)
                BEGIN
                    INSERT INTO CHANNEL_PT VALUES(@CPT+1,@CH,@PT)
                END
            SELECT @PT = MIN(PT_CD) FROM PT WHERE PT_CD > @PT
        END 
    SELECT @CH=MIN(CHANNEL_CD) FROM CHANNEL WHERE CHANNEL_CD > @CH
END
COMMIT;

Cursors are in most cases slower than set-based operations. But you replace the cursor with a while loop which is even slower. — adrianm
– adrianm, Commented Nov 1, 2013 at 12:34

No Refunds No Returns · Accepted Answer · 2013-11-01 05:26:43Z

1

It sounds like you are describing a cross join. Is column CHANNEL_PT_CD defined as an IDENTITY column? That removes the need for you to assign that value since the server will do it for you.

Then you can do a simple INSERT/SELECT

INSERT INTO CHANNEL_PT
SELECT b.CHANNEL_CD, c.PT
  FROM CHANNEL b 
 cross join PT c

answered Nov 1, 2013 at 5:26

No Refunds No Returns

8,4464 gold badges36 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RobertKing Over a year ago

CHANNEL_PT_CD is not an IDENTITY column

TechDo · Accepted Answer · 2013-11-01 05:54:51Z

0

Please try:

DECLARE @CPT INT=0;
SELECT @CPT = ISNULL(MAX(CHANNEL_PT_CD),0) FROM CHANNEL_PT

INSERT INTO CHANNEL_PT
SELECT DISTINCT @CPT+ROW_NUMBER() OVER(ORDER BY b.CHANNEL_CD),
     b.CHANNEL_CD, 
     c.PT
FROM CHANNEL b, PT c
WHERE (SELECT COUNT(*) FROM CHANNEL_PT a 
       WHERE a.CHANNEL_CD=b.CHANNEL_CD AND a.PT_CD=c.PT)=0

edited Nov 1, 2013 at 5:54

answered Nov 1, 2013 at 5:18

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Collectives™ on Stack Overflow

Need Help to Optimize SQL Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related