[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