使用 scope_identity() 执行嵌套批量插入的最快方法? [英] Fastest way to perform nested bulk inserts with scope_identity() usage?

问题描述

在我们的一个 (C#) 应用程序中，我们正在插入/更新一个大图(100 次插入和更新).这被包装在一个事务中，因为我需要在发生错误时回滚整个事物.我们使用 Dapper 来执行 SQL 语句.

In one of our (C#) applications we are inserting/updating a large graph (100's of inserts and updates). This is wrapped in a transaction because I need the entire thing to rollback in case of an error. We are using Dapper to execute the SQL statements.

不幸的是，整个操作目前需要 2 到 8 秒.那是我们数据库中的核心表被锁定 2 到 8 秒，导致其他应用程序挂起或受到锁定的影响.

Unfortunately the entire operation is currently taking between 2 to 8 seconds. That's 2 to 8 seconds the core tables in our database are locked, causing other applications to hang or to suffer from the locks.

我确定其中一项操作，即向包含超过 1.2 亿条记录的表插入，占用了大部分时间，但我不确定如何优化它.

I determined that one of the operations, inserts to a table containing over 120 million records, is taking up most of the time, but I'm not sure how I could optimize this.

粗略地说，该表格图设置如下:

Roughly, that table graph is set up as follows:

table A (
  id int primary_key,
  name nvarchar
)

table B (
    id int primary_key,
    a_id int foreign_key, # has an index
    name nvarchar
)

在A中插入数据时，也需要在B中插入相应的数据.因此，我使用 scope_identity() 来获取记录的 id 并在将记录插入 B 时使用它.

When inserting data into A I also need to insert corresponding data into B. Thus, I'm using scope_identity() to get the id of the record and use it when inserting records into B.

伪明智如下:

# open transaction
# perform other table inserts
#
# this is one of the slowest operations
for item in list_a
    id_a = exec "insert into A (name) values (" + item.name + "); select scope_identity() as int"

    for bar in item.list_b
        exec "insert into B (id_a, name) values (" + id_a + ", " + bar.name + ")"
#
# perform more operations
# commit transaction

谷歌搜索时，解决方案之一是将其包装在事务中.但我不知道性能会如何，因为这已经包含在父事务中.它会解决问题吗?

When Googling this, one of the solutions is to wrap this in a transaction. But I don't know what the performance would be because this is already wrapped in the parent transaction. Would it solve the issue?

第二种解决方案是使用 union all select... 插入记录，但不知道我是否可以在其中使用 scope_identity()打电话.

A second solution is given by inserting the records using a union all select..., but don't know if I could use the scope_identity() in that call.

我该如何改进这种特殊情况?我还可以采取哪些其他措施来加快操作速度或防止其他应用程序受到这些锁定的影响?

How can I improve this particular case? What are some other things I can do to speed up the operation or to prevent other applications to suffer from these locks?

推荐答案

我以前写过类似的东西，但最后没有机会测试它的性能.
为了将数据插入到两个相关的表中，我使用了一个存储过程和一些 T-SQL 技巧，如表值参数、合并和输出子句.
这是一个示例脚本:

I've written something like this before, but in the end didn't get the chance to test it for performance.
To insert data into two related table I've used a single stored procedure and some T-SQL tricks like table valued parameters, merge and output clause.
Here is a sample script:

首先，创建示例表:tblBase 和 tblRelated 使用一对多外键链接.

First, create the sample tables: tblBase and tblRelated are linked with a one to many foreign key.

CREATE TABLE tblBase 
(
    base_id int identity(1,1) primary key, 
    base_data int
); 

CREATE TABLE tblRelated
(
    related_base_id int foreign key references tblBase (base_id), 
    related_Id int identity(1,1) primary key, 
    related_data int
);

现在我们有了示例表，我们可以开始创建插入的存储过程.

Now that we have sample tables, we can start creating the stored procedure for insert.

创建用户定义的表类型:
对于这个存储过程，我们需要使用三种用户定义的表类型:
一张为基表，
一个为相关表，
一个将临时 id 映射到数据库分配的 id.

Create user defined table types:
For this stored procedure, we need to use three user defined table types:
One for the base table,
one for the related table,
and one to map the temporary id to the database-assigned id.

CREATE TYPE udt_base As Table
(
    base_id int,
    base_temp_id int,  -- NOTE: when populating the table valued parameters, this column holds the id used to connect the base and related tables.

    base_data int  
);

CREATE TYPE udt_related As Table
(
    related_base_id int,
    related_data int
);

CREATE TYPE udt_idMap as table
(
    temp_id int,
    id int
)
GO

创建存储过程:
现在我们有了用户定义的类型，我们可以创建存储过程来使用它们.

Create the stored procedure:
Now that we have the user defined types, we can create the stored procedure to use them.

CREATE PROCEDURE stp_InsertMultipleRecordsToMultipleTables
(
    @base as dbo.udt_base readonly,
    @related as dbo.udt_related readonly
)
AS

DECLARE @idMap as dbo.udt_idMap
MERGE INTO tblBase USING @base AS temp ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
    INSERT (base_data)
    VALUES (temp.base_data)
    OUTPUT temp.base_temp_id, inserted.base_id -- Here we use the base_temp_id to map to the correct id
    INTO @idMap (temp_id, id);

INSERT INTO tblRelated(related_base_id, related_data)
SELECT id, related_data
FROM @related r
INNER JOIN @idMap m ON(r.related_base_id = m.temp_id) -- here we use the map to insert the related records with the correct base ids


GO

测试:
对基表中的 10 条记录和相关表中的 1000 条记录进行了测试，执行时间不到 1 秒.
这是测试的脚本:

Testing:
Tested on 10 records in the base table and a 1000 records in the related table, executed in less then 1 second.
Here is the script for the test:

-- declare and populate table parameters to send to the procedure
DECLARE @base as dbo.udt_base,
        @related as dbo.udt_related

INSERT INTO @base (base_temp_id, base_data) VALUES 
(10, 10), (20, 20), (30, 30), (40, 40), (50, 50), (60, 60), (70, 70), (80, 80), (90, 90), (100, 100)

INSERT INTO @related(related_base_id, related_data) VALUES 
(10, 100), (10, 101), (10, 102), (10, 103), (10, 104), (10, 105), (10, 106), (10, 107), (10, 108), (10, 109), 
(10, 110), (10, 111), (10, 112), (10, 113), (10, 114), (10, 115), (10, 116), (10, 117), (10, 118), (10, 119), 
(10, 120), (10, 121), (10, 122), (10, 123), (10, 124), (10, 125), (10, 126), (10, 127), (10, 128), (10, 129), 
(10, 130), (10, 131), (10, 132), (10, 133), (10, 134), (10, 135), (10, 136), (10, 137), (10, 138), (10, 139), 
(10, 140), (10, 141), (10, 142), (10, 143), (10, 144), (10, 145), (10, 146), (10, 147), (10, 148), (10, 149), 
(10, 150), (10, 151), (10, 152), (10, 153), (10, 154), (10, 155), (10, 156), (10, 157), (10, 158), (10, 159), 
(10, 160), (10, 161), (10, 162), (10, 163), (10, 164), (10, 165), (10, 166), (10, 167), (10, 168), (10, 169), 
(10, 170), (10, 171), (10, 172), (10, 173), (10, 174), (10, 175), (10, 176), (10, 177), (10, 178), (10, 179), 
(10, 180), (10, 181), (10, 182), (10, 183), (10, 184), (10, 185), (10, 186), (10, 187), (10, 188), (10, 189), 
(10, 190), (10, 191), (10, 192), (10, 193), (10, 194), (10, 195), (10, 196), (10, 197), (10, 198), (10, 199), 
(20, 200), (20, 201), (20, 202), (20, 203), (20, 204), (20, 205), (20, 206), (20, 207), (20, 208), (20, 209), 
(20, 210), (20, 211), (20, 212), (20, 213), (20, 214), (20, 215), (20, 216), (20, 217), (20, 218), (20, 219), 
(20, 220), (20, 221), (20, 222), (20, 223), (20, 224), (20, 225), (20, 226), (20, 227), (20, 228), (20, 229), 
(20, 230), (20, 231), (20, 232), (20, 233), (20, 234), (20, 235), (20, 236), (20, 237), (20, 238), (20, 239), 
(20, 240), (20, 241), (20, 242), (20, 243), (20, 244), (20, 245), (20, 246), (20, 247), (20, 248), (20, 249), 
(20, 250), (20, 251), (20, 252), (20, 253), (20, 254), (20, 255), (20, 256), (20, 257), (20, 258), (20, 259), 
(20, 260), (20, 261), (20, 262), (20, 263), (20, 264), (20, 265), (20, 266), (20, 267), (20, 268), (20, 269), 
(20, 270), (20, 271), (20, 272), (20, 273), (20, 274), (20, 275), (20, 276), (20, 277), (20, 278), (20, 279), 
(20, 280), (20, 281), (20, 282), (20, 283), (20, 284), (20, 285), (20, 286), (20, 287), (20, 288), (20, 289), 
(20, 290), (20, 291), (20, 292), (20, 293), (20, 294), (20, 295), (20, 296), (20, 297), (20, 298), (20, 299), 
(30, 300), (30, 301), (30, 302), (30, 303), (30, 304), (30, 305), (30, 306), (30, 307), (30, 308), (30, 309), 
(30, 310), (30, 311), (30, 312), (30, 313), (30, 314), (30, 315), (30, 316), (30, 317), (30, 318), (30, 319), 
(30, 320), (30, 321), (30, 322), (30, 323), (30, 324), (30, 325), (30, 326), (30, 327), (30, 328), (30, 329), 
(30, 330), (30, 331), (30, 332), (30, 333), (30, 334), (30, 335), (30, 336), (30, 337), (30, 338), (30, 339), 
(30, 340), (30, 341), (30, 342), (30, 343), (30, 344), (30, 345), (30, 346), (30, 347), (30, 348), (30, 349), 
(30, 350), (30, 351), (30, 352), (30, 353), (30, 354), (30, 355), (30, 356), (30, 357), (30, 358), (30, 359), 
(30, 360), (30, 361), (30, 362), (30, 363), (30, 364), (30, 365), (30, 366), (30, 367), (30, 368), (30, 369), 
(30, 370), (30, 371), (30, 372), (30, 373), (30, 374), (30, 375), (30, 376), (30, 377), (30, 378), (30, 379), 
(30, 380), (30, 381), (30, 382), (30, 383), (30, 384), (30, 385), (30, 386), (30, 387), (30, 388), (30, 389), 
(30, 390), (30, 391), (30, 392), (30, 393), (30, 394), (30, 395), (30, 396), (30, 397), (30, 398), (30, 399), 
(40, 400), (40, 401), (40, 402), (40, 403), (40, 404), (40, 405), (40, 406), (40, 407), (40, 408), (40, 409), 
(40, 410), (40, 411), (40, 412), (40, 413), (40, 414), (40, 415), (40, 416), (40, 417), (40, 418), (40, 419), 
(40, 420), (40, 421), (40, 422), (40, 423), (40, 424), (40, 425), (40, 426), (40, 427), (40, 428), (40, 429), 
(40, 430), (40, 431), (40, 432), (40, 433), (40, 434), (40, 435), (40, 436), (40, 437), (40, 438), (40, 439), 
(40, 440), (40, 441), (40, 442), (40, 443), (40, 444), (40, 445), (40, 446), (40, 447), (40, 448), (40, 449), 
(40, 450), (40, 451), (40, 452), (40, 453), (40, 454), (40, 455), (40, 456), (40, 457), (40, 458), (40, 459), 
(40, 460), (40, 461), (40, 462), (40, 463), (40, 464), (40, 465), (40, 466), (40, 467), (40, 468), (40, 469), 
(40, 470), (40, 471), (40, 472), (40, 473), (40, 474), (40, 475), (40, 476), (40, 477), (40, 478), (40, 479), 
(40, 480), (40, 481), (40, 482), (40, 483), (40, 484), (40, 485), (40, 486), (40, 487), (40, 488), (40, 489), 
(40, 490), (40, 491), (40, 492), (40, 493), (40, 494), (40, 495), (40, 496), (40, 497), (40, 498), (40, 499), 
(50, 500), (50, 501), (50, 502), (50, 503), (50, 504), (50, 505), (50, 506), (50, 507), (50, 508), (50, 509), 
(50, 510), (50, 511), (50, 512), (50, 513), (50, 514), (50, 515), (50, 516), (50, 517), (50, 518), (50, 519), 
(50, 520), (50, 521), (50, 522), (50, 523), (50, 524), (50, 525), (50, 526), (50, 527), (50, 528), (50, 529), 
(50, 530), (50, 531), (50, 532), (50, 533), (50, 534), (50, 535), (50, 536), (50, 537), (50, 538), (50, 539), 
(50, 540), (50, 541), (50, 542), (50, 543), (50, 544), (50, 545), (50, 546), (50, 547), (50, 548), (50, 549), 
(50, 550), (50, 551), (50, 552), (50, 553), (50, 554), (50, 555), (50, 556), (50, 557), (50, 558), (50, 559), 
(50, 560), (50, 561), (50, 562), (50, 563), (50, 564), (50, 565), (50, 566), (50, 567), (50, 568), (50, 569), 
(50, 570), (50, 571), (50, 572), (50, 573), (50, 574), (50, 575), (50, 576), (50, 577), (50, 578), (50, 579), 
(50, 580), (50, 581), (50, 582), (50, 583), (50, 584), (50, 585), (50, 586), (50, 587), (50, 588), (50, 589), 
(50, 590), (50, 591), (50, 592), (50, 593), (50, 594), (50, 595), (50, 596), (50, 597), (50, 598), (50, 599), 
(60, 600), (60, 601), (60, 602), (60, 603), (60, 604), (60, 605), (60, 606), (60, 607), (60, 608), (60, 609), 
(60, 610), (60, 611), (60, 612), (60, 613), (60, 614), (60, 615), (60, 616), (60, 617), (60, 618), (60, 619), 
(60, 620), (60, 621), (60, 622), (60, 623), (60, 624), (60, 625), (60, 626), (60, 627), (60, 628), (60, 629), 
(60, 630), (60, 631), (60, 632), (60, 633), (60, 634), (60, 635), (60, 636), (60, 637), (60, 638), (60, 639), 
(60, 640), (60, 641), (60, 642), (60, 643), (60, 644), (60, 645), (60, 646), (60, 647), (60, 648), (60, 649), 
(60, 650), (60, 651), (60, 652), (60, 653), (60, 654), (60, 655), (60, 656), (60, 657), (60, 658), (60, 659), 
(60, 660), (60, 661), (60, 662), (60, 663), (60, 664), (60, 665), (60, 666), (60, 667), (60, 668), (60, 669), 
(60, 670), (60, 671), (60, 672), (60, 673), (60, 674), (60, 675), (60, 676), (60, 677), (60, 678), (60, 679), 
(60, 680), (60, 681), (60, 682), (60, 683), (60, 684), (60, 685), (60, 686), (60, 687), (60, 688), (60, 689), 
(60, 690), (60, 691), (60, 692), (60, 693), (60, 694), (60, 695), (60, 696), (60, 697), (60, 698), (60, 699), 
(70, 700), (70, 701), (70, 702), (70, 703), (70, 704), (70, 705), (70, 706), (70, 707), (70, 708), (70, 709), 
(70, 710), (70, 711), (70, 712), (70, 713), (70, 714), (70, 715), (70, 716), (70, 717), (70, 718), (70, 719), 
(70, 720), (70, 721), (70, 722), (70, 723), (70, 724), (70, 725), (70, 726), (70, 727), (70, 728), (70, 729), 
(70, 730), (70, 731), (70, 732), (70, 733), (70, 734), (70, 735), (70, 736), (70, 737), (70, 738), (70, 739), 
(70, 740), (70, 741), (70, 742), (70, 743), (70, 744), (70, 745), (70, 746), (70, 747), (70, 748), (70, 749), 
(70, 750), (70, 751), (70, 752), (70, 753), (70, 754), (70, 755), (70, 756), (70, 757), (70, 758), (70, 759), 
(70, 760), (70, 761), (70, 762), (70, 763), (70, 764), (70, 765), (70, 766), (70, 767), (70, 768), (70, 769), 
(70, 770), (70, 771), (70, 772), (70, 773), (70, 774), (70, 775), (70, 776), (70, 777), (70, 778), (70, 779), 
(70, 780), (70, 781), (70, 782), (70, 783), (70, 784), (70, 785), (70, 786), (70, 787), (70, 788), (70, 789), 
(70, 790), (70, 791), (70, 792), (70, 793), (70, 794), (70, 795), (70, 796), (70, 797), (70, 798), (70, 799), 
(80, 800), (80, 801), (80, 802), (80, 803), (80, 804), (80, 805), (80, 806), (80, 807), (80, 808), (80, 809), 
(80, 810), (80, 811), (80, 812), (80, 813), (80, 814), (80, 815), (80, 816), (80, 817), (80, 818), (80, 819), 
(80, 820), (80, 821), (80, 822), (80, 823), (80, 824), (80, 825), (80, 826), (80, 827), (80, 828), (80, 829), 
(80, 830), (80, 831), (80, 832), (80, 833), (80, 834), (80, 835), (80, 836), (80, 837), (80, 838), (80, 839), 
(80, 840), (80, 841), (80, 842), (80, 843), (80, 844), (80, 845), (80, 846), (80, 847), (80, 848), (80, 849), 
(80, 850), (80, 851), (80, 852), (80, 853), (80, 854), (80, 855), (80, 856), (80, 857), (80, 858), (80, 859), 
(80, 860), (80, 861), (80, 862), (80, 863), (80, 864), (80, 865), (80, 866), (80, 867), (80, 868), (80, 869), 
(80, 870), (80, 871), (80, 872), (80, 873), (80, 874), (80, 875), (80, 876), (80, 877), (80, 878), (80, 879), 
(80, 880), (80, 881), (80, 882), (80, 883), (80, 884), (80, 885), (80, 886), (80, 887), (80, 888), (80, 889), 
(80, 890), (80, 891), (80, 892), (80, 893), (80, 894), (80, 895), (80, 896), (80, 897), (80, 898), (80, 899), 
(90, 900), (90, 901), (90, 902), (90, 903), (90, 904), (90, 905), (90, 906), (90, 907), (90, 908), (90, 909), 
(90, 910), (90, 911), (90, 912), (90, 913), (90, 914), (90, 915), (90, 916), (90, 917), (90, 918), (90, 919), 
(90, 920), (90, 921), (90, 922), (90, 923), (90, 924), (90, 925), (90, 926), (90, 927), (90, 928), (90, 929), 
(90, 930), (90, 931), (90, 932), (90, 933), (90, 934), (90, 935), (90, 936), (90, 937), (90, 938), (90, 939), 
(90, 940), (90, 941), (90, 942), (90, 943), (90, 944), (90, 945), (90, 946), (90, 947), (90, 948), (90, 949), 
(90, 950), (90, 951), (90, 952), (90, 953), (90, 954), (90, 955), (90, 956), (90, 957), (90, 958), (90, 959), 
(90, 960), (90, 961), (90, 962), (90, 963), (90, 964), (90, 965), (90, 966), (90, 967), (90, 968), (90, 969), 
(90, 970), (90, 971), (90, 972), (90, 973), (90, 974), (90, 975), (90, 976), (90, 977), (90, 978), (90, 979), 
(90, 980), (90, 981), (90, 982), (90, 983), (90, 984), (90, 985), (90, 986), (90, 987), (90, 988), (90, 989), 
(90, 990), (90, 991), (90, 992), (90, 993), (90, 994), (90, 995), (90, 996), (90, 997), (90, 998), (90, 999), 
(100, 1000), (100, 1001), (100, 1002), (100, 1003), (100, 1004), (100, 1005), (100, 1006), (100, 1007), (100, 1008), (100, 1009), 
(100, 1010), (100, 1011), (100, 1012), (100, 1013), (100, 1014), (100, 1015), (100, 1016), (100, 1017), (100, 1018), (100, 1019), 
(100, 1020), (100, 1021), (100, 1022), (100, 1023), (100, 1024), (100, 1025), (100, 1026), (100, 1027), (100, 1028), (100, 1029), 
(100, 1030), (100, 1031), (100, 1032), (100, 1033), (100, 1034), (100, 1035), (100, 1036), (100, 1037), (100, 1038), (100, 1039), 
(100, 1040), (100, 1041), (100, 1042), (100, 1043), (100, 1044), (100, 1045), (100, 1046), (100, 1047), (100, 1048), (100, 1049), 
(100, 1050), (100, 1051), (100, 1052), (100, 1053), (100, 1054), (100, 1055), (100, 1056), (100, 1057), (100, 1058), (100, 1059), 
(100, 1060), (100, 1061), (100, 1062), (100, 1063), (100, 1064), (100, 1065), (100, 1066), (100, 1067), (100, 1068), (100, 1069), 
(100, 1070), (100, 1071), (100, 1072), (100, 1073), (100, 1074), (100, 1075), (100, 1076), (100, 1077), (100, 1078), (100, 1079), 
(100, 1080), (100, 1081), (100, 1082), (100, 1083), (100, 1084), (100, 1085), (100, 1086), (100, 1087), (100, 1088), (100, 1089), 
(100, 1090), (100, 1091), (100, 1092), (100, 1093), (100, 1094), (100, 1095), (100, 1096), (100, 1097), (100, 1098), (100, 1099)

-- execute the stored procedure
EXEC stp_InsertMultipleRecordsToMultipleTables @base, @related

-- test that the values are correct
SELECT *
FROM tblBase 
INNER JOIN tblRelated ON(base_id = related_base_id)

清理:

DROP TABLE tblRelated;
DROP TABLE tblBase;
DROP PROCEDURE stp_InsertMultipleRecordsToMultipleTables;
DROP TYPE udt_base;
DROP TYPE udt_related;
DROP TYPE udt_idMap;
GO

附注:
这个答案是一篇题为 在我的博客上在单个存储过程中插入多个父级和多个子级.
它的代码版本略有不同，但思路是一样的.

Side note:
This answer is the inspiration to a blog post entitled Insert multiple parents with multiple children in a single stored procedure on my blog.
It has a slightly different version of the code, but the idea is just the same.

这篇关于使用 scope_identity() 执行嵌套批量插入的最快方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！