[AccessD] SQL Speed

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




More information about the AccessD mailing list