SQL Server Performance Forum – Threads Archive

Multithreaded Queries

Hi All, I don’t know why i’m always stucked with large data processing problems, here is my new problem and the solution which i need your openion about it first the data, it a simple set of data nothing complicated with it, its an order table to store orders made by clients, second the query: basic enough to getting some count and AVG of some fields, based on a group By, the problem, of course its my large database(as usual <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> ) and the time query takes to return results, note that the query is based on time periods, the query works fine in small time periods say 1 to 3 days, but for a week it takes alot of time,and this is not accepted in web enviroment,where i publish my reports so i started my analysis, the query that generates the data needed for the report consists of 7 queries,after re-writing, indexing,there still the time problem. so i was looking for a differen solution,and here it is,multithreading, i have 7 queries, so i created 7 threads for each query run them on the same time from my asp page code, and waited for the results. logically it looks like its going to be a greate solution, based on the idea that sql enginge takes much time bcoz it process one query by query, but when i run all the queries on the same time, i must get results faster than usual, but it didn’t happen at all, actually it takes time more than the sql analyzer take,first i thought its bcoz of network overhead ,but i tried this , i opened 7 different sql analyzer windows, and run on each window one of the 7 queries,and the result was the same as i was running it from my asp page, more time than the combined query. sorry for telling you such long story, but i want to understand why it take more time than usual, and if the idea of multithreading queries is a good one or not. thnx bye, M.M.Faramawi

if you are running one or more large aggregate queries, it probably has sort or hash operations in the execution plan, which at some point spills into tempdb (does this show high disk activity?) if so, then a better disk could help
if your queries are predominately based on time periods, is the table clustered on time?, that could help

]]>