SQL General Question

[Mark Appleby]

I am curious about using SQL as a back end. My problem is the only reason that I am asking is because so many people have said that it is super fast when doing searches and when summarising many thousands of records.

The biggest problem of all is that I have not got a clue as to how to work in SQL, and I don't want to start learning it either! What are the options here?

Mark

[Anatoli]

Lasso is using MySQL. Lasso syntax is similar to CDML. If you will do the whole solution as HTML driven, then you can do it in MySQL and Lasso. You have to develop some kind of record locking.

What part of FM is slow for you? Maybe it is wrong design?

[Kurt Knippel]

I think that you may have been greatly misled about SQL. SQL is not an application, it is simply a language (Structured Query Language) and it is not inherantly faster at anything, let alone summarizing thousands of records.

Now some applications which use SQL as thier primary interface such as Oracle or SQLServer or MySQL may indeed be faster in some functions than Filemaker, but that really is moving to a whole different system and I would suggest that you first define what you consider slow and see if there isn't an existing way to speed things up within Filemaker.

[Mark Appleby]

I know that people in my company are going to be unhappy when they are summarising 100,000 transactions in order to get for instance a sales report by three or four categories, so far I have not run into this problem but I know that I will.

Imagine having to wait fifteen minutes! (Mind you this is better than how it is done at the moment!) The idea of having to wait is something that they feel they shouldn't have to do if we are doing all this work to get our solution right.

So far while it has taken me a while to get it running it seems to be giving me the information that I need in a reasonable ammount of time.

[Anatoli]

Hmm... 100,000 transactions, it depends. If you are using dynamic values like counting all transactions all the time, you may hit slow performance. In any case, you are not doing that every hour.

You can for instance run that report over night and post results to another database.

I prefer to have only static values in databases. When transaction is made, I immediately post all results to database. It can be payment, stock take, and invoice or stock level adjustments after invoice. It can be grouped by salesman or stock type.

So the transaction is posted in transaction file and in the same moment I am updating 5-10 *static* numbers in relevant files. What does it take? Split second to 1 second. User will not notice that. But when anyone in company needs some result from stock or invoicing, everything is instant. All the time and without SQL. Speed is not issue

[Mark Appleby]

I have always been thought that when creating numerical values in our databases that we should use calculations as much as possible. This goes against what I have been taught for instance the sum of sales for a sales person in a particular catagory could be adjusted at time of invoice but could this not lead to corruption? This is what I have always been thought, I must admit that I always woundered what if the value that the calculation is based on became corrupted, I think that I might try using lots of relationships to summarise data instead. It would definately be easier than learning SQL. All comments are gratefully recieved.

[Anatoli]

RE: what if the value that the calculation is based on became corrupted

Then you are done. You will never know.

Best is to have transactions and static values, because you can run the consistency test, when there is discrepancy between both values, the static one and calculated from transaction.

IMHO, in database should be only static values all the time and never ever calculate between multiple records!

Database is not spreadsheet. Database is just storing static values.

In database with population of country one will not calculate your mother birthday by formula: current year - your age - age of your mother when you where born.

That system will collapse the first day.

Store just static values.

[Kurt Knippel]

Something else to keep in mind.

I was working with people reporting on many hundreds of thousands of records and it took hours to generate a single page of summaries; however EXPORTING those same summaries to a reporting file took like 30 seconds. I then ran the "summaries" (which were now static values) from that file.

The only downside to this is that it requires a temp export file.

So the moral of the story is that exporting summaries can be much faster than running those summaries from the original file.

[Anatoli]

Why not use what works?

Most things are down to experience. If something works, use it, if not avoid that.

[Mark Appleby]

I appreciate what everybody is saying to me here, (in the immortal words of Fraiser Crane) "I am listening" . I agree with you to the greatest extent about my design, The export file might be the way to run summaries I have seen some terrific examples of these. I also agree with sticking with what you know. It took me a lot of time to get used to Filemaker, I came from Lotus Approach, which I found too restrictive for me (You are stuck to twenty joined tables Max) I am now working with about fifty but it is the transactions file which is growing the fastest. I have aprox 10,000 sales in a year, each sale would have a number of receipts which would each often have a number of transactions. I also knew that I would never be able to get the most out of it without learning code which I know I would not have the patience or time to learn. Mind you it is faster at running summaries of thousands of records, it is also faster to learn up to a point. But filemaker while it takes a little longer to put things together, I can do so much more, without writing code.

I have heard of filemaker plugins that can work with SQL tables and you don't have to know how work with them. That would give us the best of both worlds. Also the competition Alfa5 is working towards integrating SQL into their system.

For the moment I think that I will restrict the number of records that most people are allowed to report on, I have created two new fields that are static values when the sale is complete I will update the sale value as a number field. When I print a receipt I will update the total transaction value as a number field also. This will restrict the quantity of records that would need to be summarised.

Another Question now is if the size of the table is smaller (using only a few text fields mainly numeric values) will that make the sorting of records faster, or is it just the quantity of records that we are trying to sort and summarise, obviously I can only summarise numeric values?

I do appreciate the help, both Anatoli and Kurt, the experience of these forums is what makes learning Filemaker so much fun, if I get into difficulty or I am just considering something I can discuss it with the likes of you guys who have seen it all before. Being able to know the dangers and problems ahead will enable me to avoid trouble with my colleagues for instance when the server is taken up creating reports.

Mark

[Kurt Knippel]

The size of the table itself has some impact but not much. Especially when sorting and summarizing.

Also the speed of the interface has a huge impact. Using the Freeze Window until everything else is calculated and then Refresh can make huge speed improvements, also NOT showing a print preview can make a report seem like it goes faster, since Filemaker does not have to generate a screen version of the report.

Finally you can also look at automating the reports to run at set intervals, such as nightly at midnight, or once a week or whatever. This way it requires no user intervention and can take hours if needed.

[Anatoli]

I agree 100%. The size will have some impact, but it really depends on design.

[Mark Appleby]

Thanks for your help guys.

Mark

[Anatoli]

Any time