Do Stored Queries Increase the Speed of Access Queries?

[JimBolla]

Huzzah to M@rco on his recently published article on 15seconds.com, titled "Do Stored Queries Increase the Speed of Access Queries?"

[greg.harvey]

Good stuff. I can't put that into practice sadly, as you need Access running on the web server don't you ... ?

One other thing Marco missed when setting the background was option three, keeping your regularly used SQL strings in an SI. Anyway. I'm nit-picking. Good stuff mate! Shame I'm migrating all my stuff to MSDE!

[JEmLAC]

Originally posted by greg.harvey
[B]Good stuff. I can't put that into practice sadly, as you need Access running on the web server don't you ... ?

Actually, I believe what Marcus is talking about would apply in any application that is interacting with an Access database via ADO. This would include Web servers or desktop applications.

[MarcusJT]

Thanks CrowDozer! I mentioned it to DaveMaxwell, but was wondering if anyone kept an eye on 15Seconds.com (I've recommended it enough times )!!!


JEmLAC, you're quite correct - as I have explained many times in the past in these forums, "Access databases" are really just JET4 databases (which are stored on disk with an .mdb file extension), and do not require the presence of Access (the desktop app) to function, only ADO.

Access (the app) is merely an impressive frontend to the ADO and ADOX objects, which do all the work of interacting with the .mdb on the disk.

Hence referring to .mdb files as "Access databases" is an unfortunate misnomer, but since that's the established norm, it's not going to change any time soon.
(I would have hoped that all of this would be common knowledge and well-understood by now, but perhaps not)


greg.harvey - Thanks, but I'm afraid that I don't quite follow your reference to "keeping your regularly used SQL strings in an SI". When you hardcode an SQL statement in a script this is exactly what you are doing, so how is this another option??!

[MarcusJT]

BTW - Has anyone run the benchmarks themselves? If so, did you get similar results?