Fred Hooper
fahooper at trapo.com
Wed Aug 15 17:07:26 CDT 2007
Hi Mark, Is there any chance you could string those 10k runs together with union all's? If so, you could run all of them at once with a pass-though query, which would be *much* faster than 10k separate runs. Fred -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Wednesday, August 15, 2007 9:47 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] SQL Speed I'm not returning 4K rows...the table I'm running these SQL statements againts has 4K rows... In one table I have 10K SQL statements(1 per row). The SQL statements are all filtering on indexed currency and integer fields. I pullin all 10K as a recordset....and loop through...for each row, I execute that SQL againts a table with about 4K rows...and take the results (typically between 1 and 20 rows) and concatenated a Char4 and a Currency field from each of the results into 1 long string (this will later be the body of an email). So...I run 10K SQL statements, one right after the other, against a table with about 4K rows, returning between 1 and 20 records per SQL statement. To run these 10K and store the results it takes just less than 2 Minutes...if this is slow...please share how long (average) you would expect it to take 10K queries to run? There is more detail in the emails below... >From: "Christopher Hawkins" <clh at christopherhawkins.com> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: <accessd at databaseadvisors.com> >Subject: Re: [AccessD] SQL Speed >Date: Tue, 14 Aug 2007 20:08:09 -0600 > >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 > > > _________________________________________________________________ Puzzles, trivia teasers, word scrambles and more. Play for your chance to win! http://club.live.com/home.aspx?icid=CLUB_hotmailtextlink -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com