Very Computer

Hello,

I have an application which outpus a comma delimited file with automobile
data for posting on a Web site, and I am having problems with speed.

There is a table of vehicles with about 10K records belonging to about 50
dealers. Then there are two related tables which are joined to provide a
list of options for each vehicle. Since there is usually a long list of
options, the joined tables have about 600K records.

Since I need to do a lot of data manipulation I open a recordset on the auto
table and step through it, one vehicle at the time. For each vehicle, I then
need to retrieve the text for each option, e.g. Tinted Glass, Power
Steering, CD Player, etc.

First, I tried opening a recordset for each vehicle, selected by the VIN,
then stepping through and concatenating the options into a comma separated
single string for the Options column in the text file. This proved very
slow, 3 to 4 hours typical time. For debugging purposes I use an Access 2000
database running on a 1.7 GHz Win XP machine with 256MB memory, the live
data feed will run SQL Server, but I was shocked by the slow speed.

Then I tried opening a recordset on the entire options tables, with about
600K record, and use Find to pick out the records for each vehicle, and the
time dropped to about 25 minutes!

I have noticed that opening and closing ADODB recordsets seems to slow, but
I would have thought searching through 600K records would tak som time too.

Any comments? Any suggetsions for a better way of doing this. I try to do as
much as I can with SQL, because it seems to be the fastest way of retrieving
data, but I can't see any way of getting the options data into a string with
SQL.

Any help would be appreciated

Ragnar

Quote:> I have an application which outpus a comma delimited file with automobile
> data for posting on a Web site

There may be other ways of doing this part.

Quote:> There is a table of vehicles with about 10K records belonging to about 50
> dealers. Then there are two related tables which are joined to provide a
> list of options for each vehicle. Since there is usually a long list of
> options, the joined tables have about 600K records.

Has an index and relation been created in the database to join these tables
with?

Quote:> First, I tried opening a recordset for each vehicle, selected by the VIN,
> then stepping through and concatenating the options into a comma separated
> single string for the Options column in the text file. This proved very
> slow, 3 to 4 hours typical time.

You can get speed improvements by ensuring your connection and recordsets
are using server side cursors.

Quote:> Any comments? Any suggetsions for a better way of doing this. I try to do
as
> much as I can with SQL, because it seems to be the fastest way of
retrieving
> data, but I can't see any way of getting the options data into a string
with
> SQL.

> Any help would be appreciated

Make sure you have an index and relation defined in your database so the
join is done quickly. Using server side cursors can be faster than client
side cursors. Use JET for Access (no odbc). You may be able to avoid using
CSV output if more about your requirements was known.

--
Mike Collier BSc (Hons)
www.adoanywhere.com
ADO Interactive Object Browser Tool
Trial From http://www.adoanywhere.com/download/aaBrowser.zip

Hello Mike

Thanks for your comments and suggestions, please see my inline comments
below.

Ragnar

Quote:> > I have an application which outpus a comma delimited file with
automobile
> > data for posting on a Web site

> There may be other ways of doing this part.

Afraid not, I have to follow the spec issued by Yahoo.

Quote:

> > There is a table of vehicles with about 10K records belonging to about
50
> > dealers. Then there are two related tables which are joined to provide a
> > list of options for each vehicle. Since there is usually a long list of
> > options, the joined tables have about 600K records.

> Has an index and relation been created in the database to join these
tables
> with?

I am pretty sure, but I will certainly check.

Quote:

> > First, I tried opening a recordset for each vehicle, selected by the
VIN,
> > then stepping through and concatenating the options into a comma
separated
> > single string for the Options column in the text file. This proved very
> > slow, 3 to 4 hours typical time.

> You can get speed improvements by ensuring your connection and recordsets
> are using server side cursors.

Will try that.

> > Any comments? Any suggetsions for a better way of doing this. I try to
do
> as
> > much as I can with SQL, because it seems to be the fastest way of
> retrieving
> > data, but I can't see any way of getting the options data into a string
> with
> > SQL.

> > Any help would be appreciated

> Make sure you have an index and relation defined in your database so the
> join is done quickly. Using server side cursors can be faster than client
> side cursors. Use JET for Access (no odbc). You may be able to avoid using
> CSV output if more about your requirements was known.

> --
> Mike Collier BSc (Hons)
> www.adoanywhere.com
> ADO Interactive Object Browser Tool
> Trial From http://www.adoanywhere.com/download/aaBrowser.zip

OK, let us know if you still get problems.

--
Mike Collier

Thanks Mike,

I have concluded that I need to use a stored procedure on the SQL Server.
The server is kind of slow and it is servicing several Web sites, so we
can't afford to tie it up for mor than a few minutes.
I will create a proc that will take the VIN as an argument and return a
string. Since it is precompiled it will run quickly, and the only data
crossing the network will be a VIN going out and a string coming back for
each vehicle.

.Ragnar

Quote:> OK, let us know if you still get problems.

> --
> Mike Collier