복붙노트

[SQL] SCOPE_IDENTITY () 사용과 중첩 된 대량 삽입을 수행 할 수있는 가장 빠른 방법은?

SQL

SCOPE_IDENTITY () 사용과 중첩 된 대량 삽입을 수행 할 수있는 가장 빠른 방법은?

우리 (C #을) 응용 프로그램 중 하나에서 우리는 큰 그래프 (삽입 및 업데이트 100의)를 갱신 / 삽입된다. 내가 오류의 경우에는 롤백에 전체 일을해야하기 때문에 이것은 트랜잭션에 싸여있다. 우리는 SQL 문을 실행하는 데 단정을 사용하고 있습니다.

불행하게도 전체 작업은 현재 2 ~ 8 초 사이에하고있다. 즉 우리의 데이타베이스는 제품의 핵심 테이블이 다른 응용 프로그램이 중단 또는 잠금에서 고통의 원인이 잠겨 이초 8입니다.

나는 작업의 하나가, 1 억 3 천 5 백만명 이상의 레코드를 포함하는 테이블에 삽입은 대부분의 시간을 차지,하지만이 최적화 수있는 방법을 잘 모르겠어요 결정했다.

다음과 같이 대략 그 나타난 그래프 설정 :

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 I에 데이터를 삽입하는 경우도 이와 같이, I가 레코드의 ID를 얻을 B.에 레코드를 삽입 할 때 그것을 사용 SCOPE_IDENTITY ()를 사용하고 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

이 인터넷 검색을 할 때, 솔루션 중 하나는 트랜잭션이를 래핑하는 것입니다. 그러나 나는이 이미 상위 트랜잭션에 싸여 있기 때문에 성능이 어떻게 될지 모른다. 이 문제를 해결시겠습니까?

두 번째 솔루션은 ... 선택,하지만 난 그 전화에 SCOPE_IDENTITY ()를 사용할 수 있는지 알고하지 않는 모든 노동 조합을 사용하여 레코드를 삽입하여 주어진다.

어떻게이 특정 사건을 향상시킬 수 있습니까? 내가 작업 속도를 높이기 위해 이들 잠금으로 고생하는 다른 응용 프로그램을 방지하기 위해 할 수있는 다른 일들은 무엇인가?

해결법

  1. ==============================

    1.내가 전에 이런 식으로 뭔가를 작성했지만, 결국 성능을 테스트 할 수있는 기회를 얻을하지 않았다. 2 관련 테이블에 데이터를 삽입에 나는 하나의 저장 프로 시저 및 테이블 반환 매개 변수, 병합 및 출력 절과 같은 몇 가지 SQL 트릭을 사용했습니다. 다음은 샘플 스크립트입니다 :

    내가 전에 이런 식으로 뭔가를 작성했지만, 결국 성능을 테스트 할 수있는 기회를 얻을하지 않았다. 2 관련 테이블에 데이터를 삽입에 나는 하나의 저장 프로 시저 및 테이블 반환 매개 변수, 병합 및 출력 절과 같은 몇 가지 SQL 트릭을 사용했습니다. 다음은 샘플 스크립트입니다 :

    첫째, 샘플 테이블을 만들 : tblBase 및 tblRelated은 많은 외국 키에 하나와 연결되어 있습니다.

    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
);

    이제 우리는 샘플 테이블을 가지고, 우리는 삽입에 대한 저장 프로 시저를 만들기 시작할 수 있습니다.

    사용자 정의 테이블 유형을 만듭니다 이 저장 프로 시저를 들어, 우리는 세 개의 사용자 정의 테이블 형식을 사용합니다 : 기본 테이블에 대한 하나, 관련 테이블 하나, 하나는 데이터베이스에 할당 된 ID에 임시 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 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

    테스트 : 미만 1 초 실행 관련 테이블의 기본 테이블의 레코드 (10) 및 1000 개의 레코드에 테스트했다. 다음은 테스트를위한 스크립트입니다 :

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

    2. from https://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL은 CHECK 제약 조건이 작동하지 않습니다  (0) 2020.03.25
[SQL] 때 SQL 테이블 별칭을 사용하는  (0) 2020.03.25
[SQL] 어떻게 PDO와 MySQL의 기능을 대체하는?  (0) 2020.03.24
[SQL] 마이크로 소프트 제트는 와일드 카드 : 별표 또는 백분율 기호를?  (0) 2020.03.24
[SQL] 어떻게 저장된 프로 시저에 테이블 이름을 통과해야합니까?  (0) 2020.03.24
,

티스토리툴바