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.
Background
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.
Query
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')
Concern
This whole process takes about an hr to run. I am look to increase the speed that this whole process runs.
Question
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.
Thanks,
jimbo99999
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.
Background
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.
Query
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')
Concern
This whole process takes about an hr to run. I am look to increase the speed that this whole process runs.
Question
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.
Thanks,
jimbo99999
Kyle Abrahams, PMP
Are you looking for common part numbers in all databases or if they exist in any 2 databases you want them listed?
ASKER
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?
Thanks,
jimbo99999
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?
Thanks,
jimbo99999
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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.
Thanks,
jimbo99999
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.
Thanks,
jimbo99999
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)
etc.
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)
etc.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.