[AccessD] SQL Speed

Thanks to All for your responses...(everything discussed below is currently 
in A2K)

I'm at the beginning of this and appreciate any ideas...This program has 
been running 24/7 for the last 3 years...but only runs 1 SQL statement.  It 
runs the statement, loops through the results and concatenates the results, 
and then emails the results (for these tests we are going to forget about 
the email part and just store the results in a separate table).

Last night I put a loop on this and ran it 10K times.  It took just under 2 
minutes.  To make it more realistic, (the 10k SQL statements will all be 
different, but very similar) I removed the SQL from the code and placed it 
in a memo field in another table (tblSQL).  Next, I modified the code so now 
it first pulls all records form tblSQL (I added 10k rows...but all the same 
SQL statement)...then for each of these records...it does the stuff I 
outlined above.

Again, it ran in just under 2 minutes.  I need this to be as fast as 
possible, and I don't know what a realistic time is.  I apparently can do 
10K in less than 2 minutes, but is this good, bad, average?

Any thoughts/ideas?

Thanks,

Mark A. Matte


>From: Jim Lawrence <accessd at shaw.ca>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] SQL Speed
>Date: Wed, 08 Aug 2007 22:15:43 -0700
>
>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

_________________________________________________________________
Tease your brain--play Clink! Win cool prizes! 
http://club.live.com/clink.aspx?icid=clink_hotmailtextlink2