First things first: your code is susceptible to SQL Injection. Already covered in other answers, so I will not rehash.
Second thing on this list is that your VB code is chit-chatting with the SQL Server way too much; you need to cut down the amount of SQL Commands you are executing back to back
Third thing on this list is a recommendation of checking your database schemas; the item of interest are
INDEXes, have they been created and are you
indexing the right things relative to this query.
And now for my recommendations
Stop working on this block of code in Visual Studio. Install Sql Server Management Studio and use the tools within it, such as
Include Actual Execution Plan; to fine tune the queries and to see what INDEX work needs to be done.
You can also do some research into using
joinssubqueries in concert to see what needs to be done to utilize this. AND your set of queries can be done this way.
Once you have your query issues worked out, I would then place all of the queries into 1
Stored Procedure. This will compile all of the queries into one execution plan and optimize them once- which will make the batch run quicker and will only need one call from VB which will reduce chit-chat between servers.
I am not going to rewrite all of this for you as I don't have your schemas and this is something you should learn through research and working with the tools. BUT you may end up with something like this (which just may work for ya as is)
CREATE PROCEDURE dbo.AggregateValues (
@ProdID NVARCHAR(16)
) AS
BEGIN
SELECT p.ProdID
, GRNQty = ISNULL(SUM(g.Qty),0)
, Qty = ISNULL(SUM(o.Qty_FullFill)
, CrdQty = ISNULL(SUM(c.Qty),0)
, DbtQty = ISNULL(SUM(d.Qty),0)
FROM (SELECT ProdID = @ProdID) p
LEFT OUTER JOIN GRNTable g ON p.ProdID = g.Prod_ID AND g.GRN_Type='Local'
LEFT OUTER JOIN Order_tbl o ON p.ProdID = o.Prod_ID
LEFT OUTER JOIN CrditNote_tb c ON p.ProdID = c.Prod_ID
LEFT OUTER JOIN DebitNote_tb d ON p.ProdID = d.Prod_ID
END
GONaturally you will get to rework your VB to accommodate this change
Here are the basics on calling this new Stored Procedure, and it is in C# so you will need to transcode it into VB but that should help you understand what is going on; and is mostly vernacular changes so it should be relatively easy. Take note of
cmd.Parameters.Add... line, as that is the proper way to add variables into a SQL command to mitigate the SQL Injection Vulnerability
SqlCommand cmd = new SqlCommand("AggregateValues", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProdID", strProdId);
SqlDataReader dr = cmd.ExecuteReader();
if ((dr != null) && (dr.Rows.Count > 0)) {
Submit an article or tip