I'm working on a C# (2005) app that requires shooting a large number of queries via Odbc, wait until the last one is done and then compute an overall result.
Since the queries are not interdependent, I would like to get them to run concurrently on multiple threads.
I did manage to get several queries run on different threads but the response time for the same query varies dramatically when another query is running concurently (even on a different ODBC connection). All is as if something was queing those queries regardless of the threads and/or the connection objects being used.
Basically when I run a very simple query q1 (thread 1 connection1) on its own, the result is instantaneous, but if I first start a big query q2 (thread 2, connection 2) and then q1 while q2 is running q1 takes forever.
Is it possible at all (for example, does the Odbc driver support being used that way)
Any suggestion as to how to handle that problem would be appreciated.
MM
I do not see why it could not be done. Miltithreading is basically the same as multiple connections from different clients at same time and it is definitely allowed. First it is quite possible that your first thread blocks another one until it finishes execution. In this case second block is doing nothing and just waits. Another potential issue is your big query is so *heavy* for the server that sever just uses 100% of its resources for this query. But I am doubt that this is the case|||Are you using (nolocks) on your tables?
Select * from Customers (nolock)
Where yada yada yada
If you are not, one query is locking the table until the connection closes.
This is how it works:
1. Query1 --> Select * from Customers
Customers Table is locked until connection closes|||Yes, I have no doubt that from a server standpoint there is no problem.
The question was more whether multiple queries running simultaneously from the same client machine may be a problem.
For example what if the ODBC driver queues the queries.
I read somewhere that depending on the provider some drivers may or may not support multithreading but have no ide whether it's true or if it has anything to do with my problem.
Thanks anyway,
MM
|||Adamus,
Good thought, it may be the cause of my problem.
I did try shooting the queries at different tables and it seemed to work fine.
a- Now, for the (nolock); I believe this is a SQL Server specific syntax, do you know if there's a generic way to issue a 'dirty read' through ODBC without being tied to the syntax of particular vendor/DB ?
Would starting a transaction from the ODBCConnection used for the command and set its isolationlevel to 'UncommitedRead' work ?
b- Assuming there's a way of doing a dirty read. Any suggestion as to how best handle the following ? :
- Do first select and get a list of values
- For each of the values issue a scalar command and get the result
- Once all the results are collected, do something with them and display the final result.
The trick of course is to create as many threads are scalar commands to be issued since it would improve perfs to run them in parallel. But it has to be done dynamically since we don't know how many we'll get. Then we need the ability to tie everything back together. Not exactly obvious to do, at least to me.
Thanks,
MM
|||ODBC? Kind of unfamiliar ground for me unfortunately. (I'm more of a SQL guy)The first thing that comes to mind is .LockType
...but good luck :)
Adamus
|||.Locktype ? Where did you find that property ? On which class ?
I can't find it in the doc.
Thanks,
MM
|||LockType = adLockOptimistic
google will give you some good material on this.
http://support.microsoft.com/default.aspx/kb/281998
Adamus
