Fred Hooper
fahooper at trapo.com
Fri Aug 17 16:18:13 CDT 2007
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