VB.Net - SQL Query in my Project

Good Day Experts!

I have a inquiry on how to improve the speed of the queries in my little project.  But here is a little background information on what is going on in the project.


Each Customer has their own database.  I have to look for common PartNumbers between all of the databases.  So, I have a DataTable for the Outer and Inner loops listing all the databases. Each time through the Outerloop, I go through the all databases on the Innerloop looking for common PartNumbers. Then I increment the Outerloop and go through all the databases on the InnerLoop look for common PartNumbers.  I do this until I have gone through the entire Outerloop list.  

Currently I am doing the following query each time:
Select PartNumber from [Customer B].dbo.[Processing Table] where PartNumber in
(Select PartNumber from [Customer A].dbo.[Processing Table] where [Process Week] = '10/4/2013')

Then the next time through the loop:
Select PartNumber from [Customer C].dbo.[Processing Table] where PartNumber in
(Select PartNumber from [Customer A].dbo.[Processing Table] where [Process Week] = '10/4/2013')

Then the next time through the loop...and so on till I go through all 30+ databases:
Select PartNumber from [Customer D].dbo.[Processing Table] where PartNumber in
(Select PartNumber from [Customer A].dbo.[Processing Table] where [Process Week] = '10/4/2013')

This whole process takes about an hr to run. I am look to increase the speed that this whole process runs.  

Is there a better way to structure my query to make it faster since I have to run it repetitively?

Please ask questions if I need to provide more information.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Are you looking for common part numbers in all databases or if they exist in any 2 databases you want them listed?
Jimbo99999Author Commented:
This  sounds weird, but I am not quite sure how to seperate the 2 scearios in your question.

CustomerA   CustomerA
CustomerB   CustomerB
CustomerC   CustomerC
CustomerD   CustomerD

See if PartNubmers in CustomerA are in CustomerB, CustomerC, CustomerD.
Then see if PartNubmers in CustomerB are in CustomerA, CustomerC, CustomerD.
Then see if PartNubmers in CustomerC are in CustomerA, CustomerB, CustomerD.
Then see if PartNubmers in CustomerD are in CustomerA, CustomerB, CustomerC.

What do you think?

Kyle AbrahamsSenior .Net DeveloperCommented:
So if CustomerA has a part and Customer B has a part you need to know about it (even if customer C doesn't).

How bout something like:
--c = customer id, p = part number.
select 1 c, 'A' p  into #t1

insert into #t1 
select 2, 'A'  

insert into #t1 
select 3, 'B'  

select p, count(*) from #t1
group by p
having (count (*) > 1)

Open in new window

this will tell you the parts that have duplicates . . . you can then query which parts belong to which customers, but at that point you're doing it set based.

I'm assuming you're copying the data over to a new table, if not I would recommend normalizing the data in a common tbale.  

Something like:

insert into MyTable select 'CustomerA' as Customer, * from [Customer A].dbo.[Processing Table]  where [Process Week] = '10/4/2013'
--repeat with other customers.

Then you can do your comparisons on this table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jimbo99999Author Commented:
"So if CustomerA has a part and Customer B has a part you need to know about it (even if customer C doesn't)"

Yes, CustomerA to CustomerB then to CustomerC then to CustomerD...etc, indepently of each other.  Right now, in my InnerLoop I am resetting the ConnectionString to the database for each Customer.  I do have that bit of performance time but stepping through running the queries is taking the most time.

Kyle AbrahamsSenior .Net DeveloperCommented:
I would recommend you create a stored procedure which does this.

Get the data into a local table on your server from all the customers once.

Then use the query I provided here to get the duplicates.

It would query each table one time and then do a mass compare at the end.

If you really wanted to continue on the path you have note that you would only need to query going forward.

EG:  Customer A needs to check B, C, D  
       Customer B only needs to check C & D (as you already checked A in the one above)
I would +1 to the above suggestion. Also, make sure you define appropriate indexes on the tables involved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.