Inserting records into MS-Access97

KSG

Delphi Developer

Tue, 29 Jun 2004 06:10:36 GMT

Inserting records into MS-Access97

After creating a 60 field table in MS-Access (via code) I then add roughly
several millon rows.
With this in mind, Which methods seems better for speed of inserting records

1. TADOQuery
2. TADOCommand

Brian Bushay Team

Delphi Developer

Tue, 29 Jun 2004 13:17:29 GMT

Re:Inserting records into MS-Access97

Quote

>After creating a 60 field table in MS-Access (via code) I then add roughly
>several millon rows.
>With this in mind, Which methods seems better for speed of inserting records

>1. TADOQuery
>2. TADOCommand

There should not be any speed difference between these two components.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Vassiliev V.V

Delphi Developer

Tue, 29 Jun 2004 16:52:57 GMT

Re:Inserting records into MS-Access97

You should use TADOTable with server cursor and TableDirect = True.

Or, you could check OLEDB Direct components at http://www.oledbdirect.com.
These components use OLEDB directly (whitout ADO) and are the fastest way to
work with Access databases. For your task look at CreateAndFillJetTable demo
(in Examples directory). This demo creates and fills table with 10 integer
columns. Inserting 100 000 rows for Access97 (Jet 4x provider with
JETDBENGINETYPE_JET3X engine type) takes 20 seconds on my PII-300. For
PIV-2000 it could take ~3 seconds.

Regards,
Vassiliev V.V.
http://www.oledbdirect.com

"KSG" <kevin.s.gallag...@state.or.us> ???Y/???Y ????? ???Y??:
news:3c3e115f$1_1@dnews...

Quote

> After creating a 60 field table in MS-Access (via code) I then add roughly
> several millon rows.
> With this in mind, Which methods seems better for speed of inserting
records

> 1. TADOQuery
> 2. TADOCommand

AdoAnywhere Tea

Delphi Developer

Tue, 29 Jun 2004 18:46:09 GMT

Re:Inserting records into MS-Access97

Quote

> several millon rows.

Good luck using Access. I would reconsider my database ASAP.

--
Michael Collier Bsc (Hons) Comp Sci
ADO Support Tools.
Download Free Fully Working 30-Day Trial in 7 Minutes From...
http://www.adoanywhere.com/download/aaBrowse2_6.zip

All information in this message is provided 'As is' and without warranty of
any kind. http://www.adoanywhere.com

KSG

Delphi Developer

Tue, 29 Jun 2004 22:40:47 GMT

Re:Inserting records into MS-Access97

Thanks, I think the same way but for the first year of the application it is
MS-Access, afterwards
it's moving to an AS400/DB2 table.

Quote

"AdoAnywhere Team" <m...@adoanywhere.com> wrote in message

news:3c3ec83b_1@dnews...

Quote

> > several millon rows.

> Good luck using Access. I would reconsider my database ASAP.

> --
> Michael Collier Bsc (Hons) Comp Sci
> ADO Support Tools.
> Download Free Fully Working 30-Day Trial in 7 Minutes From...
> http://www.adoanywhere.com/download/aaBrowse2_6.zip

> All information in this message is provided 'As is' and without warranty
of
> any kind. http://www.adoanywhere.com

KSG

Delphi Developer

Tue, 29 Jun 2004 22:41:30 GMT

Re:Inserting records into MS-Access97

I tried using TADOTable but was way to slow.

Quote

"Vassiliev V.V." <vv_...@iname.com> wrote in message

news:3c3eb14a_2@dnews...

Quote

> You should use TADOTable with server cursor and TableDirect = True.

> Or, you could check OLEDB Direct components at http://www.oledbdirect.com.
> These components use OLEDB directly (whitout ADO) and are the fastest way
to
> work with Access databases. For your task look at CreateAndFillJetTable
demo
> (in Examples directory). This demo creates and fills table with 10 integer
> columns. Inserting 100 000 rows for Access97 (Jet 4x provider with
> JETDBENGINETYPE_JET3X engine type) takes 20 seconds on my PII-300. For
> PIV-2000 it could take ~3 seconds.

> Regards,
> Vassiliev V.V.
> http://www.oledbdirect.com

> "KSG" <kevin.s.gallag...@state.or.us> ???Y/???Y ?????
???Y??:
> news:3c3e115f$1_1@dnews...
> > After creating a 60 field table in MS-Access (via code) I then add
roughly
> > several millon rows.
> > With this in mind, Which methods seems better for speed of inserting
> records

> > 1. TADOQuery
> > 2. TADOCommand

KSG

Delphi Developer

Tue, 29 Jun 2004 22:43:23 GMT

Re:Inserting records into MS-Access97

Brian,
Is there any benifit to using TParams in an INSERT query? or simply create
an SQL string for either
component? The reason I ask is that I don't really want to write it both
ways unless there is a
good reason too.

Thanks
Kevin

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:qfss3uoikp48opibspeqc1ubamigg07ead@4ax.com...

Quote

> >After creating a 60 field table in MS-Access (via code) I then add
roughly
> >several millon rows.
> >With this in mind, Which methods seems better for speed of inserting
records

> >1. TADOQuery
> >2. TADOCommand

> There should not be any speed difference between these two components.
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Thérèse Hanque

Delphi Developer

Wed, 30 Jun 2004 03:07:46 GMT

Re:Inserting records into MS-Access97

Hi KSG

Quote

> I tried using TADOTable but was way to slow.

> > You should use TADOTable with server cursor and TableDirect = True.

You can do that also with TADODataSet, by setting CommandType to
cmdTableDirect.

Thrse

Brian Bushay Team

Delphi Developer

Wed, 30 Jun 2004 13:11:41 GMT

Re:Inserting records into MS-Access97

Quote

>Is there any benifit to using TParams in an INSERT query? or simply create
>an SQL string for either
>component? The reason I ask is that I don't really want to write it both
>ways unless there is a
>good reason too.

Using Parameters is quite a bit faster when you execute the query more than
once.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

KSG

Delphi Developer

Wed, 30 Jun 2004 23:29:51 GMT

Re:Inserting records into MS-Access97

Thanks for the info Brian.

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:sfcv3u0cqtq8guk78pfk9tpki1e2k98a5l@4ax.com...

Quote

> >Is there any benifit to using TParams in an INSERT query? or simply
create
> >an SQL string for either
> >component? The reason I ask is that I don't really want to write it both
> >ways unless there is a
> >good reason too.
> Using Parameters is quite a bit faster when you execute the query more
than
> once.
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Other Threads

1. How to change field value of inserted record in insert trigger for MS SQL

2. MS-access97 SQL-insert problem

3. ms-access97 sql-insert problems

4. How to insert records into indexed MS-SQL-Tables with Delphi C/S

5. Insert record in MS SQL through Delphi

6. ms sql7: no records inserted

7. Delphi 4 and MS SQL V7: no records inserted

8. MS SQL Server Insert a record with a IDENTITY field

9. Record Cloning or Insert New Record using data from currently selected record

10. insert huge Blob insert Ms SQL Server