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.
LVL 50
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
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.
0
Dale FyeAuthor Commented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

PortletPaulfreelancerCommented:
oh, I see now, sorry, your follow-up wasn't available as I was prepping my initial reply.

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 would say "yes, good chance it will be faster, but can't guarantee it."

It isn't necessary to reference a field in the select clause, this would do the same job:

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

or: select 1

the exists/not exists decision is done inside the from clause, that select clause just goes for the ride :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
Thanks. Still slow though :(
are statistics and indexes in good shape?
I suggest then looking at the execution plan

Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.