performance different between DSN to DSN-Less Connection
| Author |
Message |
|
boaz
#1 / 8
|
 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 |
|
 |
|
Mike Collie
#2 / 8
|
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 |
|
|
|
boaz
#3 / 8
|
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 |
|
|
|
Mike Collie
#4 / 8
|
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 |
|
|
|
Carlos J. Quinter
#5 / 8
|
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 |
|
|
|
Jeff Jone
#6 / 8
|
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 |
|
|
|
boaz
#7 / 8
|
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 |
|
|
|
boaz
#8 / 8
|
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 |
|
| |
|
