[AccessD] SQL Speed

Hi Mark,

I once wrote an application that updated an Excel file with the results of
running 5K cross tabs. These were constructed in Access and returned ADO
recordsets which I placed in the Excel file. As I recall, it took 20 minutes
running against a 5M row SQL Server table. 

An even faster way (that I didn't think of then) would be to make a
pass-through query that uses a (probably one line) file to control its
results. You could then update that file and run a query that appends the
results of the pass-through query to your table. This way you do as much
with SQL as possible.

Overall, it sounds doable and fun.

Fred

-----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 4: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