I worte a routine in VB.NET 2005 + SQL Server 2005 that operates on a
single
table. I loop through 10 "LEVELS". For each of these "LEVELS" I extract n
"PATTERNS" where n could be between 700 and 2000. For each of those
LEVEL+PATTERN, I select matching records and splite them up into two
DataSets
- "dataSetComeplete" and "dataSetEmpty". Based on deeper patterns found
in
"dataSetComeplete", I edit fields in "dataSetEmpty". Finally, I update
the
"dataSetEmpty", using a sqlAdapter and its updateCommand properties. This
update is done withn a sqlTransaction. Once the Update is successful, I
dispose the datasets and set them to nothing, before processing the next
LEVEL+PATTERN.
The program works - but slows down as the process continues, till it hangs
compeltely. On the task manager, I see that the Mem Usage keeps on
increasing till it increasese no more. I can manually select ant ONE
LEVEL
and the program works, though slowing down. Sometimes I can get through
two
LEVELS if the number of patterns are not large.
Here are my questions - I know without seeing the code it might be a bit
difficult - but this is a ADO forum and I am not expecting any help in a
VB.NET perspective.
1. From what I explained, does it feel that I am approaching the solution
in
a wrong way?
2. Can this algorith be implemented in T-SQL? I have never used Loops or
Cursors or Transactions and Rollbacks in T-SQL.
3. Is a CLR stored procedure a better approach?
4. For those of you who recommend T-SQL, what/which literature can you
refer
me to that shows such Iterations, Loops, Cursors, Updates, Transactions
etc.
Also, how would you monitor the process? There are no progress bars :) or
Threads.
I am totally stuck and need to run this process ASAP. Any help would be
much appreciated.
|
