[AccessD] SQL Speed

Hello All,

I haven't received any responses after the email below.  I am specifically 
curious about the  realistic time to run 10K sql statements (see below).  
Access vs. SQL server?

Any feedback is greatly appreciated.

Thanks,

Mark A. Matte


>From: "Mark A Matte" <markamatte at hotmail.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] SQL Speed
>Date: Thu, 09 Aug 2007 17:27:51 +0000
>
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
A new home for Mom, no cleanup required. All starts here. 
http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us