Mark A Matte
markamatte at hotmail.com
Fri Aug 17 09:58:13 CDT 2007
Thanks Fred, Making a union with my 10k SQLs would put me almost to 4million characters. As for the concatenation ...its not just the fields... it will concatenate 2 fields from every record returned from each SQL statement: ID VALUE aa -15 bb 18.5 cc -21.2 ee 16 Lets say these records are returned from 1 SQL...I then need the ID and Value from each of these records stored as a string: aa -15: bb 18.5: cc -21.2: ee 16 ...or something similar. and then do it again for the other 9999 SQLs. 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: Thu, 16 Aug 2007 18:35:49 -0400 > >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 > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ Now you can see trouble…before he arrives http://newlivehotmail.com/?ocid=TXT_TAGHM_migration_HM_viral_protection_0507