I have a development site which is failing due to exceeding database connections.  My SQL Data Provider is using the SQLHelper class in all methods.  So connections are in theory being closed when they should, being opened when they should.

This is a threading application which searches a database table for parts.  That is, there are clients who have flat file data being loaded into a table and if they're selected to be searched, they are.  We can search 3 with no trouble, once we add on 4 or more, we have issues.

For each client, a thread is started, so the same search information is being done for each client selected.  This may be the issue.  Can it be that a bottleneck is created by searching for the same part number information or text description of a part, across several different providers? (clients)  How could this be proven with some sort of database troubleshooting code?

If this is the case, I can see where the error returned would be a connections issue but actually be that 5 or more providers searching the same column (part number or part description) of the same table at the same time, could cause the issue.

I'm not a DBA but I think my line of reasoning is probably right.  If this is a correct guess, having a temp table or a CTE created for each thread would be the answer.  But I don't want to just try this out without proving it is the correct plan of attack.

I'm looking for input from anyone who's run into this situation before.

More data is, the table in question does have 2 million + rows.  Only one provider has by far the majority of these and here could be another bottleneck:  One provider alone has 2 million records give or take a few thousand.  The others have far less.  Count of records in the table currently is 1,723,004.  The largest provider has 1,624,487.  The others are 3,989; 4,326; 12,352; 48,092; 90.

It sort of makes sense that with threading, the largest supplier would be open the longest while the others would open and close far quicker.  This could be a clue.

OK, 64K clams question: Who has run into this sort of thing before and does a Temp Table for each Provider or a Common Table Expression for each provider sound like the logical answer?  I'm tempted to go ahead and do this anyhow as I'm sure it will allow each connection to work on a separate table with only that provider's data in it.  The advantage of a Temp Table or CTE is that when it's use is finished, it is too.  Neither are persisted.

I may have answered my own question here, so it could just be I need a 2nd set of eyes.  I can email code to any responders so that the situation can be fully explored and the exact answer found, rather than trial and error being used.

I'd really appreciate some help and I think this is an opportunity for any involved to learn from participation.

Thanks,

Mike