[AccessD] SQL Speed

Fred Hooper fahooper at trapo.com
Thu Aug 16 17:35:49 CDT 2007


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





More information about the AccessD mailing list