[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

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.
0
Dale Fye
Asked:
Dale Fye
  • 3
  • 3
1 Solution
 
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
 
PortletPaulCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
PortletPaulCommented:
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
 
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
 
PortletPaulCommented:
Thanks. Still slow though :(
are statistics and indexes in good shape?
I suggest then looking at the execution plan

Cheers, Paul
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now