[AccessD] SQL Speed

Fred Hooper fahooper at trapo.com
Mon Aug 20 15:23:01 CDT 2007


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






More information about the AccessD mailing list