performance different between DSN to DSN-Less Connection 
Author Message
 performance different between DSN to DSN-Less Connection

Hi guys,

Is there any performance different between a standard DSN from a DSN-less
connection for an Access database stored in a server?

I am trying to make the Access MDB database do magic.

--
-



Sun, 03 Oct 2004 05:20:42 GMT  
 performance different between DSN to DSN-Less Connection
If you need performance then try JET instead of ODBC DSN & ODBC DSN-Less, it
is normally faster.

--
Michael Collier Bsc (Hons)

Interactive ADO Object Browser (Free Trial) www.adoanywhere.com
Free ADO Browser (FAB)  www.adoanywhere.com/fab
Home Page www.adoanywhere.com

--


Quote:
> Hi guys,

> Is there any performance different between a standard DSN from a DSN-less
> connection for an Access database stored in a server?

> I am trying to make the Access MDB database do magic.

> --
> -



Sun, 03 Oct 2004 06:53:45 GMT  
 performance different between DSN to DSN-Less Connection
Hmm...
I have 5 people accessing the mdb file at the same time and the mdb file is
close to 170Meg in size now.
This program was a VB3 program using JET.  It was very unstable before.
I updated the program to a VB6 program using ADO.   It runs ok now but it is
just slow.

I am trying find a way to make it run faster.
I've found that the bottleneck is from Access.
Maybe changing the way I connect to the mdb file may give me a better
performance?


Quote:
> If you need performance then try JET instead of ODBC DSN & ODBC DSN-Less,
it
> is normally faster.

> --
> Michael Collier Bsc (Hons)

> Interactive ADO Object Browser (Free Trial) www.adoanywhere.com
> Free ADO Browser (FAB)  www.adoanywhere.com/fab
> Home Page www.adoanywhere.com

> --



> > Hi guys,

> > Is there any performance different between a standard DSN from a
DSN-less
> > connection for an Access database stored in a server?

> > I am trying to make the Access MDB database do magic.

> > --
> > -



Sun, 03 Oct 2004 07:51:14 GMT  
 performance different between DSN to DSN-Less Connection
I think changing DSN / DSN-Less will affect only the connection time but I
can't be sure. (DSN would require a lookup in the registry)

The situations where I normally see differences in speed (apart from
Jet/Odbc) is in the connection and recordset settings.

The following give the fastest queries (in general and you may not get them
all with access):

Recordset Cursor Location = Server
Lock Type = Read Only.
Cursor Type = Forward Only

Also specify the CommandType of a recordset whenever possible.

--
Michael Collier Bsc (Hons)

Interactive ADO Object Browser (Free Trial) www.adoanywhere.com
Free ADO Browser (FAB)  www.adoanywhere.com/fab
Home Page www.adoanywhere.com

--


Quote:
> Hmm...
> I have 5 people accessing the mdb file at the same time and the mdb file
is
> close to 170Meg in size now.
> This program was a VB3 program using JET.  It was very unstable before.
> I updated the program to a VB6 program using ADO.   It runs ok now but it
is
> just slow.

> I am trying find a way to make it run faster.
> I've found that the bottleneck is from Access.
> Maybe changing the way I connect to the mdb file may give me a better
> performance?



> > If you need performance then try JET instead of ODBC DSN & ODBC
DSN-Less,
> it
> > is normally faster.

> > --
> > Michael Collier Bsc (Hons)

> > Interactive ADO Object Browser (Free Trial) www.adoanywhere.com
> > Free ADO Browser (FAB)  www.adoanywhere.com/fab
> > Home Page www.adoanywhere.com

> > --



> > > Hi guys,

> > > Is there any performance different between a standard DSN from a
> DSN-less
> > > connection for an Access database stored in a server?

> > > I am trying to make the Access MDB database do magic.

> > > --
> > > -



Sun, 03 Oct 2004 08:46:45 GMT  
 performance different between DSN to DSN-Less Connection

Quote:
> I've found that the bottleneck is from Access.
> Maybe changing the way I connect to the mdb file may give me a better
> performance?

There is virtually no difference of connection speed using DSN-less
connections vs DSN connections. As Mike has said, stick to read-only,
forward-only cursors and do INSERTS, UPDATES and DELETES with direct SQL
statements.

--
Best regards,

Carlos J. Quintero

MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB5, VB6 and VBA:
Code Templates, Detect dead code, Favorite Procedures, Favorite Projects,
External Utilities, XML Documentation,  Private Clipboards, Select Case
Assistant,
Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
File, Edit File As Text, MsgBox Assistant, Connect String Assistant and
up to 40+ features:
www.mztools.com



Sun, 03 Oct 2004 18:22:21 GMT  
 performance different between DSN to DSN-Less Connection
My advice to you is to update your Access MDB to the free MSDE from
Microsoft, and use either ADO 2.7 in VB6 or ADO.NET (SQLClient) in VB.NET.
When using the COM ADO in VB6, forget the Command object.   It is totally
unnecessary.

If you convert to the MSDE, you have the SQL Server tools on your VB6
enterprise CDs, and can use them for managing the MSDE.  Also, convert your
static SQL to stored procs in MSDE.  Use read only for simple reads, use
stored procs via the Connection object to write to the database.

Do these things and you'll see a significant performance increase.  Feel
free to email me if you have specific questions.

Jeff Jones

Quote:
> Hmm...
> I have 5 people accessing the mdb file at the same time and the mdb file
is
> close to 170Meg in size now.
> This program was a VB3 program using JET.  It was very unstable before.
> I updated the program to a VB6 program using ADO.   It runs ok now but it
is
> just slow.

> I am trying find a way to make it run faster.
> I've found that the bottleneck is from Access.
> Maybe changing the way I connect to the mdb file may give me a better
> performance?



Sun, 03 Oct 2004 21:15:21 GMT  
 performance different between DSN to DSN-Less Connection
I totally agree.  This is what I have done with this old program.  I open a
recordset with a Static Cursor, get the records, and then close the
recordset right away.

Look like this is the best I can do.  Can't do any more to make Access do
magic.



Quote:
> > I've found that the bottleneck is from Access.
> > Maybe changing the way I connect to the mdb file may give me a better
> > performance?

> There is virtually no difference of connection speed using DSN-less
> connections vs DSN connections. As Mike has said, stick to read-only,
> forward-only cursors and do INSERTS, UPDATES and DELETES with direct SQL
> statements.

> --
> Best regards,

> Carlos J. Quintero

> MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB5, VB6 and VBA:
> Code Templates, Detect dead code, Favorite Procedures, Favorite Projects,
> External Utilities, XML Documentation,  Private Clipboards, Select Case
> Assistant,
> Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
> File, Edit File As Text, MsgBox Assistant, Connect String Assistant and
> up to 40+ features:
> www.mztools.com



Mon, 04 Oct 2004 01:01:59 GMT  
 performance different between DSN to DSN-Less Connection
I had tried to upsize the Access file to MSDE.  It ran really fast.
However, I am using alot of .RecordCount function in my program.  It will
take me forever to re-structure the whole program.


Quote:
> My advice to you is to update your Access MDB to the free MSDE from
> Microsoft, and use either ADO 2.7 in VB6 or ADO.NET (SQLClient) in VB.NET.
> When using the COM ADO in VB6, forget the Command object.   It is totally
> unnecessary.

> If you convert to the MSDE, you have the SQL Server tools on your VB6
> enterprise CDs, and can use them for managing the MSDE.  Also, convert
your
> static SQL to stored procs in MSDE.  Use read only for simple reads, use
> stored procs via the Connection object to write to the database.

> Do these things and you'll see a significant performance increase.  Feel
> free to email me if you have specific questions.

> Jeff Jones

> > Hmm...
> > I have 5 people accessing the mdb file at the same time and the mdb file
> is
> > close to 170Meg in size now.
> > This program was a VB3 program using JET.  It was very unstable before.
> > I updated the program to a VB6 program using ADO.   It runs ok now but
it
> is
> > just slow.

> > I am trying find a way to make it run faster.
> > I've found that the bottleneck is from Access.
> > Maybe changing the way I connect to the mdb file may give me a better
> > performance?



Mon, 04 Oct 2004 01:06:42 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Crystal and ODBC DSN/DSN-less connections - do I need USER DSN or SYSTEM DSN

2. DSN vs DSN-less connections

3. Connect SQL-SERVER via File DSN or DSN LESS

4. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

5. DSN/ DSN-Less Conenction

6. DSN/ DSN-Less Conenction

7. Connection string for DSN-less Informix connection

8. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

9. VB6/SQL6.5/MTS - DSN or DSN-LESS?.

10. DSN versus DSN-less

11. which Dsn(Data Source Name) is Faster System DSN Or File DSN

12. Import Excel with ODBC Drivers and DSN-less connection

 

 
Powered by phpBB® Forum Software