SQL中的多线程? [英] Multi Thread in SQL?

问题描述

我有一个 SQL 查询喜欢

I have a SQL query Like

SELECT Column1, Column2, Column3, **ufn_HugeTimeProcessFunction**(Column1, Column2, @Param1) As Column4
From Table1

这个 ufn_HugeTimeProcessFunction 函数针对大表(就行数而言)运行，并且后面有几个计算返回值.

This ufn_HugeTimeProcessFunction function run against large table (in terms of number of rows) and there are several calculation behind to return value.

我能否强制 SQL 编译器在另一个线程(进程)中运行该函数?

Am I able to force the SQL compiler to run that function in another thread (process)?

已基本上该函数从 3 个不同的数据库中获取数据.这就是为什么我打算并行"运行它，而且不可能更改其他数据库上的索引

Edited : Basically that function get the data from 3 different databases. That's why I am planing to run it "in parallel", moreover it is not possible to change the indexes on the other databases

推荐答案

如果运行 SQL Server 的服务器计算机有多个 CPU，则 SQL Server 可以使用多个线程并行运行单个查询.除了在多个处理器上运行用户查询之外，SQL Server 还可以使用多个线程来构建索引.在检查文本或图形执行计划时，如果查询使用多个处理器，您会注意到交换运算符分发流、重新分区流和收集流.通常，大量使用 CPU 周期的查询很适合并行执行.例如，在将输出返回给用户之前连接几个大表并对输出进行排序的查询可能会受益于并行执行计划.

If the server computer on which SQL Server is running has multiple CPU's SQL Server can run a single query in parallel using multiple threads. In addition to running user queries on multiple processors SQL Server can also use multiple threads to build indexes. When examining textual or graphical execution plans you will notice exchange operators distribute streams, repartition streams and gather streams if the query is using more than one processor. Typically queries that make heavy use of CPU cycles are good candidates for parallel execution. For example a query joining several large tables and sorting the output before returning it to the user is likely to benefit from a parallel execution plan.

简而言之，SQL server 本身就可以很好地判断一个查询是否可以在多个线程中运行.基本上查询优化器会查看查询的各个部分是否可以并行运行并接受调用.

In brief, SQL server itself is a good judge of whether a query can be run in multiple threads or not. Basically query optimizer sees if parts of the query can be run in parallel and takes a call.

如果查询包含无法并行运行的标量运算符或关系运算符，则不会考虑并行执行.此外，如果要操作的行数相对较少，查询优化器不会考虑并行执行计划.

If the query contains a scalar operator or relational operators that cannot run in parallel, then it won't be considered for parallel execution. Furthermore, if the number of rows to be operated on is relatively low, query optimizer doesn't consider parallel execution plans.

照你的话来说，这个功能很耗时.所以外部选择和函数肯定会在不同的线程上运行.但是，如果没有 ufn_HugeTimeProcessFunction 的可见性，将很难提供优化并行运行函数的解决方案.如果没有，我建议您查看函数的执行计划，看看是否可以调整查询以减少标量和关系操作

Going by your words, the function is time consuming. So external select and the function will most definitely be running on different threads. However, without visibility to the ufn_HugeTimeProcessFunction, it will be very difficult to provide a solution to optimize the function for parallel runs. In the absence of that, I would recommend you to have a look at the execution plan for the function and see if you can tweak the query to reduce scalar and relational operations

引用来源:http:///www.toadworld.com/platforms/sql-server/w/wiki/9824.parallel-query-processing.aspx

有关在分析执行计划后使用并行性的更多信息，请访问 https://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/

More info on using parallelism after analyzing execution plans can be found at https://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/

这篇关于SQL中的多线程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！