데이터 베이스 MySQL 튜토리얼 SqlServer StringToTable性能测试

SqlServer StringToTable性能测试

Jun 07, 2016 pm 03:45 PM
sqlserver 성능 시험

问题起因: 最近做的项目DB数据量比较大(基本上一个月的数据就是10亿),而工程中Proc参数中包含有id拼接字符串,id拼接字符串格式:1,2,4,5,100,301。当数据量很小的情况下,这样做没有问题,但一旦数据量到达亿级,运行会很耗时,比如:当这样的参数id拼接

  • 问题起因:

最近做的项目DB数据量比较大(基本上一个月的数据就是10亿),而工程中Proc参数中包含有id拼接字符串,id拼接字符串格式:1,2,4,5,100,301。当数据量很小的情况下,这样做没有问题,但一旦数据量到达亿级,运行会很耗时,比如:当这样的参数id拼接字符串中包含有10万个id的时候(我们实际应用中确实有这么多个id需要传到数据库,而且这样的id是从库中取出后,又经过程序的筛选后剩余的id),像这样的语句:

<span>Declare</span> <span>@IDS</span> <span>nvarchar</span>(<span>max</span><span>);
</span><span>Set</span> <span>@IDS</span><span>=</span><span>'</span><span>10w个id用逗号分割组成的字符串</span><span>'</span><span>;
</span><span>Select</span> T10.<span>TEXT</span>,T10.Name <span>FROM</span> DX.M <span>as</span> T10 <span>inner</span> <span>join</span> dbo.StringToTable(<span>@IDS</span>,<span>'</span><span>,</span><span>'</span>) <span>as</span> T11 <span>on</span> T10.ID<span>=</span>T11.ID;
로그인 후 복사

执行了18个小时还未查询出数据。

备注:

虚拟机配置:内存:64G;CPU核数:40。

  • DBA建议:

我测试了下,性能还算可以。在解析5000个逗号之内性能还行,太多了,性能就急速下降了。 

最初的那个版本其实还是很常用的,性能要比改写之后的要好一些(在字符串特别长的情况下)。但是同样存在,如果字符串太长,性能急速下降的问题。

如果真的有5W以上逗号的字符串。这个SqlServer在执行计划上会消耗很多性能。

(自己也可以测试一下解析5000个逗号串和解析5W个字符串的差距,并不是5000字符串消耗时间*10的线性关系) 

所以应当写一个循环,一次处理一部分。

比如以下两种方法:

1. 每次截取前1W个字符串,解析出来之后插入到临时表,然后在解析后面的,在插入到临时表,循环处理。最后临时表和实际表进行关联。

insert into #t1

select id

from dbo.stringtotable(@字符串1‍)

 

insert into #t1

select id

from dbo.stringtotable(@字符串2)‍

 

2。用in的方式,每次where条件 in 一部分。然后将结果union all起来。

类似如下

select id

from table a

where id in (@字符串1)

union all

select id

from table a

where id in (@字符串2)‍

 

两种方法都可行。在字符串较短的情况下,第二种方法应该好一些。字符串较长,第一种应该好一些。

  • 测试代码:

<span>Declare</span> <span>@MRE_MROOIDS</span> <span>Nvarchar</span>(<span>Max</span><span>);

</span><span>Set</span> <span>@MRE_MROOIDS</span><span>=</span><span>'</span><span>2,4,5,396009,</span><span>'</span><span>;
</span><span>--</span><span>Set @MRE_MROOIDS='2,4,5,6,7,8,9,10,11,14,15,16,17,18,20,21,23,24,25,26,29,30';</span>

<span>Declare</span> <span>@SplitChar</span> <span>nvarchar</span>(<span>2</span><span>);
</span><span>Declare</span> <span>@EndIndex</span> <span>int</span><span>;
</span><span>Declare</span> <span>@Step</span> <span>int</span><span>;
</span><span>Declare</span> <span>@LastChars</span> <span>nvarchar</span>(<span>MAX</span><span>);
</span><span>Declare</span> <span>@CurrentTempChars</span> <span>nvarchar</span>(<span>max</span><span>);

</span><span>Set</span> <span>@LastChars</span><span>=</span><span>@MRE_MROOIDS</span><span>;
</span><span>Set</span> <span>@Step</span><span>=</span><span>5000</span><span>;
</span><span>Set</span> <span>@EndIndex</span><span>=</span><span>0</span><span>;
</span><span>Set</span> <span>@SplitChar</span><span>=</span><span>'</span><span>,</span><span>'</span><span>;

</span><span>IF</span> <span>EXISTS</span>(<span>SELECT</span> <span>*</span> <span>FROM</span> tempdb.dbo.sysobjects <span>where</span> id<span>=</span><span>OBJECT_ID</span>(N<span>'</span><span>tempdb..#StringToTableEntry_Temp10</span><span>'</span><span>))
    </span><span>Begin</span>
        <span>Drop</span> <span>Table</span><span> #StringToTableEntry_Temp10;            
    </span><span>End</span>    
    
<span>Create</span> <span>Table</span> #StringToTableEntry_Temp10(ID <span>INT</span><span>);


</span><span>While</span>(<span>LEN</span>(<span>@LastChars</span>)<span>></span><span>@Step</span><span>)
</span><span>Begin</span>    
    <span>Set</span> <span>@EndIndex</span><span>=</span> <span>charindex</span>(<span>@SplitChar</span>,<span>@LastChars</span>,<span>@Step</span><span>);
    
    </span><span>Set</span> <span>@CurrentTempChars</span><span>=</span><span>SubString</span>(<span>@LastChars</span>,<span>0</span>,<span>@EndIndex</span><span>);
    </span><span>--</span><span> insert into temp table</span>
    <span>Insert</span> <span>Into</span><span> #StringToTableEntry_Temp10
    </span><span>Select</span> Id <span>from</span> dbo.StringToTable2(<span>@CurrentTempChars</span>,<span>'</span><span>,</span><span>'</span><span>);
    
     </span><span>Set</span> <span>@LastChars</span><span>=</span><span>SubString</span>(<span>@LastChars</span>,<span>@EndIndex</span><span>+</span><span>1</span>,<span>LEN</span>(<span>@LastChars</span>)<span>-</span><span>@EndIndex</span><span>+</span><span>1</span><span>)
     </span><span>--</span><span>Select @LastChars as LastChars;</span>
     <span>Set</span> <span>@EndIndex</span><span>=</span><span>@EndIndex</span><span>+</span><span>@Step</span><span>;     
</span><span>End</span>

<span>If</span> <span>LEN</span>(<span>@LastChars</span>)<span>></span><span>0</span> <span>Begin</span>
    <span>Insert</span> <span>Into</span><span> #StringToTableEntry_Temp10
    </span><span>Select</span> Id <span>from</span> dbo.StringToTable2(<span>@LastChars</span>,<span>'</span><span>,</span><span>'</span><span>);
</span><span>End</span>


<span>Select</span> <span>COUNT</span>(<span>0</span>) <span>From</span> #StringToTableEntry_Temp10
로그인 후 복사

 

StringToTable2函数:

<span>ALTER</span> <span>FUNCTION</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>StringToTable</span><span>]</span><span>
(
    </span><span>@ids</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>max</span><span>),
    </span><span>@separator</span> <span>[</span><span>char</span><span>]</span>(<span>1</span><span>)
)
</span><span>RETURNS</span> <span>@IdsTable</span> <span>TABLE</span><span>
(
    </span><span>[</span><span>Id</span><span>]</span> <span>INT</span> <span>NOT</span> <span>NULL</span><span>
)
</span><span>AS</span>
<span>BEGIN</span>
    <span>IF</span>(<span>RIGHT</span>(<span>@ids</span>,<span>1</span>)<span>=</span><span>@separator</span><span>)
        </span><span>BEGIN</span>
            <span>SET</span> <span>@ids</span><span>=</span><span>SUBSTRING</span>(<span>@ids</span>,<span>0</span>,<span>LEN</span>(<span>@ids</span><span>));
        </span><span>END</span>

    <span>--</span><span>下面的方式性能更好</span>
    <span>IF</span>(<span>LEN</span>(<span>@ids</span>) <span>></span> <span>0</span><span>)
        </span><span>BEGIN</span>
            <span>DECLARE</span> <span>@i</span> <span>int</span><span>;        
            </span><span>SET</span> <span>@i</span> <span>=</span> <span>CHARINDEX</span>(<span>@separator</span>, <span>@ids</span><span>);
            
            </span><span>WHILE</span> <span>@i</span> <span>></span> <span>0</span>
                <span>BEGIN</span>
                    <span>INSERT</span> <span>@IdsTable</span> <span>VALUES</span>(<span>LEFT</span>(<span>@ids</span>, <span>@i</span> <span>-</span> <span>1</span><span>));            
                    </span><span>SET</span> <span>@ids</span> <span>=</span> <span>SUBSTRING</span>(<span>@ids</span>, <span>@i</span> <span>+</span> <span>1</span>, <span>LEN</span>(<span>@ids</span>) <span>-</span> <span>@i</span><span>);
                    </span><span>SET</span> <span>@i</span> <span>=</span> <span>CHARINDEX</span>(<span>@separator</span>, <span>@ids</span><span>);
                </span><span>END</span>
                
            <span>IF</span>(<span>LEN</span>(<span>@ids</span>) <span>></span> <span>0</span><span>)
                </span><span>BEGIN</span>
                    <span>INSERT</span> <span>@IdsTable</span> <span>VALUES</span>(<span>@ids</span><span>);
                </span><span>END</span>
        <span>END</span>        
    <span>RETURN</span><span>;
</span><span>END</span>
로그인 후 복사

 

  • 测试结果:

 

@MRE_MROOIDS包含id记录

@Step长度

执行时间

100,000

100000

00:09:15

100,000

20000

00:03:48

100,000

10000

00:01:57

100,000

5000

00:01:01

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

인기 기사

R.E.P.O. 에너지 결정과 그들이하는 일 (노란색 크리스탈)
3 몇 주 전 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. 최고의 그래픽 설정
3 몇 주 전 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. 아무도들을 수없는 경우 오디오를 수정하는 방법
3 몇 주 전 By 尊渡假赌尊渡假赌尊渡假赌

뜨거운 도구

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

mysql과 sqlserver 구문의 차이점은 무엇입니까 mysql과 sqlserver 구문의 차이점은 무엇입니까 Apr 22, 2024 pm 06:33 PM

MySQL과 SQL Server의 구문 차이는 주로 데이터베이스 개체, 데이터 유형, SQL 문 및 기타 측면에 반영됩니다. 데이터베이스 개체의 차이점에는 저장소 엔진, 파일 그룹 지정 방법, 인덱스 및 제약 조건 생성 등이 포함됩니다. 데이터 유형의 차이에는 숫자 유형, 문자 유형, 날짜 및 시간 유형의 차이가 포함됩니다. SQL 문의 차이점은 결과 집합 제한, 데이터 삽입, 업데이트 및 삭제 작업 등에 반영됩니다. 다른 차이점으로는 ID 열, 뷰 및 저장 프로시저를 만드는 방법이 있습니다. 다양한 데이터베이스 시스템을 사용할 때 오류를 방지하려면 이러한 차이점을 이해하는 것이 중요합니다.

새로운 Xianxia 모험에 참여하세요! 'Zhu Xian 2' 'Wuwei Test' 사전 다운로드가 가능합니다 새로운 Xianxia 모험에 참여하세요! 'Zhu Xian 2' 'Wuwei Test' 사전 다운로드가 가능합니다 Apr 22, 2024 pm 12:50 PM

새로운 판타지 요정 MMORPG '주선2'의 '무작용 테스트'가 4월 23일 출시된다. 원작으로부터 수천 년이 지난 주선 대륙에서는 어떤 새로운 요정 모험 이야기가 펼쳐질 것인가? 육계선불세계, 불멸수련을 위한 전임 학원, 불멸수련의 자유로운 삶, 불멸세계의 온갖 즐거움이 불멸친구들이 직접 탐험하는 것을 기다리고 있습니다! 이제 'Wuwei 테스트' 사전 다운로드가 공개되었습니다. 요정 친구들은 공식 웹사이트에 접속하여 다운로드할 수 있습니다. 서버가 출시되기 전에는 게임 서버에 로그인할 수 없습니다. 사전 다운로드 및 설치 후에는 활성화 코드를 사용할 수 있습니다. 완성 됐습니다. "Zhu Xian 2" "Inaction Test" 개장 시간: 4월 23일 10:00 - 5월 6일 23:59 Zhu Xian의 정통 속편 "Zhu Xian 2"의 새로운 요정 모험 장은 "Zhu Xian" 소설을 기반으로 합니다. 원작의 세계관을 바탕으로 게임 배경이 설정되었습니다.

다양한 Java 프레임워크의 성능 비교 다양한 Java 프레임워크의 성능 비교 Jun 05, 2024 pm 07:14 PM

다양한 Java 프레임워크의 성능 비교: REST API 요청 처리: Vert.x가 최고이며 요청 속도는 SpringBoot의 2배, Dropwizard의 3배입니다. 데이터베이스 쿼리: SpringBoot의 HibernateORM은 Vert.x 및 Dropwizard의 ORM보다 우수합니다. 캐싱 작업: Vert.x의 Hazelcast 클라이언트는 SpringBoot 및 Dropwizard의 캐싱 메커니즘보다 우수합니다. 적합한 프레임워크: 애플리케이션 요구 사항에 따라 선택하세요. Vert.x는 고성능 웹 서비스에 적합하고, SpringBoot는 데이터 집약적 애플리케이션에 적합하며, Dropwizard는 마이크로서비스 아키텍처에 적합합니다.

PHP 배열 키 값 뒤집기: 다양한 방법의 성능 비교 분석 PHP 배열 키 값 뒤집기: 다양한 방법의 성능 비교 분석 May 03, 2024 pm 09:03 PM

PHP 배열 키 값 뒤집기 방법의 성능 비교는 array_flip() 함수가 대규모 배열(100만 개 이상의 요소)에서 for 루프보다 더 나은 성능을 발휘하고 시간이 덜 걸리는 것을 보여줍니다. 키 값을 수동으로 뒤집는 for 루프 방식은 상대적으로 시간이 오래 걸립니다.

navicat 데이터베이스 파일은 어디에 있나요? navicat 데이터베이스 파일은 어디에 있나요? Apr 23, 2024 am 10:57 AM

Navicat 데이터베이스 구성 파일이 저장되는 위치는 운영 체제에 따라 다릅니다: Windows: 사용자별 경로는 %APPDATA%\PremiumSoft\Navicat\macOS: 사용자별 경로는 ~/Library/Application Support/Navicat\Linux: 사용자별 경로는 ~/ .config/navicat\입니다. 구성 파일 이름에는 navicat_mysql.ini와 같은 연결 유형이 포함되어 있습니다. 이러한 구성 파일은 데이터베이스 연결 정보, 쿼리 기록 및 SSH 설정을 저장합니다.

다른 언어에서 기능 테스트와 적용 범위의 차이점은 무엇입니까? 다른 언어에서 기능 테스트와 적용 범위의 차이점은 무엇입니까? Apr 27, 2024 am 11:30 AM

기능 테스트는 블랙박스 및 화이트박스 테스트를 통해 기능 기능성을 검증하고, 코드 커버리지는 테스트 케이스에 포함된 코드 부분을 측정합니다. Python 및 Java와 같은 언어마다 테스트 프레임워크, 적용 범위 도구 및 기능이 다릅니다. 실제 사례에서는 기능 테스트 및 적용 범위 평가를 위해 Python의 Unittest 및 Coverage와 Java의 JUnit 및 JaCoCo를 사용하는 방법을 보여줍니다.

C++에서 멀티스레드 프로그램의 성능을 최적화하는 방법은 무엇입니까? C++에서 멀티스레드 프로그램의 성능을 최적화하는 방법은 무엇입니까? Jun 05, 2024 pm 02:04 PM

C++ 다중 스레드 성능을 최적화하기 위한 효과적인 기술에는 리소스 경합을 피하기 위해 스레드 수를 제한하는 것이 포함됩니다. 경합을 줄이려면 가벼운 뮤텍스 잠금을 사용하세요. 잠금 범위를 최적화하고 대기 시간을 최소화합니다. 동시성을 향상하려면 잠금 없는 데이터 구조를 사용하세요. 바쁜 대기를 피하고 이벤트를 통해 스레드에 리소스 가용성을 알립니다.

벤치마크를 사용하여 Java 기능의 성능을 평가하는 방법은 무엇입니까? 벤치마크를 사용하여 Java 기능의 성능을 평가하는 방법은 무엇입니까? Apr 19, 2024 pm 10:18 PM

Java 기능의 성능을 벤치마킹하는 방법은 JMH(Java Microbenchmark Suite)를 사용하는 것입니다. 구체적인 단계는 다음과 같습니다. 프로젝트에 JMH 종속성을 추가합니다. 새로운 Java 클래스를 생성하고 @State로 주석을 달아 벤치마크 메서드를 나타냅니다. 클래스에 벤치마크 방법을 작성하고 @Benchmark로 주석을 답니다. JMH 명령줄 도구를 사용하여 벤치마크를 실행합니다.

See all articles