patternsqlMinor
Linked tables versus stored procedure performance in MySQL
Viewed 0 times
storedtablesversusproceduremysqlperformancelinked
Problem
Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form).
Option 1 is to simply create a linked table and set that as the record source for the sub-form. This of course requires little work.
Option 2 is to create a set of stored procedures for inserting, deleting, updating and reading records from the MySQL table to/from a local temporary table. The sub-form's record source is then set to the local table and the appropriate form events are tied to the appropriate stored procedure calls. Obviously this requires a lot more work than option 1.
Now the obvious question is why ever go for option 2? Well, I've been experimenting with replacing linked tables with stored procedures and have found the speed increase to be considerable, particularly over a slow network. I've done a bit of detective work too and dug around in the MySQL log files and I've noticed that the linked table approach generates a shocking amount of SQL calls. This explains the performance difference I suppose.
Anyway I'm now considering replacing all linked tables with the stored procedures option (well maybe just the important tables). Has anyone else had this experience? Any thoughts or advice?
Option 1 is to simply create a linked table and set that as the record source for the sub-form. This of course requires little work.
Option 2 is to create a set of stored procedures for inserting, deleting, updating and reading records from the MySQL table to/from a local temporary table. The sub-form's record source is then set to the local table and the appropriate form events are tied to the appropriate stored procedure calls. Obviously this requires a lot more work than option 1.
Now the obvious question is why ever go for option 2? Well, I've been experimenting with replacing linked tables with stored procedures and have found the speed increase to be considerable, particularly over a slow network. I've done a bit of detective work too and dug around in the MySQL log files and I've noticed that the linked table approach generates a shocking amount of SQL calls. This explains the performance difference I suppose.
Anyway I'm now considering replacing all linked tables with the stored procedures option (well maybe just the important tables). Has anyone else had this experience? Any thoughts or advice?
Solution
When I use Access, I use both techniques as some form of Command Query Separation (CQS)
That is, the form can read the base tables but all writes are via stored procedures.
In my case, I chose this for
Note: I used a view for Access not a base table also for some security.
Edit, for clarity, after comments:
"Read only" refers to server-side permissions that are transparent to Access. Access would try to write if we let it (instead of intercepting the call and invoking a stored procedure), and we'd get an error from the DB engine.
That is, the form can read the base tables but all writes are via stored procedures.
In my case, I chose this for
- security/audit reasons
- easier to deal data integrity
- re-use of write code for other clients
Note: I used a view for Access not a base table also for some security.
Edit, for clarity, after comments:
"Read only" refers to server-side permissions that are transparent to Access. Access would try to write if we let it (instead of intercepting the call and invoking a stored procedure), and we'd get an error from the DB engine.
Context
StackExchange Database Administrators Q#3031, answer score: 2
Revisions (0)
No revisions yet.