Hey guys, I have a script to add/update members in...
# sqlg
gus_dancy
02/01/2022, 7:00 PMHey guys, I have a script to add/update members in our members table. I ran it on an import table with 6K records and it finished in just a few seconds. But an import table with 96K rows it has been running over 90 minutes! Thoughts? https://pastebin.com/GL6FRTsC
a
ageax
02/01/2022, 7:17 PMAny reason for using a cursor instead of JOINs? Since a cursor processes row by row, a single INNER JOIN for updates (member id exists) and LEFT JOIN for inserts (member id is null) should be way faster.
✅ 3
g
gus_dancy
02/01/2022, 8:39 PMHmm, I don't follow.
gus_dancy
02/01/2022, 8:41 PMI thought I have to loop over the import table records and if they exist do an update, otherwise do an insert.
gus_dancy
02/01/2022, 8:42 PMI feel like I wrote something wrong and maybe it's running out of memory or something. There's no logical reason it can't do this in under 2 minutes based on the smaller imports I've done with it.
a
ageax
02/01/2022, 8:52 PMFrom what I see, there is no reason you can't achieve the same result with 2 JOIN statements, instead of a cursor
s
s1deburn
02/01/2022, 9:03 PMa single left join might even suffice or maybe i'm missing something
👍 2
s1deburn
02/01/2022, 9:22 PMlook up upsert/merge into for a lead, may need a window function to get the top memberid, i'd probably use a cte there
a
ageax
02/01/2022, 9:30 PM@s1deburn - Yes, technically it could be a single statement. Personally I prefer old style (2 statements) for clarity, but either works
ageax
02/01/2022, 9:31 PM@gus_dancy - I have to jump on a call, but here's an example of what I mean. https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=438e7a6bc4e831f7bbe33ec5ce772a15
g
gus_dancy
02/01/2022, 9:33 PMOh, that is interesting.
a
ageax
02/01/2022, 9:34 PMLook into what @s1deburn suggested too. It's the same concept, but using 1 statement versus 2
g
George Meng
02/01/2022, 9:46 PMI think you need to have two sqls, one for insert and one for update. Then you should get consistent performance. With cursor or similar (which process record by record), it would take long time.
George Meng
02/01/2022, 9:48 PMThe current solution, might caused locking issue since it need long time to run. It might be slow, but should not that slow.
a
ageax
02/02/2022, 12:51 AMWhat indexes exist on the tables?
3 Views