Perfomance using threading

[Veerabhadraiah L M]

Hi,
I have two databases D1 with 6 million records and D2 with 95 thousand
records. I need to check Common records from these two databases based on
UserID and need to insert into other database D3 and also need to create XML
files.

For this i followed below approach.
I have used two threads.
-->one thread to Pick the users from D1, filter out against D2 and will be
inserting into D3.
In this thread iam fetching 5000 records everytime and for each user
Calling an event using delegate. in this event iam filtering against D2
database and inserting user into D3 database as follows.

Public void Run()
{
While(!Stop)
{
--Here fetching 5000 records ranging from 240000 to 290000 against D1
database (Querying D1 database)
-- calling a event using delegate for each Record.
-- Filtering each record against D2 database (Querying D2 database)
-- If exists Inserting into D3 database or if not exists then
returning loop to process the next record.
}
}

-->other thread fetches all the records from D3 database and writes to Xml
files.
-- this thread fetches the records say 1000(after filtering out) at one
time (Querying D3 database)
-- here also i have created delegate which calls an event for each record.
-- for each user the data will be written to xml file

Now i have an issue with this, its killing the perfomance. can anyone please
help me on this as how to improve performance.

Iam using .Net 1.1 for development and SQL 2000 as backend.

Thanks,
-Veera

 

[Scott Roberts]

Hi,
I have two databases D1 with 6 million records and D2 with 95 thousand
records. I need to check Common records from these two databases based on
UserID and need to insert into other database D3 and also need to create
XML
files.

For this i followed below approach.
I have used two threads.
-->one thread to Pick the users from D1, filter out against D2 and will be
inserting into D3.
In this thread iam fetching 5000 records everytime and for each user
Calling an event using delegate. in this event iam filtering against D2
database and inserting user into D3 database as follows.

Public void Run()
{
While(!Stop)
{
--Here fetching 5000 records ranging from 240000 to 290000 against
D1
database (Querying D1 database)
-- calling a event using delegate for each Record.
-- Filtering each record against D2 database (Querying D2 database)
-- If exists Inserting into D3 database or if not exists then
returning loop to process the next record.
}
}

-->other thread fetches all the records from D3 database and writes to Xml
files.
-- this thread fetches the records say 1000(after filtering out) at one
time (Querying D3 database)
-- here also i have created delegate which calls an event for each
record.
-- for each user the data will be written to xml file

Now i have an issue with this, its killing the perfomance. can anyone
please
help me on this as how to improve performance.

Iam using .Net 1.1 for development and SQL 2000 as backend.

Thanks,
-Veera

What kind of machine will this be running on? I would try to load all 95K
records from D2 into a hash table in memory so I never had to query D2
(that's going to be your bottleneck right there).

Also, is there a specific reason why you must save to D3, then re-load from
D3 and generate XML? Why not just generate the XML file at the same time you
insert into D3?

 

[Ignacio Machin \( .NET/ C# MVP \)]

Are both DBs in the same server?


If so y ou can do it directly in the server and just save A LOT of
processing.

 

[Ignacio Machin \( .NET/ C# MVP \)]

What kind of machine will this be running on? I would try to load all 95K
records from D2 into a hash table in memory so I never had to query D2
(that's going to be your bottleneck right there).

I would not do that. if both DBs are in the same server I would do the
entire query in the DB.

 

[Scott Roberts]

I would not do that. if both DBs are in the same server I would do the
entire query in the DB.

I guess I assumed he had some reason for not doing something that obvious in
the first place. Maybe that was a poor assumption on my part.

Assuming there is some reason to do the processing on the client, the OP
might also look into the SqlBulkCopy class for inserting a large number of
rows into D3 as well. If all he is doing is moving data, then yeah, using
Stored Procs or SSIS is definitely the way to go.

 

[Peter Bromberg [C# MVP]]

Even if the databases are on different servers, you should still be able to
perform this feat completely within SQL Server using only T-SQL; there should
be no need to pull all the data out to a client in order to do these
manipulations. You can even create Xml files with Transact SQL.
--Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com