Alpha DevCon 2018
Results 1 to 19 of 19

Thread: Speed, saved queries, and entering data

  1. #1
    Member
    Real Name
    Dennis Mathias
    Join Date
    Aug 2000
    Posts
    231

    Default Speed, saved queries, and entering data

    I have a now fairly large (12,000 record x 50,000 record) database. It's a parent and one to many child arrangement. Of course when I enter records into the child table--it's really slow. But when I query on the last 1000 records and save that query and execute it before I enter data, it can be blazingly fast during child entry. Unfortunately, there is no guarantee that the data gets written (for some reason) and data is lost. Or it ends up with a totally different record. Very mysterious.

    Has anyone seen this before? What about having an open query and entering records in that state? I did it in A5V5 and never had any problem. Now A5V11--yikes. I do recall reading something a while back regarding this..but can't find it now.

  2. #2
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default Re: Speed, saved queries, and entering data

    Hello Dennis,

    Does your application still work in V5? Or have you rewritten the application in V11.

    If you have the V5 application copy, make a copy of V5 application. Open up the V5 copy in V11, and compact the application. Now open the compacted application in V11 and see if it does what you want it to do. If you made the sets correctly in V5 they should work in V11 if the application is the same. Have you changed your data entering form from its original design?

    Regards,

    Ed

  3. #3
    Member
    Real Name
    Dennis Mathias
    Join Date
    Aug 2000
    Posts
    231

    Default Re: Speed, saved queries, and entering data

    Yes, the V5 works fine but I have started making cosmetic changes to the V11 model. There's very little 'code' in this app. It RUNS fine as long as you have no saved queries in effect. I have compacted and do so regularly since sometimes the indexes get wild. The sets are very straight forward..

    I should point out that I didn't 'rewrite' the application since most elements work fine. I have a couple of really simple drop downs that are agonizingly slow. I don't know why that is. The data entering is the same.

    Could be the network..which I don't control and wouldn't know if that's impacting the operation. Maybe there's a metric I could use for that--but don't know what it would be.

    As I said, if I do a query on a group of the parent records then the app appears/feels REALLY fast. It's just that when you get done with a record it may or may not retain the child records entered and that's scary business. Sometimes, I don't know where those go. Other times they are applied to an older record..like way old. And that's scary too.
    Last edited by Dennis Mathias; 06-26-2013 at 10:42 AM. Reason: I wasn't clear..

  4. #4
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default Re: Speed, saved queries, and entering data

    Hi Dennis,

    From the way you are describing things, it would be beneficial to send in a stripped down copy of your application. Make a copy of it, and take out the data if needed. Upload it here. That way the message board can take a look at the structure and help you out.

    How do you have it networked? A dedicated server, with the application on it? Are you using shadowing, where your forms, etc are in each runtime PC? Sounds like you have different things happening not just one.

    Regards,

    Ed

  5. #5
    Member
    Real Name
    Dennis Mathias
    Join Date
    Aug 2000
    Posts
    231

    Default Re: Speed, saved queries, and entering data

    I'll see if I can get to sending in a copy of the app. Taking out the data would not be a good idea because with a small table application its fast. It is networked on a server and the application is local on each machine via runtime. No shadowing..I could and have but I'm hesitant since it could be a liability. Forms are pulled off the server..to each runtime PC.

    Wouldn't surprise me a bit to find that I have a couple of different issues. The forms load almost instantly. It's just the data input that is slow.

  6. #6
    Member
    Real Name
    Jacob
    Join Date
    Mar 2013
    Location
    Florida
    Posts
    60

    Default Re: Speed, saved queries, and entering data

    Quote Originally Posted by Dennis Mathias View Post
    Could be the network..which I don't control and wouldn't know if that's impacting the operation. Maybe there's a metric I could use for that--but don't know what it would be.
    This might not apply, but it's the first thing I think of when someone says data loss with file shared ISAM databases.

    Are you by any chance using Windows Vista/7 or Sever 2008/2008R2? The data loss / wild index problems sound ever so like some of the bugs with SMB 2. Microsoft has some hotfixes, why they haven't put these out as updates I don't understand.

    If so, take a look here for a guide to what hotfixes you might need: http://www.caseware.com/support/caseknowledge/kb-271

    There's another fix here that will turn off some more SMB 2 caching if you still have problems: www.alaska-software.com/fixes/smb2/overview.shtm

  7. #7
    Member
    Real Name
    Dennis Mathias
    Join Date
    Aug 2000
    Posts
    231

    Default Re: Speed, saved queries, and entering data

    Quote Originally Posted by jacobe View Post
    This might not apply, but it's the first thing I think of when someone says data loss with file shared ISAM databases.

    Are you by any chance using Windows Vista/7 or Sever 2008/2008R2? The data loss / wild index problems sound ever so like some of the bugs with SMB 2. Microsoft has some hotfixes, why they haven't put these out as updates I don't understand.

    If so, take a look here for a guide to what hotfixes you might need: http://www.caseware.com/support/caseknowledge/kb-271

    There's another fix here that will turn off some more SMB 2 caching if you still have problems: www.alaska-software.com/fixes/smb2/overview.shtm

    Uh, yes we are. The reason I went to V11 was because we updated to Windows 7. That is when the problem started but of course I blamed V11 and not Win 7.

  8. #8
    Member
    Real Name
    Jacob
    Join Date
    Mar 2013
    Location
    Florida
    Posts
    60

    Default Re: Speed, saved queries, and entering data

    Quote Originally Posted by Dennis Mathias View Post
    Uh, yes we are. The reason I went to V11 was because we updated to Windows 7. That is when the problem started but of course I blamed V11 and not Win 7.
    Well, I would say give the hotfixes a try and let us know how it goes!

  9. #9
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,890

    Default Re: Speed, saved queries, and entering data

    I think you can really help your self with shadowing the app. Once shadowed, all that is needed is a data stream which may not be that large as compared to both data and all the other. Just copying files does not help. The runtime knows if it is NOT shadowed and auto looks at the server folder.

    Index issues? I would like to see a copy of the indexes you have. I have found many issues that could be fixed pretty easy and some not so easy.

    Remember, with no shadowing, all indexing has to be on the server. If you have several users, it compounds.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  10. #10
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,925

    Default Re: Speed, saved queries, and entering data

    I second Dave's suggestion - shadow it.

    I would be interested in whether anyone has applied and tried the "SMB" solution. I tried it maybe two years ago, and it doubled the time for everything - slowed significantly.

    Also, interestingly enough, that client has 667 tables, with about 590 of them having indexes, and I have never run into index corruption or data loss.

    My warning/alert is that going from V5 to V11 is a super-major jump. The odds that everything will work are slim to none. I suspect that that is the issue, or the app itself, the coding, etc.
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  11. #11
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,890

    Default Re: Speed, saved queries, and entering data

    As per Martin.

    I jumped from 5 to 7 and it worked ok. When I went through all the code(written and other), it speeded up a great deal. When taking that same 40 table app to v11, I had the same experiences.

    I don't have index issues because they are simply used as indexes, not filters or barriers and all are 10 characters or less. It is possible for index corruption in any app if there is errant code too though. That is why I asked for a snapshot of the indexes.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  12. #12
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,925

    Default Re: Speed, saved queries, and entering data

    I think the "corruption" the one fellow is talking about is due to "opportunitistic record locking." If that is the cause, then index names and types would be irrelevant. It assumes a client-server arrangement, which Alpha dbf is not.

    PS: the indexes are maintained on the server, whether shadowed or not.
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  13. #13
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,890

    Default Re: Speed, saved queries, and entering data

    It assumes a client-server arrangement, which Alpha dbf is not.
    This is why shadowing.


    My reference to indexes was not aimed at the corruption, but at the speed issue. I just did not make it clear.

    Going back a bit. Why would the shadow copy over the *.cdx files if there was no bearing at the user's shadow? I just would like to know?
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  14. #14
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,925

    Default Re: Speed, saved queries, and entering data

    they are pointers - look at their size

    think about it - unless the indexes are maintained on the server, then there would definitely be corruption in a multiuser environ, whether shadowed or not
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  15. #15
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,993

    Default Re: Speed, saved queries, and entering data

    I don't find any cdx files in a shadow directory after network optimizing. They could be copied manually from the server or they would exist if you have local tables in the shadow directory.
    There can be only one.

  16. #16
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,890

    Default Re: Speed, saved queries, and entering data

    You are correct. My error. Since I always send a copy of the db in the shadow folder with my installs, I did not realize it was a waste.

    Thanks both of you.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  17. #17
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,925

    Default Re: Speed, saved queries, and entering data

    Yes - for many of the static tables that need to be shared, I copy from the server to the local shadow. (or to a user subfloder in T/S)

    all others really should not even be there.
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  18. #18
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,395

    Default Re: Speed, saved queries, and entering data

    Quote Originally Posted by martinwcole View Post
    Yes - for many of the static tables that need to be shared, I copy from the server to the local shadow. (or to a user subfloder in T/S)

    all others really should not even be there.
    Old thread but still relevant...
    How would someone go about this when the shadow is updated by Alpha? Because those tables would have to be copied from the server each time after optimizing.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  19. #19
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    3,999

    Default Re: Speed, saved queries, and entering data

    I'd put money on long index names.
    Have a look at the index names you are using.
    Anything over 8 char and any with a hint of possible matching and you could have trouble.
    V5 was a major change, V9 was also.
    If you originally built in V5, tweeked in V11, you have a problem. Different animals.
    V9, 10, 11 an 12 are similar enough to work together
    V5 to 11, no way.
    Ted Giles
    Example Consulting - UK
    .

    https://exampleltd.example-software.com/
    See our site for Alpha Support, Conversion and Upgrade.

Similar Threads

  1. Saving saved queries
    By CALocklin in forum Code Archive
    Replies: 5
    Last Post: 10-23-2007, 02:02 AM
  2. Saved Queries
    By Judith in forum Alpha Five Version 7
    Replies: 1
    Last Post: 07-03-2006, 10:53 AM
  3. Saved Ask Queries
    By David in forum Alpha Five Version 7
    Replies: 11
    Last Post: 06-28-2006, 03:06 PM
  4. Run Saved Query script does not find saved Queries
    By pk9 in forum Alpha Five Version 7
    Replies: 2
    Last Post: 03-06-2006, 06:41 PM
  5. Saved Queries
    By David Priest in forum Alpha Five Version 5
    Replies: 0
    Last Post: 01-08-2004, 03:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •