Answered How to access database fast...

kushalbhatia169

New Member
hello everyone,
i have to make a database access fast which contains large data of unique index mstr_nbr how index how can i make it fast.

Code:
                DEFINE QUERY Mstr FOR mstr scrolling.
                OPEN QUERY Mstr FOR EACH mstr no-lock
                            where (Mstr_status = "close" or Mstr_status = "closed")
                            QUERY-TUNING(LOOKAHEAD CACHE-SIZE 32768 DEBUG EXTENDED).
                FOR EACH serial fields(serial_key serial_parent serial_pallet serial__chr06 serial__chr07 serial__chr08 serial__chr09 serial__chr10 serial__chr11 serial__chr12                             serial__chr12 serial__chr13
                 serial__chr14 serial__chr15 serial__chr16 serial__int02 serial__int06 serial__int07 serial__int08 serial__int09 serial__int10)
                  WHERE (serial_pallet = f_pallet AND serial_f_chr11 <> "BOX")
                           or (serial_key begins f_pallet)
                 NO-LOCK    BREAK BY serial_pallet by serial_parent by serial__chr11 QUERY-TUNING(LOOKAHEAD CACHE-SIZE 32768 DEBUG EXTENDED):
                    GET FIRST mstr.
                    define variable roID as rowid no-undo.
                    roID = rowid(mstr).
                    DO WHILE NOT QUERY-OFF-END('mst'):
                        for each det fields(detnbr detmodel detlot detqty) no-lock
                        where (detnbr = Mstr_nbr) and (detmodel = serial_parent and detlot = serial__chr11)
                        QUERY-TUNING(LOOKAHEAD CACHE-SIZE 32768 DEBUG EXTENDED):
                            tinspected = tinspected + detqty.
                        end. /* for each */
                        GET NEXT mstr.
                    END.
                    reposition mstr to rowid roID.
                end.
in which get first mstr taking 0ms
and get next mstr takes most of the time 0ms but sometimes takes 1ms
means in 180000 for each mstr runs 12000 runs takes 1 ms which is increasing the time and if no of serial loop increases every time 12000ms second also increases can anyone help this is a webspeed generated webpage how can i make it fast...anyone help
 
Last edited by a moderator:

Rob Fitzpatrick

ProgressTalk.com Sponsor
We don't have anywhere near enough information to answer your question. It may not even be possible to "make a database access fast" via forum posts.

You have not provided:
  • OpenEdge version;
  • platform type and version;
  • information about record size or counts in relevant tables;
  • information about the indexes on these tables;
  • information about database (databases?) involved, including their broker configuration;
  • information about the client(s) running the code, how they connect to the database(s), and how they are configured;
  • information about the workload of the database and database server;
  • performance metrics from the databases or WebSpeed agents;
  • whether OpenEdge DataServers and some SQL database are involved? (based on QUERY-TUNING);
  • information about the computers involved;
  • information about the storage hardware involved;
  • information about how WebSpeed is configured;
  • what "fast" means to you.
You have provided:
  • some code.
 

kushalbhatia169

New Member
We don't have anywhere near enough information to answer your question. It may not even be possible to "make a database access fast" via forum posts.

You have not provided:
  • OpenEdge version;
  • platform type and version;
  • information about record size or counts in relevant tables;
  • information about the indexes on these tables;
  • information about database (databases?) involved, including their broker configuration;
  • information about the client(s) running the code, how they connect to the database(s), and how they are configured;
  • information about the workload of the database and database server;
  • performance metrics from the databases or WebSpeed agents;
  • whether OpenEdge DataServers and some SQL database are involved? (based on QUERY-TUNING);
  • information about the computers involved;
  • information about the storage hardware involved;
  • information about how WebSpeed is configured;
  • what "fast" means to you.
You have provided:
  • some code.
-openedge version 11.3
-platform webspeed webtools version 3.1
-large tables approx 2lakh records each
-no info i have about database because i work for a company and this is not my project this is some other project my manager told me to do this and i can't say no but now I'm out of ideas
-sql server 2014
-fast means if my page will take 10sec to load i will be happy but it is taking approx 30-40 sec to load

index of mstr table--
index-name field-name

badgenew 1 badgenew Active
datenew 1 datenew Active
nbridx 1 nbr Unique Primary Active
pallet 1 pallet Active
proddesc 1 proddesc Active
prodline 1 prodline Active
status 1 status Active
type 1 type Active



indexes for table serial:
actual_prod_day 1 dte04 2 serial_chr01 Active
actual_prod_line 1 serial_pallet 2 serial_dte04 3 serial_chr01 4 serial_line Active
pallet_prod 1 serial_pallet 2 serial_dte04 Active
pallet_prod_line 1 serial_pallet 2 serial_dte04 3 serial_line Active

serial_chr01 1 serial_chr01 Active
serial_chr05 1 serial_chr05 Active
serial_chr06 1 serial_chr06 Active
serial_chr11 1 serial_chr11 Active
serial_chr14 1 serial_chr14 Active
serial_dte04 1 serial_dte04 Active
serial_int01 1 serial_int01 Active

serial_line 1 serial_line Active

serial_pallet 1 serial_pallet Active

serial_parent 1 serial_parent Active

serial_serial__key 1 serial_serial__key 2 serial_parent Unique Primary Active

-serial_pallet serial_key and serial_c11 all are character data type
 
Last edited:

TomBascom

Curmudgeon
On Stackoverflow you claimed it is SQL Server 6.03 (after claiming it was a Progress db). Here you are saying SQL Server 2014. Which is it?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
A table with 200,000 records is small and access to its data should be fast. To enable data access to be as fast as it can be, given external factors like system speed and configuration, queries need to be well written and index design needs to support those queries.

In this case, you have a query on mstr with field mstr_status in the where clause but there is no index with mstr_status as the leading component. This means each iteration of this query will scan the entire table instead of bracketing on the relevant records. This is an example of the type of analysis you can do. I'll leave analysis of the other queries as an exercise for you.

Note: this doesn't mean your performance problems are all related to code. I doubt that they are. Performance problems must be viewed holistically, factoring in all of the aspects I mentioned above, not just one or two.

It sounds like you don't have the information you need to attempt to solve this problem. I think your manager should hire a consultant rather than dumping this problem on you.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
A data-access driver may be versioned differently from the product it accesses.

Wikipedia shows that SQL Server 6.0 shipped in 1995. So you aren't using that version.
 

TomBascom

Curmudgeon
I'm sorry but you are making it really difficult by trickling out partial information and making apparently conflicting claims about your configuration.

If your database is a Progress database you do not need QUERY-TUNING and information about SQL Server drivers is a distraction.

On the other hand you do not appear to have a grasp on how your system is configured, how it operates, and what resources it really is using. You seem to have some access to the client side of things but little or no access to the server. I suspect that you are a programmer with little or no knowledge and experience with regards to the backend of this system and that someone has thrown this snippet of code at you without adequate preparation. I believe that you are probably doing the best you can. Unfortunately that results in wasting an awful lot of our time trying to pry details out of you only to end up with answers that are not very believable and which need significant double checking and explanation.

If this is really a Progress database then it is running on a server somewhere. On that server there is a log file. The file is named whatever the database name is plus ".lg". Assuming that the db is named "fred" the log file is "fred.lg". (Your actual db name is probably not "fred".) Someone should be able to find that log file and provide it to you. Get the log. Open it with any text editor. Find the *last* occurrence of the string "(333)". Then copy the next 200 lines of text and upload them. This will tell us all about how the database is configured and we can make some more reasonable suggestions.
 

kushalbhatia169

New Member
I'm sorry but you are making it really difficult by trickling out partial information and making apparently conflicting claims about your configuration.

If your database is a Progress database you do not need QUERY-TUNING and information about SQL Server drivers is a distraction.

On the other hand you do not appear to have a grasp on how your system is configured, how it operates, and what resources it really is using. You seem to have some access to the client side of things but little or no access to the server. I suspect that you are a programmer with little or no knowledge and experience with regards to the backend of this system and that someone has thrown this snippet of code at you without adequate preparation. I believe that you are probably doing the best you can. Unfortunately that results in wasting an awful lot of our time trying to pry details out of you only to end up with answers that are not very believable and which need significant double checking and explanation.

If this is really a Progress database then it is running on a server somewhere. On that server there is a log file. The file is named whatever the database name is plus ".lg". Assuming that the db is named "fred" the log file is "fred.lg". (Your actual db name is probably not "fred".) Someone should be able to find that log file and provide it to you. Get the log. Open it with any text editor. Find the *last* occurrence of the string "(333)". Then copy the next 200 lines of text and upload them. This will tell us all about how the database is configured and we can make some more reasonable suggestions.
okay tom I'm really sorry, i don't work for this project just got work for this I'll ask my manager for the log file, I'm really sorry for wasting your precious time.
 

TomBascom

Curmudgeon
This is where things are with the code I have suggested over on StackOverflow:
Code:
define temp-table tt_mstr
  field mstr_nbr as integer                /* or whatever the proper data type is */
  index mstr_nbr-idx is primary unique     /* I am assuming mstr_nbr is unique    */
.

for each mstr no-lock
   where mstr.mstr_status = "close"
      or mstr.mstr_status = "closed":

  create tt_mstr.
  tt_mstr.mstr_nbr = mstr.mstr_nbr. 
     
end.

for each serial no-lock
   where ( serial_pallet = f_pallet and serial_f_chr11 <> "box" )       /* <> "box" is going to perform poorly, there may be better ways to do this     */
      or ( serial_key begins f_pallet ):

         /* break by serial_pallet by serial_parent by serial__chr11: ** this sort of pointless, you're just adding up "tinspected", the order and the break groups have no impact */ 
 
  for each det fields( detnbr detmodel detlot detqty ) no-lock
     where detmodel = serial_parent and detlot = serial__chr11:

    find tt_mstr where tt_mstr.mstr_nbr = detnbr no-error.      
      if available tt_mstr then
        tinspected = tinspected + detqty.              
   
  end.
 
end.

I am a bit confused about the fact that there is an indexed field in the mstr table called "nbr" and that there seems to *also* be a field in mstr called mstr_nbr. Likewise there is an index for "status" but not for mstr_status. I am suspicious that there is really only one field and that in an effort to obfuscate the code "." has been replaced by "_" in places where it should not have been. If that is true, and "nbr" and "status" are the real field names then the following code eliminates the temp-table:
Code:
for each serial no-lock
   where ( serial_pallet = f_pallet and serial_f_chr11 <> "box" )
      or ( serial_key begins f_pallet ):

  for each det fields( detnbr detmodel detlot detqty ) no-lock
     where detmodel = serial_parent and detlot = serial__chr11:

    find mstr no-lock where ( status = "close" or status = "closed" ) and nbr = detnbr no-error.
    if available tt_mstr then
        tinspected = tinspected + detqty.              

  end.
 
end.

Note: if it does turn out that the field names were improperly shown due to an attempt at code obfuscation let that be a lesson regarding why you should not do stuff like that. Show the code. The real code. Not some fake paraphrased code. Show the errors. The real errors. All the errors. The actual error text. Not some fantasy error that never actually happened.
 

kushalbhatia169

New Member
This is where things are with the code I have suggested over on StackOverflow:
Code:
define temp-table tt_mstr
  field mstr_nbr as integer                /* or whatever the proper data type is */
  index mstr_nbr-idx is primary unique     /* I am assuming mstr_nbr is unique    */
.

for each mstr no-lock
   where mstr.mstr_status = "close"
      or mstr.mstr_status = "closed":

  create tt_mstr.
  tt_mstr.mstr_nbr = mstr.mstr_nbr.

end.

for each serial no-lock
   where ( serial_pallet = f_pallet and serial_f_chr11 <> "box" )       /* <> "box" is going to perform poorly, there may be better ways to do this     */
      or ( serial_key begins f_pallet ):

         /* break by serial_pallet by serial_parent by serial__chr11: ** this sort of pointless, you're just adding up "tinspected", the order and the break groups have no impact */

  for each det fields( detnbr detmodel detlot detqty ) no-lock
     where detmodel = serial_parent and detlot = serial__chr11:

    find tt_mstr where tt_mstr.mstr_nbr = detnbr no-error. 
      if available tt_mstr then
        tinspected = tinspected + detqty.         

  end.

end.

I am a bit confused about the fact that there is an indexed field in the mstr table called "nbr" and that there seems to *also* be a field in mstr called mstr_nbr. Likewise there is an index for "status" but not for mstr_status. I am suspicious that there is really only one field and that in an effort to obfuscate the code "." has been replaced by "_" in places where it should not have been. If that is true, and "nbr" and "status" are the real field names then the following code eliminates the temp-table:
Code:
for each serial no-lock
   where ( serial_pallet = f_pallet and serial_f_chr11 <> "box" )
      or ( serial_key begins f_pallet ):

  for each det fields( detnbr detmodel detlot detqty ) no-lock
     where detmodel = serial_parent and detlot = serial__chr11:

    find mstr no-lock where ( status = "close" or status = "closed" ) and nbr = detnbr no-error.
    if available tt_mstr then
        tinspected = tinspected + detqty.         

  end.

end.

Note: if it does turn out that the field names were improperly shown due to an attempt at code obfuscation let that be a lesson regarding why you should not do stuff like that. Show the code. The real code. Not some fake paraphrased code. Show the errors. The real errors. All the errors. The actual error text. Not some fantasy error that never actually happened.
Sorry that was a code from someone else project i was not sure that i can share the project code in any online forum so i changed tables name little bit then i made a blunder because of enormous pressure because i'm not much familiar to this project and little bit confused I'm out ideas may be that's why
Mstr_ is in start of every field name
Index. Fields
badgenew 1 Mstr_adgenew Active
datenew 1 Mstr_datenew Active
nbridx 1 Mstr_nbr Unique Primary Active
pallet 1 Mstr_pallet Active
proddesc 1 Mstr_proddesc Active
prodline 1 Mstr_prodline Active
status 1 Mstr_status Active
type 1 Mstr_type Active

Except this mistake every info i provided is true and correct with little bit changed names. in starting i was thinking about making a efficient query i thought my code has a problem because i usually work for development but first time got this analysis of progress database work and also for unknown project(my manager) i know i made a mistake I'm also frustrated just working on a thing which i don't know..
 
Last edited:

TomBascom

Curmudgeon
So the fields are actually mstr_nbr and mstr_status?

Then it should be possible to take the most recent version of the revised query, without the temp-table, and run it. And that code ought to perform quite a bit better at the task that you have shown us - namely counting up the total of inspected items items.
 
Top