Christopher Hawkins
clh at christopherhawkins.com
Tue Aug 14 21:08:09 CDT 2007
Hi, Mark. I think I missed this topic the first time it came up. This is a hard question to answer, mainly because you don't mention what type of data is contained in the 4K rows you're querying, and how many fields are involved. You also mention that the results will be "concatenated", which seems like an odd thing to do. I would expect you to run a sum or a count or something, not a concatenation of 4K rows. Can you provide more detail? Off the cuff, 2 minutes sounds slow to return 4K rows of the kind of data I tend to work with, but like I said, I'm not exactly clear on what you're doing with those rows. Can you show us the actual SQL? -C- ---------------------------------------- From: "Mark A Matte" <markamatte at hotmail.com> Sent: Tuesday, August 14, 2007 3:12 AM To: accessd at databaseadvisors.com Subject: Re: [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" >Reply-To: Access Developers discussion and problem >solving >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 > >Reply-To: Access Developers discussion and problem > >solving > >To: "'Access Developers discussion and problem > >solving'" > >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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com