Hi all.
As has been mentioned here recently, I have a front-end database which just includes some queries and a bunch of (rather large) linked tables. For reasons of (very poor) performance, I had to do quite a bit of fiddling around to get my VBA code to build queries which would query each table individually, then UNION the results. Doing a single UNION query produced performance I probably could have bettered with a pencil and paper.
Something mentioned in this forum (by either NeoPa or mmccarthy, I think) inspired me to do some experimentation (which I had previously been putting off due to time pressure). This has led to the conclusion that the performance of a UNION query is hugely improved if the tables involved have primary keys. This may not come as a surprise to you, but I just hadn’t had time to check it out.
My big tables don’t have a primary key because a there isn’t room (they’re right up against the 2GB limit) and b it wasn’t necessary when working with one table at a time – performance was quite acceptable. I’ve only just moved to the split design to try and consolidate everything into a single interface.
What I’d like to know is, have I stuffed up my testing, or am I really likely to see an enormous speed improvement when doing simple queries against a bunch of “unioned” tables by adding a primary key to the tables? (The primary key that I added was totally unrelated to my test queries or to the real ones I’ll be using.)
As has been mentioned here recently, I have a front-end database which just includes some queries and a bunch of (rather large) linked tables. For reasons of (very poor) performance, I had to do quite a bit of fiddling around to get my VBA code to build queries which would query each table individually, then UNION the results. Doing a single UNION query produced performance I probably could have bettered with a pencil and paper.
Something mentioned in this forum (by either NeoPa or mmccarthy, I think) inspired me to do some experimentation (which I had previously been putting off due to time pressure). This has led to the conclusion that the performance of a UNION query is hugely improved if the tables involved have primary keys. This may not come as a surprise to you, but I just hadn’t had time to check it out.
My big tables don’t have a primary key because a there isn’t room (they’re right up against the 2GB limit) and b it wasn’t necessary when working with one table at a time – performance was quite acceptable. I’ve only just moved to the split design to try and consolidate everything into a single interface.
What I’d like to know is, have I stuffed up my testing, or am I really likely to see an enormous speed improvement when doing simple queries against a bunch of “unioned” tables by adding a primary key to the tables? (The primary key that I added was totally unrelated to my test queries or to the real ones I’ll be using.)
Comment