Fred Hooper
fahooper at trapo.com
Thu Aug 16 17:35:49 CDT 2007
Hi Mark, I don't know how long an SQL statement can be, but I've written some very long ones - at least 5k characters. When you find out the limit you could just use a loop and maybe have to run 2 or 3 (or 50) queries - still much better than 10k. You could adapt Gustav's "sort with union all" idea by placing "select n as ID_no" as the first field and separate the results later using that #. If they have different numbers of fields, then place dummy fields where needed. The results get into the temp table by having another query that uses the pass-through query as its source. I suggested this because of my experience: I was placing information on all of the tables/fields in an SQL Server database (about 13k fields) into an Access table so I could more easily learn the database. First, I tried a direct SQL statement against the database. It ran a couple of seconds in Query Analyzer. When I used it as a recordset to fill the table, I killed it in a few minutes - but it was going to run at least an hour. The pass-through query fills the table (through another query) in 2-3 seconds. Also, why not concatenate the fields in SQL? Fred -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Thursday, August 16, 2007 11:26 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] SQL Speed Thanks Fred, But I need to do take the results of each statement and send them individually somewhere(either email or temp table)... ...and can an SQL statement be that long? Thanks, Mark A. Matte >From: "Fred Hooper" <fahooper at trapo.com> >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, 15 Aug 2007 18:07:26 -0400 > >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 > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ 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