asked on

Follow-up question SQL Server Append query

Background is in this question.

Now I need to do an insert into that same table for those records from my view that do not have a matching record in the destination table, based on Equip_ID and DT_Recorded/docDate. The query I have looks like:

USE WHR_System_Tables

DECLARE @AsOf as datetime2(7)

SELECT @AsOf = NULL

INSERT INTO tbl_Readings_Tanks (Equip_ID, docDate, Prod_ID, Inches_End, Closing_Vol)
SELECT T.Equip_ID, T.DT_Recorded, T.Product_ID, T.Inches, T.Closing_Vol  
FROM (
SELECT ET.Equip_ID
, CTRI.FacilityID
, CTRI.DT_Recorded
, CTRI.Product_ID
, CTRI.TankNum
, CTRI.Inches
, ET.Diameter
, Closing_Vol = (CTRI.Inches/12) * (3.14159 * (ET.Diameter/2) * (ET.Diameter/2)) * 0.17811
FROM  dbo.vw_Equipment_Tanks as ET
INNER JOIN dbo.vw_Cygnet_Tank_Readings_Inches as CTRI
ON ET.DS_PK_Text = CTRI.FacilityID 
AND ET.Product_ID = CTRI.Product_ID 
AND ET.Tank_Num = CTRI.TankNum
WHERE (@AsOf IS NULL) OR (CTRI.Dt_recorded > @AsOf)
) as T
LEFT JOIN tbl_Readings_Tanks as RT
ON T.Equip_ID = RT.Equip_ID
AND T.DT_Recorded = RT.docDate
WHERE RT.TankReadingID IS NULL

Open in new window

The select part of this is basically an unmatched query, find all the records in T, that don't have a matching record in RT, and insert them into tbl_Readings_Tanks.

Dale Fye

ASKER

At the moment, because @AsOf = NULL, there are about 10,000 records that will be added to the destination table, which contains about 2M records at the moment.

That query is still running after over 15 minutes. So the real question is, is there a quicker way to identify the records in T that don't already exist in RT, something like:

SELECT * FROM (...) as T
WHERE NOT Exists (SELECT TankReadingID FROM tbl_Readings_Tanks as RT where RT.Equip_ID = T.Equip_ID and RT.docDate = T.DT_Recorded)

Is that going to run any quicker? I've already got an index on Equip_ID and docDate in tbl_Readings_Tanks.

PortletPaul

I'm not certain what the question is to be honest. Is there a problem in the above?

FYI

Since sql 2008 there is a MERGE capability. With this you can combine both the update and insert (sometimes referred to as "upsert" :)

The structure of a MERGE is neat. You can branch on "WHEN MATCHED" where you do the updating, and WHEN NOT MATCHED" where you do the inserts, and you then don't need to concern yourself with determining the un-matched records - merge does this for you.

Dale Fye

ASKER

Paul,

The "problem" with the above is that it took 17 min, 33 seconds to append 9K records. Would like to do this significantly quicker! I'll take a look at the Merge syntax, we have been able to do "Upsert" queries in Access since at least 2003, but last time I had checked, SQL Server didn't have that capability. Is it basically the same as the Update query but with left joins between the table that has the data and the destination table?

Dale

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

Dale Fye

ASKER

Well, the Not Exists method only took 11 min, 33 sec, as opposed to over 17 minutes for the other method.

am amazed that there is not a quicker method, but if I can combine the matched and unmatched queries with a merge, that might be even better.

PortletPaul

Thanks. Still slow though :(
are statistics and indexes in good shape?
I suggest then looking at the execution plan

Cheers, Paul