How to execute query fast within multile loops

  • Thread starter Thread starter How to execute query fast within multile
  • Start date Start date
H

How to execute query fast within multile

hi there.

i am developing a web application in C# with Access Database.

i and using 5 inner loops for executing multiple sql query for calucate
group by sum from my fifferent-2 transactions table. means my loops is
running 16000 times or more. it is taking a to much time for exucuting all
the query.
actually i have also used temporary datatable for this perpose after fill
all data in this temporary table.
like
DataTable
dtAllRecord;
dtAllRecord =
FillDataTable();
object sumObjectQty;
sumObjectQty = dtAllRecord.Compute(
"Sum(TotalPieces)", "Trans_Date<#" + strDatestart + "# and Type='Op' and
WH_ID = '" + tempWhlist[w1].WarehouseID + "' and Product_ID = '" +
strProductId + "' and Thickness_JointValue = '" +
tempThicklist[t1].JointValue + "' and Width_JointValue = '" +
tempWidthList[t2].JointValue + "' and Length_JointValue = '" +
tempLengthlist[t3].JointValue + "' and Grade_ID ='" +
tempGradelist[t4].GradeID + "' and Trans_ID LIKE '%" + TempCategnameOpStock +
"%'");
is there any other way to fast execution of query.

thanks in advance
(e-mail address removed)
 
Avoid looping with your code when the computed element of the loop(s) is an
SQL statement, mainly if your SQL statement can DO the loop itself, such as
using a GROUP BY clause:

SELECT SUM(totalOfPieces), ProductID
FROM AllRecords
WHERE TransDate <= DateStart
AND ...
GROUP BY ProductID


will avoid a loop over all possible ProductID values, as example, by
returning a set of records supplying a ProductID and the SUM associated to
it.


Vanderghast, Access MVP



"How to execute query fast within multile" <How to execute query fast within
(e-mail address removed)> wrote in message
news:[email protected]...
 
its neccesory to use looping.......how to reduce the execution time of query

Michel Walsh said:
Avoid looping with your code when the computed element of the loop(s) is an
SQL statement, mainly if your SQL statement can DO the loop itself, such as
using a GROUP BY clause:

SELECT SUM(totalOfPieces), ProductID
FROM AllRecords
WHERE TransDate <= DateStart
AND ...
GROUP BY ProductID


will avoid a loop over all possible ProductID values, as example, by
returning a set of records supplying a ProductID and the SUM associated to
it.


Vanderghast, Access MVP



"How to execute query fast within multile" <How to execute query fast within
(e-mail address removed)> wrote in message
hi there.

i am developing a web application in C# with Access Database.

i and using 5 inner loops for executing multiple sql query for calucate
group by sum from my fifferent-2 transactions table. means my loops is
running 16000 times or more. it is taking a to much time for exucuting all
the query.
actually i have also used temporary datatable for this perpose after fill
all data in this temporary table.
like
DataTable
dtAllRecord;
dtAllRecord =
FillDataTable();
object sumObjectQty;
sumObjectQty = dtAllRecord.Compute(
"Sum(TotalPieces)", "Trans_Date<#" + strDatestart + "# and Type='Op' and
WH_ID = '" + tempWhlist[w1].WarehouseID + "' and Product_ID = '" +
strProductId + "' and Thickness_JointValue = '" +
tempThicklist[t1].JointValue + "' and Width_JointValue = '" +
tempWidthList[t2].JointValue + "' and Length_JointValue = '" +
tempLengthlist[t3].JointValue + "' and Grade_ID ='" +
tempGradelist[t4].GradeID + "' and Trans_ID LIKE '%" +
TempCategnameOpStock +
"%'");
is there any other way to fast execution of query.

thanks in advance
(e-mail address removed)
Click to expand...
Click to expand...
 
Your loops are inefficient. It is like: open Excel, open a worksheet, read
cell A1, close Excel, open Excel, open a worksheet, read cell A2, close
Excel, open Excel, open a worksheet, read cell A3, ... Instead, it is easy
to see that reading a RANGE of cells would allow us to open Excel just ONCE,
and then, we would loop on the array of values we would have got. Same thing
in your case with your Compute( Sum ... ), which is like VBA-DSum( ).
Instead of doing it for one case at a time, should do it ONCE, for each
possible values, and then, loop over the result you would get.




Vanderghast, Access MVP


"How to execute query fast within multile"
its neccesory to use looping.......how to reduce the execution time of
query

Michel Walsh said:
Avoid looping with your code when the computed element of the loop(s) is
an
SQL statement, mainly if your SQL statement can DO the loop itself, such
as
using a GROUP BY clause:

SELECT SUM(totalOfPieces), ProductID
FROM AllRecords
WHERE TransDate <= DateStart
AND ...
GROUP BY ProductID


will avoid a loop over all possible ProductID values, as example, by
returning a set of records supplying a ProductID and the SUM associated
to
it.


Vanderghast, Access MVP



"How to execute query fast within multile" <How to execute query fast
within
(e-mail address removed)> wrote in message
hi there.

i am developing a web application in C# with Access Database.

i and using 5 inner loops for executing multiple sql query for calucate
group by sum from my fifferent-2 transactions table. means my loops is
running 16000 times or more. it is taking a to much time for exucuting
all
the query.
actually i have also used temporary datatable for this perpose after
fill
all data in this temporary table.
like
DataTable
dtAllRecord;
dtAllRecord =
FillDataTable();
object sumObjectQty;
sumObjectQty = dtAllRecord.Compute(
"Sum(TotalPieces)", "Trans_Date<#" + strDatestart + "# and Type='Op'
and
WH_ID = '" + tempWhlist[w1].WarehouseID + "' and Product_ID = '" +
strProductId + "' and Thickness_JointValue = '" +
tempThicklist[t1].JointValue + "' and Width_JointValue = '" +
tempWidthList[t2].JointValue + "' and Length_JointValue = '" +
tempLengthlist[t3].JointValue + "' and Grade_ID ='" +
tempGradelist[t4].GradeID + "' and Trans_ID LIKE '%" +
TempCategnameOpStock +
"%'");
is there any other way to fast execution of query.

thanks in advance
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top