jwcolby
jwcolby at colbyconsulting.com
Wed Dec 7 10:00:52 CST 2011
I have a half dozen tables which I need to get counts of valid records for. Additionally I need to get counts where the two tables join on a common field - HashPerson, HashFamily and HashAddress. We have written a process in C# which dynamically generates these count SQL statements, passes it off to SQL Server, gets the results back and populates a spreadsheet with the answers. As it happens there is an entire "table" or square area of the spreadsheet for the intersection (as I call it) counts for each of those hashes, IOW an entire section for HashPerson counts, another for HashFamily and another for hashAddress. Since I am working in C# and each Hash area is a class instance, and since I have threads available in C#, and since it was taking about 5 hours to run all these counts, I decided to thread it and run each Hash on it's own thread, IOW try and get the counts for the HashPerson, HashFamily and HashAddress "simultaneously". I have 12 cores and about 50 gigs of memory assigned to SQL Server so I thought *maybe* it would work. Well... it appears that at some point very early on, something locks the process and only one of the hash classes proceeds. When that one finishes then the other two process together. We are checking whether we are having deadlock issues in C# but I need to know whether SQL Server itself would lock the tables that the count was processing. So the question is, does SQL Server apply locks to a table when doing a count of the records in the table. Likewise if I join two tables and do a count on that, does that cause a lock to be applied to either or both of the tables. If so is there a syntax I can use to prevent this lock? I am doing a join potentially on two tables with as many as 50 or 100 million records in each table. These counts take a long time so having table locks applied is not a good idea in general. -- John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it