VB.NET & SQL Server 2012 : thread 1 writing to a table while thread 2 reads from the same table

James Stanich 0
Viewed 26

I have a VB.NET application with two separate background workers.

BG1 connects to a stored procedure, which, as the script progresses, writes out information to Table A.

BG2, contained within a System.Windows.Forms.Timer, in the same application, will retrieve the records from Table A and display them in a ListView.

Table A schema:

CREATE TABLE IMPORT.DBO.XXX_ANALYSIS_UPDATES 
(
    ID INT IDENTITY(1, 1) NOT NULL, 
    [GROUPING] VARCHAR(500) NOT NULL, 
    SOURCE VARCHAR(500) NOT NULL, 
    TARGET VARCHAR(500) NOT NULL, 
    [TYPE] VARCHAR(500) NOT NULL, 
    ACTION VARCHAR(500) NOT NULL, 
    COUNTS INT NOT NULL
)

When I run this, it is taking over one minute for the below query to execute

SELECT * 
FROM IMPORT.DBO.XXX_ANALYSIS_UPDATES

It's likely this is because I am constantly writing to the table IN BG1, while trying to extract the data in BG2.

Is there a way to get around this lag so I can report on the "progress" from Table A in the VB.NET ListBox "while" inserts are being made into Table A?

Table A, at the most, will contain 100 records.

sql-server vb.net
1 Answers

Sometimes, you stumble across the answer...

Basically, I had to have a separate connection to the same database in the BG and everything works exactly as expected.

James Stanich0
Related