Multi-User 100~300 new records per hour...locking error

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

In the help files there is a section called:
Program your own Autoincrement field
where a separate table is created with a single auto increment field using a counter loop & a sleep() function that makes you wait for a sec or two until the 'counter' table is free.

Should I be resorting to this method?

[DaveM]

Re: Multi-User 100~300 new records per hour...locking error

Assuming 1000 records per day and 300 days per year, you will have 1,500,000 records in a few years and evem more on child tables. Add the rapidity of insertion to that and several things comes to mind.

You should have this on a dedicated server(linux or), not peer to peer, and you should it to be using sql. I do not see a way around this with the speed needed and the numbers of records. You also should make sure your sets are optimized and your indexes need to be few and very specific. Memory should to be plentiful on all shadowed workstations and the server. Work stations would benefit from XP Pro. Also, a very fast network is advisable like a hardwired system using a switch or switches with nothing else on it but this app and no internet or other extraineous bandwidth robbers. All the reports should be made from a shadowed workstation. Server should be using a scsi drive or at least SATA drive. Reaons are the speed at which the data gets from the push of a button to the actual data being saved and returned for further input.

By putting it in 'edit mode', do you mean to make the form 'modeless' from the beginning?
Or do you mean to use the
parentform.allow_change(.T.)
parentform.Change_Mode()
to put it in change mode immediately after

The saving of a record and going to change mode can be done in 2 steps or more possibly eliminating the other issues. A button(onpush) to create a new record and with the same button(on leaving) save it, then put it into edit mode with a seperate with entering the next field if that field is always the same. If it is not always the same next field, you could utilize a hidden field that redirects focus somewhere else. Sometimes we have to cause alpha to do other than it wants by breaking it up into smaller bits which makes the whole much stronger and faster.

Most of this has already been mentioned directly or aluded to above by very learned people.

If you could possibly post your application here with some sample data so others can go through it to suggest changes or do the changes for you in samples to make it better.

Hope this helps you.

.

[G Gabriel]

Re: Multi-User 100~300 new records per hour...locking error

First:
3000 records in 4 hours at peak time, assuming 3 peaks (Bkfst, Lunch and Dinner) or even 2 peaks, that's 6000 records/peak time, say 8000/day all in all. You will have a million record in 4 months, 3 million in a year. Before long, you will be posting a thread about the maximums for db.

Second:
I am no expert in this, but my basic information might be a starting point for you. Although your app is not necessarily one, but perhaps at the low end of mission critical. With SQL, you can control record locking depending on what SQL you use. SQL will allow either pessimistic or Optimistic Concurrency and locking escalation. Perhaps, and since you are adding records to the child table and since most likely that's where the bottleneck is, perhaps you could use Optimistic Concurrency to prevent locking.

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

Hey Guys:
I'm glad I got some people interested in the topic. For I think its a critical one.
First of all, let me make a correction. The numbers I gave you are my peak hour numbers on my peak days. I am not always that busy. But I know I will be, as the company I feed is expanding. I did check the number of records in my "previous" system and they did exceed 1 million records / year.

In my previous system, I had a separate Receipts & Line-item SET for each POS workstation. So POS1, POS2, POS3, POS4.... all had separate tables:
P1_Receipts + P1_Trans = POS1.set
P2_Receipts + P2_Trans = POS2.set.... and so on.
Of course, with this design I never had a record locking problem. However, when it came to reports there were many problems. I had to create many sub-forms on my reports each summarizing the contents of each set & then combining them into one report. My reports sets became HUGE, and my calculated fields complex.

Also, each POS had its own auto increment receipt number & trans number....which isnt good because for tax purposes...the government wants a single sequencial number regardless of the number of POS's you have. Further, when my client wants to see the list of people I fed each day, I would have to give them 4 separate tables combined...not fun if you think that I will one day go up to POS7~10 possibly...

Then a programer from the clients IT department advised me that I should use one SET for all my POS's. He said that all my POS systems can write/append to a single SET, all at the same time. I was excited for this would make the reports much more easier to make. So I embarked on rebuilding the entire system, but now am have 'locking problems'.
As for upgrading my hardware, that is not an option I really want to entertain. Why? My system worked fine before, and even if its a bit slowe I believe there is a programical way to prevent the lock. I want to know how to do it. For one day I may need to design a system where even more records will be appended to a single SET at the same time than now. For example, someone like 'Google' or 'Wallmart' must have hundreds of records appended per second.
Im curious if at a given 'Wallmart' outlet a SET is assigned to each POS or if all 30~40 POS's are writing to one SET.... My guess is that all POS's in a given store are writing to one SET with one Auto-increment "Officail Receipt Number". CAN I HAVE YOUR OPINION ON THIS PLEASE?????? ARE THEY USING ONE SET?

Also, I know someone will say that 'wallmart' has a super-duper-internal-server at each location and dedicated this n that &.whatnot....however, there must still be a way to programically avoid 'locking' within A5 using less expensive hardware. I checked my Windows for Opportunistic Locking and my version of Windows XP does not even have it to disable. So I'm assuming it is disabled as a default.

Should I create separate SETs for each POS and draw the Receipt number from a single table with only the receipt number; And then create a Mapped table to combine all the tables into one? But what I really want is to have multiple POS's all writing to one SET without Locking.

[G Gabriel]

Re: Multi-User 100~300 new records per hour...locking error

I could assure you Walmart is not using dbf files, you could bet the farm on that. I could further assure you that WalMart probably has either Oracle or SQL 2008 or the like and quite possibly they are paying in the millions for their IT systems. You are using one of the largest IT systems in the world if not by far the largest database known to man as a comparison and if you attempt to emulate that with dbf, you are blowing in the wind. You can emulate it on a smaller scale of course using MYSQL or even one of the big SQL. Nice thing about SQL is scalaility.

An average WalMart will have 15-20 cashiers. If each cashier scans one item per second, that's over 1000 records per minute, 60,000 records per hour, somewhere around 1 million records per day! and that's just one store.

Are all these workstations connected to one database?
Yes.
How do I know?
Buy an item at WalMart and go straight to customer service and return it. Not only that, it's all connected to their main server. Buy an item from a WalMart, drive 10 or 15 minutes to the next WalMar and return it.

SQL offers scalability and much better table and record locking options than what Windows offer.

however, there must still be a way to programically avoid 'locking' within A5 using less expensive hardware.

Ask, what's his name, Rumsfelt. He will tell you. He tried to fight a war on the cheap and we all know what happened. Now ask McCain, he will tell you:
You need a surge!
You need SQL.

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

I think I got your point. Great analogy.
So are you recomending that I use A5 with a MySQL backend(which I have)?
Via Active Link tables?

[G Gabriel]

Re: Multi-User 100~300 new records per hour...locking error

That sounds about right.

[martinwcole]

Re: Multi-User 100~300 new records per hour...locking error

this is beginning to remind me of the Alpha 4 DOS days, that began to not work well once Windows came out, especially once they got to XP. I feel a grief for dbf's and it also makes me feel old!!!

But since I now have the new unlimited runengine, I guess I could start playing with the client server. How would I convert an existing "dbf" to an sql table?

[G Gabriel]

Re: Multi-User 100~300 new records per hour...locking error

There are several "converter" softwares out there, many are shareware.
I would also think if you save a file as cvs or some other type of delimited file that can be read by SQL, then you should be able to use it with your SQL program.

I had MYSQL years ago, played with it for a little while and abandoned it. I am sure the newer versions are a lot easier to work with. Start out by deciding what SQL platform to use and go from there.

[martinwcole]

Re: Multi-User 100~300 new records per hour...locking error

what version of sql are you working with?

man!! the people with previous experience in other platforms like sql and web building sure a a leg over!!

[G Gabriel]

Re: Multi-User 100~300 new records per hour...locking error

I played with MYSQL years ago, abandoned it and a year ago or so statred with SQL 2005 then 2008, but I can't claim any serious knowledge in this area.

[martinwcole]

Re: Multi-User 100~300 new records per hour...locking error

thanks - will experiment with

[DaveM]

Re: Multi-User 100~300 new records per hour...locking error

Martin,

There are converters/sql writers that can convert dbf to sql tables. They are free and are generally available where you get your version of sql from. They are just tools. You might also look at tucows.com for more tools. Once you get the structure over, I believe you can put the data into the tables from alpha using active link.

The only thing that bothers me about sql on a desktop app is the system I install on may not have sql, so I have to get that over too. I have not done that testing yet, because it is not time for me.

.

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

Thanks guys for all your advice.
Let me tell you what I have decided to do.
Based on your coments I realized that having many worksations using the same form appending to the same table using dbf files is not a good idea.
So, I duplicated the tables, sets, & form so that each POS would have its own autonomous set.
I created a separate table that holds the sequencial 'Receipt No', which is not on the forms. Each POS machine draws their Receipt numbers from this one table. All the POS's will write to their own tables exclusively thus there is no record locking problem. Further, they are all drawing from one table for the sequencial receipt number which is not on the form..thereby never held in edit mode.

dim tbl as P
dim tries as n
'OR_NO stand for official receipt number.......
dim SHARED vOR_NO as C
tries = 0
again:
tbl=table.open("Receipt_No",FILE_RW_SHARED)
on error goto no_open
tbl.enter_begin()
tbl.Time_punch=now()
tbl.enter_end(.t.)
vOR_NO = tbl.Or_no
tbl.close()

goto done_Or


no_open:
if tries < 20 then
sleep(1/10)
tries = tries+1
goto again
else
reply=ui_msg_box("Unable to update counter","Keep trying?",UI_YES_NO)
if reply=UI_YES_SELECTED then
tries=0
goto again
else
end
end if
end if

done_Or:

Then I use a MAPPED table to link all the POS tables into one big table where the 'Receipt_No' table is the parent, for all my reports, calculations, etc.

Please let me know what you think?

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

I've experimented with MySql and found that the auto-increment on MYSQL is a number field, while dbf works better when it is character. this causes many problems. Also when doing calculation sums on a form that involve counting child records -- 'count()', was problemetic.

Then there's the part where for every append type of operation, you must refresh the active-link table which takes time. I found that this took longer than just working with dbf files. Thats why i reverted to dbf after struggling with sql for some time.

I may attempt to do it again in the near future.