Query optimization, timeout error in c#

Question

Is there any better way to write the below query for optimize the performance.

INSERT INTO [dbo].[MBQ_All] ([Uniq], [StoreClass], [Store], [code], [ExtendedDescription], [ITEM_CLASS], [SUPPLIER], [Brands], [Min], [Max],[ADS]
           ,[Store+whMBQ])

    SELECT [Uniq], [StoreClass], [Store], [code], [ExtendedDescription], [ITEM_CLASS], [SUPPLIER], [Brands], [Min], [Max],[ADS]
           ,[Store+whMBQ]
    FROM dbo.MBQ_All_1
    WHERE MBQ_All_1.Uniq NOT IN (
            SELECT UNIQ
            FROM dbo.MBQ_All
            );

The table MBQ_All has more than 4,00,000 Rows and MBQ_All_1 has 2,00,000.

I'm using SQL Server 2008.

can you please share a schema and an EXPLAIN? Is there an index on uniq? — Neville Kuyt
– Neville Kuyt, Commented Apr 5, 2017 at 13:26
There is one non clustered index in the table no uniq index exists.. — Developer
– Developer, Commented Apr 5, 2017 at 14:57
You will almost certainly get a significant improvement from putting an index on the uniq column on both tables. — Neville Kuyt
– Neville Kuyt, Commented Apr 5, 2017 at 15:17
Yes along with following Query fix added unique column to the table . Thank you for the tip — Developer
– Developer, Commented Apr 9, 2017 at 10:41

VDK · Accepted Answer · 2017-04-05 13:24:47Z

2

Maybe try using NOT EXISTS:

INSERT INTO [dbo].[MBQ_All] ([Uniq], [StoreClass], [Store], [code], [ExtendedDescription], [ITEM_CLASS], [SUPPLIER], [Brands], [Min], [Max],[ADS]
       ,[Store+whMBQ])

SELECT [Uniq], [StoreClass], [Store], [code], [ExtendedDescription], 
[ITEM_CLASS], [SUPPLIER], [Brands], [Min], [Max],[ADS] ,[Store+whMBQ]
FROM dbo.MBQ_All_1
WHERE NOT EXISTS (SELECT *
              FROM dbo.MBQ_All
              WHERE dbo.MBQ_All.UNIQ = MBQ_All_1.Uniq)

answered Apr 5, 2017 at 13:24

VDK

5833 silver badges8 bronze badges

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

2 Comments

Developer Over a year ago

Thank you very much, now it is taking only 4 secs, before it was more than 1 minute

VDK Over a year ago

No worries, glad I could help :)

Y.B. · Accepted Answer · 2017-04-05 13:30:07Z

2

It's a clear case for MERGE statement:

MERGE [dbo].[MBQ_All] Tgt
USING [dbo].[MBQ_All_1] Src ON Tgt.Uniq = Src.Uniq
WHEN NOT MATCHED THEN
    INSERT (    [Uniq],     [StoreClass],     [Store],     [code],     [ExtendedDescription],     [ITEM_CLASS],     [SUPPLIER],     [Brands],     [Min],     [Max],     [ADS],     [Store+whMBQ])
    VALUES (Src.[Uniq], Src.[StoreClass], Src.[Store], Src.[code], Src.[ExtendedDescription], Src.[ITEM_CLASS], Src.[SUPPLIER], Src.[Brands], Src.[Min], Src.[Max], Src.[ADS], Src.[Store+whMBQ]);

answered Apr 5, 2017 at 13:30

Y.B.

3,58617 silver badges24 bronze badges

Comments

Pavel Morshenyuk · Accepted Answer · 2017-04-05 13:30:12Z

1

One of the options would be to have LEFT JOIN, but I am not sure if it will be faster:

INSERT INTO [dbo].[MBQ_All] 
     ([Uniq], [StoreClass], [Store], [code], [ExtendedDescription], [ITEM_CLASS], [SUPPLIER], [Brands], [Min], [Max],[ADS]               ,[Store+whMBQ])

    SELECT [Uniq], [StoreClass], [Store], [code], [ExtendedDescription], [ITEM_CLASS], [SUPPLIER], [Brands], [Min], [Max],[ADS]
           ,[Store+whMBQ]
    FROM dbo.MBQ_All_1 mbqN
    LEFT JOIN MBQ_All mbqO ON mbqN.Uniq =  mbqO.Uniq
    WHERE mbqO.Uniq IS NULL

answered Apr 5, 2017 at 13:30

Pavel Morshenyuk

11.5k4 gold badges34 silver badges39 bronze badges

1 Comment

Developer Over a year ago

It is still better than the query I wrote , thank you !

Collectives™ on Stack Overflow

Query optimization, timeout error in c#

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related