Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Add Function or Read next row using SQL

dontbedontbe Posts: 1Member
hi, all'of vb experts

i need some help here,.. right know i' managed and inventory Application, with huge records in it, an i'm using MDB as backEnd. In Form designer i have no trouble at all for viewing a 2 table (with 2 huge data in it).

The trouble i found, is when i create report using Data Report Designer. (My Company doesn't want to use Crystal Report, cause to expensive for them.) Ok, back to bussiness! the trouble i have is i want to compare beetween Field_2 in 1st row, with Field_1 in Second Row in same table. I'm using di query to do that:
[Code]
SELECT
I.`Date_Trs`, I.`Plu`, D.`Descp`, I.`Start_Stock`,
I.`Return_itm`, I.`Receipt_itm`, I.`Sales_itm`,
(I.`Plus_Correct` - I.`Minus_Correct`) AS Correction,
I.`Stock`,
IIF(I.`Stock` =
(SELECT T.`Start_Stock` FROM Master_Inventory
WHERE T.`Plu` = I.`Plu` AND T.`Tanggal` =
(I.`Tanggal` + 1)), 0, I.`Stock` -
(SELECT T.`Start_Stock` FROM Master_Inventory
WHERE T.`Plu` = I.`Plu` AND T.`Tanggal` =
(I.`Tanggal` + 1)
) AS Stock_Variance
FROM Master_Inventory I INNER JOIN MASTER_ITEM D
ON I.`Store_id` = D.`Store_Id` AND I.`PLU` = D.`PLU`
WHERE VAL(FORMAT(I.`Date_Trs`,'yyyymmdd')) >= 20070601 AND VAL(FORMAT(I.`Date_Trs`,'yyyymmdd')) >= 20070630
ORDER BY I.`Date_trs`, I.`PLU`
[/Code]

When my Report show, it took a long time to show the result. And made my pc become Not Responding. So please, if you guys know how to create Loop in Data Report Designer, or adding Custom function into it. Please tell me, I really need to solve this bug!.

please need help ASAP about this problem, Thank you and
forgive me about my bad english...

Comments

  • JonathanJonathan Posts: 2,914Member
    Hi,

    Seems you have a performance issue rather than a bug. And trying to make the database query run faster will help things. It's a heck of a query you've got there - a subquery within a subquery! :-)

    The first thing that comes to mind is - do you have any indexes on fields in the database? Candidates for fields to add indexes on would be any fields used in a JOIN, if they are not already primary keys. I got over 10x performance improvement on a query just a couple of days back by adding an index (that I shoulda put in there in the first place, but it performed fine until there was a lot of data). So I'd try that as your first port of call.

    Any luck?

    Jonathan
    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");
  • dokken2dokken2 Posts: 532Member
    another item you may want to try. instead of using a complicated query to join the two tables, you could create one or two temporary tables. append just the records you want based on the WHERE clause, then query off those tables.
    ex: if you have two tables with half-million records in each and you're able to insert/append 200K records into two temp tables, then you've reduced the number of records your query has to work on by 60 percent
  • lionblionb Posts: 1,688Member ✭✭
    : Hi,
    :
    : Seems you have a performance issue rather than a bug. And trying to
    : make the database query run faster will help things. [color=Blue]It's a heck of
    : a query you've got there - a subquery within a subquery! :-)[/color]
    :
    It does look different and I think slow down SQL performance. However he said that Form Grid databinding did not have problem. I think there is something wrong with DataReport databinding plus SQL performance.
Sign In or Register to comment.