Databases
StackOverflow2013
Postsdbo
POSQL Merge - How can I optimize this?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Merge - How can I optimize this?
    primarykey
    data
    text
    <p>Table A (table to merge into) has 90,000 rows Table B (source table) has 3,677 rows I would expect this to merge really quick but it's taking 30 minutes (and counting). How can it be optimized to run faster?</p> <pre><code>ALTER PROCEDURE [dbo].[MergeAddressFromGraph] -- no params </code></pre> <p>AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;</p> <pre><code>-- first add fids to the MergeFundraiserNameAddress table instead of the temp table? SELECT fundraiserid, ein INTO #fids FROM bb02_fundraiser BEGIN TRAN; MERGE BB02_FundraiserNameAddress AS T USING ( select f.fundraiserid, n.addresslines, n.town, n.county, n.postcode, n.country, n.fulladdress, n.ein from MergeFundraiserNameAddress n join bb02_fundraiser f on f.ein = n.ein and f.isdefault = 1 group by n.ein, f.fundraiserid, n.addresslines, n.town, n.county, n.postcode, n.country, n.fulladdress ) AS S ON (T.fundraiserid in( (select fundraiserid from #fids where ein = S.ein)) ) WHEN MATCHED THEN UPDATE SET -- ADDRESS T.addresslines = S.addresslines ,T.town = S.town ,T.county = S.county ,T.postcode = S.postcode ,T.country = S.country ,T.fulladdress = S.fulladdress ; DELETE FROM MergeFundraiserNameAddress COMMIT TRAN; drop table #fids </code></pre> <p>END</p> <p><strong>UPDATE</strong> I was able to improve the stored procedure which now runs in just a few seconds. I joined on the temp table instead of the bb02_fundraiser table and removed the subquery in the ON clause.</p> <p>I realize now that the Merge is not necessary and I could have used an Update instead, but I'm ok with this right now because an INSERT may be needed soon in a refactor.</p> <p><strong>UPDATED STORED PROCEDURE BELOW</strong> IF OBJECT_ID('tempdb..#fids') IS NOT NULL DROP TABLE #fids</p> <pre><code>SELECT fundraiserid, ein INTO #fids FROM bb02_fundraiser where isdefault = 1 BEGIN TRAN; MERGE BB02_FundraiserNameAddress AS T USING ( select f.fundraiserid, n.addresslines, n.town, n.county, n.postcode, n.country, n.fulladdress, n.ein from MergeFundraiserNameAddress n join #fids f on f.ein = n.ein group by n.ein, f.fundraiserid, n.addresslines, n.town, n.county, n.postcode, n.country, n.fulladdress ) AS S ON (T.fundraiserid = S.fundraiserid) WHEN MATCHED THEN UPDATE SET -- ADDRESS T.addresslines = S.addresslines ,T.town = S.town ,T.county = S.county ,T.postcode = S.postcode ,T.country = S.country ,T.fulladdress = S.fulladdress ; DELETE FROM MergeFundraiserNameAddress COMMIT TRAN; IF OBJECT_ID('tempdb..#fids') IS NOT NULL DROP TABLE #fids </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USInduster
    1. USInduster
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Merge - How can I optimize this?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPlease post the execution plan. You're right - we can get this to be very quick.
      singulars
      1. POSQL Merge - How can I optimize this?
      1. USusr
    2. COWhy are you using MERGE if the only possible outcome is an UPDATE? ([Here's why you might want to use caution with MERGE](http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/).)
      singulars
      1. POSQL Merge - How can I optimize this?
      1. USAaron Bertrand
    3. COAlso, perhaps it is being blocked. When this is running for 30 minutes, what does sys.dm_exec_requests say? This DMV can show you blocking_session_id as well as wait type for the session_id trying to run the MERGE.
      singulars
      1. POSQL Merge - How can I optimize this?
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload