Jim Lawrence
accessd at shaw.ca
Thu Aug 9 19:05:25 CDT 2007
Hi Michael: As a matter of fact I have. As long as variables and not values are used and the procedure are allowed to compile the performance is superior. When do they get compiled... first time they are used and then they remain cached until they time-out. If for example you have an 'Add record' SP the first time used it is compiled and cache and then every other user that request this procedure enjoys almost instantaneous access. If the SQL code is in fact uploaded first, then compiled before use the performance is going slower let alone the obvious security risks. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael Maddison Sent: Wednesday, August 08, 2007 10:59 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] SQL Speed Hi Jim, Have you tested this? I have, 'any queries that can run on the SQL server as pre-compiled stored procedures will give superior performance' is too broad a statement. In most instance a sql statement that has a cached exec plan is exactly as fast as a sproc (that has a cached plan). cheers Michael M -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Thursday, 9 August 2007 3:16 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] SQL Speed Well, you have probably already thought of this, but any queries that can run on the SQL server as pre-compiled stored procedures will give superior performance. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Wednesday, August 08, 2007 1:57 PM To: accessd at databaseadvisors.com Subject: [AccessD] SQL Speed Hello All, I am involved in a project that will be web based. The database will either be access or SQL Server. The question is: I need to run a bunch (maybe 10K) of SQL statements againts a single table...or flat file, whatever is best, containing about 4K rows. The results of each will be appended to a second table, or emailed instantly (ahh...idea...good place for a JC style Class). The SQL statements themselves will be stored in a table. Does anyone have any ideas/suggestions about approach? I will need ALL of the SQLs to run in less than 5 minutes. I know 1 runs in a fraction of a second...I just don't know what that fraction is to calculate time needed. Being there are so few rows involved...but so many SQL statements...and speed is an issue...will there be a signicant advantage using SQL Server or Access? I'm thinking of having the SQLs in a table and looping through and executing each...I just don't know if this is the best approach? Thanks, Mark A. Matte _________________________________________________________________ Booking a flight? Know when to buy with airfare predictions on MSN Travel. http://travel.msn.com/Articles/aboutfarecast.aspx&ocid=T001MSN25A07001 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com