Mark A Matte
markamatte at hotmail.com
Mon Aug 20 09:20:17 CDT 2007
Thanks Fred, as for the concatenation...I will never know what the values will be...so I can't include them in the solution. Mark >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: Fri, 17 Aug 2007 17:18:13 -0400 > >Hi Mark, > >I've run SQL on SQL Server & Oracle that's *much* longer, so that's not a >limitation. You could use VBA to create a view in SQL Server that holds >your >10K SQL statements, union all'd together, then there'd be no size limit. > >For concatenation, how about something like (for SQL Server): > >select table.ID + ': ' + table.VALUE as zero, > table_1.ID + ': " + table_1.VALUE as one, > table_2.ID + ': " + table_2.VALUE as two > table_3.ID + ': " + table_3.VALUE as three, >from table where ID = 'aa' >inner join (select sql_run, ID + ': ' + VALUE > from table > where ID = 'bb') as table_1 > on table.sql_run = table_1.sql_run >inner join (select sql_run, ID + ': ' + VALUE > from table > where ID = 'cc') as table_2 > on table.sql_run = table_2.sql_run >inner join (select sql_run, ID + ': ' + VALUE > from table > where ID = 'ee') as table_3 > on table.sql_run = table_3.sql_run > >Note: sql_run is the number of one of your 10K queries. Please see the >column I've added to your example below. > >This code concatenates your records into a single row by aliasing the same >source. If you don't always have the same number of fields in the output >you'd have to use a "left outer" join in place of the "inner", and handle >the resulting nulls in the select clause. With 10K SQL statements * 5 >values >per statement this should run very quickly even with outer joins. > >You *could* write this code in Access, but you'd have to be careful when >saving and reusing it since the parser converts the parentheses in the sub >queries to square brackets and appends a period - which doesn't work nicely >if you later modify the code. > >You'd be better off to create another SQL Server view that uses the first >as >a source. It holds the above code (e.g. table <-- 10kView). Then, you call >the results of the second view in a pass-through query to get it into >Access >quickly, and use a regular query (with the pass-through as its source) to >place the results in an Access table. > >I'm guessing a few seconds run time for the whole thing -- after you have >the first view created. > >Fred > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte >Sent: Friday, August 17, 2007 10:58 AM >To: accessd at databaseadvisors.com >Subject: Re: [AccessD] SQL Speed > >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: > >sql_run ID VALUE > 1 aa -15 > 1 bb 18.5 > 1 cc -21.2 > 1 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 troublebefore he arrives >http://newlivehotmail.com/?ocid=TXT_TAGHM_migration_HM_viral_protection_0507 > > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ See what you’re getting into…before you go there http://newlivehotmail.com/?ocid=TXT_TAGHM_migration_HM_viral_preview_0507