Mark A Matte
markamatte at hotmail.com
Mon Aug 20 15:55:17 CDT 2007
the aa, bb, ...are the values....sorry...ID may have been misrepresented...so I can't say where ID=x because I will never know what x is? Sorry for the confusion...mine especially, Thanks, 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: Mon, 20 Aug 2007 16:23:01 -0400 > >The concatenation doesn't rely on the values, it relies on the aa, bb, ... >If you don't know what *they* are in advance, then you'd have to construct >the concatenation code guided by the result of a "select distinct" -- same >solution, slightly more complex code to implement it -- that is VBA code to >construct SQL code to create the needed view. > >Fred > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte >Sent: Monday, August 20, 2007 10:20 AM >To: accessd at databaseadvisors.com >Subject: Re: [AccessD] SQL Speed > >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_050 >7 > > > > > > > >-- > >AccessD mailing list > >AccessD at databaseadvisors.com > >http://databaseadvisors.com/mailman/listinfo/accessd > >Website: http://www.databaseadvisors.com > >_________________________________________________________________ >See what youre getting intobefore you go there >http://newlivehotmail.com/?ocid=TXT_TAGHM_migration_HM_viral_preview_0507 > > > >-- >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