4

I have a SQL query Like

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

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

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

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

vaduganathan
  • 141
  • 1
  • 1
  • 11
  • Have you ascertained that this is a CPU bottleneck? Unless it is badly written, this is more likely to be slow due to I/O constraints which in turn can be alleviated with the correct indexes. You might also be able to rewrite the function into a view which can speed things up. – Nick.Mc Jun 26 '14 at 08:57
  • 3
    Which DBMS are you using? –  Jun 26 '14 at 09:01
  • What does your function do? A badly written query will only get slower if you try to run it "in parallel". Database servers already use multiple threads to achieve high throughput and deal with juge tables. Check the execution plan of your query, ensure you have the proper indexes, look for table or index scans and add indexes wherever they are needed. – Panagiotis Kanavos Jun 26 '14 at 09:06
  • 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. – vaduganathan Jun 26 '14 at 09:18
  • 1
    What do you mean different databases? If they are on different servers the query is **already** running in parallel on each server. If they are on the same server, there little difference whether the tables are on the same db or not. Again what does the function do? Why is it taking so long? You'll get better answers if you post the function's code instead of looking for ways to trick the database – Panagiotis Kanavos Jun 26 '14 at 09:28
  • Sounds like a function which shouldn't run as part of a query – Allan S. Hansen Jun 26 '14 at 10:19

1 Answers1

5

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.

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.

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

Quote Source : http://www.toadworld.com/platforms/sql-server/w/wiki/9824.parallel-query-processing.aspx

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/

Jay
  • 1,980
  • 1
  • 13
  • 23