用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.

设置如下：

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

使用Google搜索时，一种解决方案是将其包装在交易中。但是我不知道性能如何，因为它已经包装在父事务中了。

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?

推荐答案

I '之前已经写过类似的东西，但是最后没有机会对其性能进行测试。

要将数据插入2个相关表中，我使用了一个存储过程和一些sql技巧像表值参数一样，合并和输出子句。

这是一个示例脚本：

I've written something like this before, but in the end didn't get the chance to test it for performance.
To inserts data into 2 related table I've used a single stored procedure and some 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

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