I noticed my queries always use up 1 single core regardless of the numbers of cores my processor has. It is nothing wrong as queries are usually executed in sequential pattern....
In order to make full use of the new generation processor power(up to 8 core) , I was wondering if we could split the queries (depending on the conditions) into multiple similar queries and execute them together and maximize all the cores to increase the performance.
Let's take an example for a retail chain stores enterprise, if we need to calculate the labour hour for individual employees, and they work in many different stores, we could possibly classify/group them by areas (which consists of a few retail stores) and process the labour hour in the multi-threaded way , where each thread will process an area (or something like that), so 8 threads will be able to process/execute the same query on all the 8 cores and complete the queries 8 times faster(in theory)!
That's the idea, but....how can I fire 8 queries at the same time? 1 friend mentioned about using stored procedures. So we can call procedures within procedures. I am not very sure how to do it, but I will try....
Just wondering if any kind soul out there did practice/experiment similar thing before.....I guess it will be something very interesting worth trying!
SQL is a declarative language. As such, you say what result you want and the RDBMS figures out the best way to accomplish this. As such, if there is benefit to running multiple threads in parallel it will, if not it won't.
I can't think how to force parallelism, although I'm sure I did so once when I thought I knew better than the optimiser. You can certainly limit the number of processors used by queries.
All in all, I wouldn't worry about it. Parallelism is most common when you are scanning a large table (many millions of rows). If you aren't doing that sort of thing then multiple threads is unlikely to help in any event.