-
Sep 17th, 2020, 10:38 AM
#1
Thread Starter
PowerPoster
ADO on Lan
I need a good Tips to speed the ADO conection over Lan, naturally tips to speed also a recordset operation.
Note:
Access database
or
Sql Server
-
Sep 17th, 2020, 01:18 PM
#2
Hyperactive Member
Re: ADO on Lan
Reduce the amount of fields you are returning (Avoid "select *" if possible)
Reduce the number of rows you are returning
SQL will be faster than access
-
Sep 17th, 2020, 01:32 PM
#3
Re: ADO on Lan
I use the DAO over LANs all the time, and it works quite well.
And yes, I don't use "select *". In fact, for the most part, I just use the regular DAO stuff (Seek, MoveFirst, MoveNext, FindFirst, FindNext, AddNew, Edit, Update). I also do use transactions (BeginTrans, CommitTrans) on virtually all updates. And, if/when changing a bunch of records, it should help to surround them in a transaction.
Also, a huge factor will be the speed of all the switches, routers, and ethernet cables in the LAN system. Also, speed of the file servers (raw horsepower and also disk speeds) will make some difference.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 17th, 2020, 01:44 PM
#4
Re: ADO on Lan
Yeah, a file-based DBMS will always be slower across a network than a client-server DBMS. That's just life.
But even then the same rule applies: "Take only what you need to survive."
And that means things like server-side cursors when possible, not updating/inserting via a connected Recordset, and smart query design.
Anyone remember the movie that quote comes from?
-
Sep 17th, 2020, 02:04 PM
#5
Re: ADO on Lan
Originally Posted by dilettante
Yeah, a file-based DBMS will always be slower across a network than a client-server DBMS. That's just life.
But even then the same rule applies: "Take only what you need to survive."
Yes! I don't have any head-to-head comparisons of file-based vs SQL server speeds over a LAN, but I'd have to believe that the SQL server would be faster. Basically, that's what they're designed for from the ground up.
And yes, I'd certainly believe all the same rules apply ... get as much bandwidth as you can, and don't needlessly waste it.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 17th, 2020, 02:23 PM
#6
Re: ADO on Lan
The main difference is that file-based DMBSs have to read/write the raw DB files across the network, handling file locking, etc. A client-server DBMS can keep all of that on the server... unless some dummy put its files on yet another file server.
-
Sep 17th, 2020, 02:40 PM
#7
Hyperactive Member
Re: ADO on Lan
Originally Posted by dilettante
Anyone remember the movie that quote comes from?
even in the future nothing works
-
Sep 17th, 2020, 03:25 PM
#8
Re: ADO on Lan
Originally Posted by dilettante
"Take only what you need to survive."
Anyone remember the movie that quote comes from?
Originally Posted by DllHell
even in the future nothing works
Did I miss something? When did we get to Disneyland?
-tg
-
Sep 17th, 2020, 11:58 PM
#9
Re: ADO on Lan
Originally Posted by luca90
I need a good Tips to speed the ADO conection over Lan, naturally tips to speed also a recordset operation.
Keep in mind that ADO doesn't connect to anything, the Provider does.
If you are using the shim Provider ("MSDASQL") then it defers the "connecting" to the associated ODBC Driver. Doing that with MDBs drops you back to the old Jet SQL syntax and loses quite a number of features. Always Use the Jet 4.0 Provider, don't shim down to ODBC.
There are a lot of possible tweaks available in the Jet 4.0 Provider that might apply when you need performance. For example record-level locking.
And firehose cursors (server-side, forward-only, read-only cursors) can also help performance where they apply. For updating you may want to consider using transactions too.
We could list tips all day and overwhelm you. There is no substitute for the documentation, good books, and even formal training.
-
Sep 18th, 2020, 12:33 AM
#10
Re: ADO on Lan
Here is a book online that you might find useful: https://flylib.com/books/en/3.9.1/
It shows a "cover" and text saying it is about ADO.NET, but it seems to really be for ADO.
I assume it isn't a pirated copy of the book.
-
Sep 18th, 2020, 03:30 AM
#11
Banned
Re: ADO on Lan
Originally Posted by Prahlad
In your Connection string use something like "\\Servername\Directory\Database.mdb" as the Database FileName and Path. Where 'ServerName' is the name of the PC that's hosting the DataBase, 'Directory' is the folder / path where the data is and 'Database' is the Database name.
This approach has the weakness of Share Full Folder, so someone can still delete the * .mdb file
Try to write Server + Client to see and access Data via IP LAN .... it's very safe
I am studying and writing in that direction
-
Sep 18th, 2020, 07:11 AM
#12
Re: ADO on Lan
Originally Posted by dilettante
We could list tips all day and overwhelm you. There is no substitute for the documentation, good books, and even formal training.
Not to mention good old fashioned proofs of concept - make sample projects and try them out.
-tg
-
Sep 18th, 2020, 07:16 AM
#13
Re: ADO on Lan
Originally Posted by PhuongNam
This approach has the weakness of Share Full Folder, so someone can still delete the * .mdb file
Try to write Server + Client to see and access Data via IP LAN .... it's very safe
I am studying and writing in that direction
IP addresses change... server names tend to be more static. Plus also if one machine physically needs to be swapped out, using a named server is the way to go.
As for deleting the mdb file ... 1) only if they know it's there; 2) if someone deletes it, the world is going to come to a screeching halt - fast; 3) there should be a data backup plan in place, if there isn't, then there needs to be one.
That said, I'm not a fan of shared file-based databases and would rather see SQL Server in some form or another, or even MySQL or Oracle. But, sometimes you gotta use what you have.
-tg
-
Sep 18th, 2020, 07:26 AM
#14
Re: ADO on Lan
In terms of speeding it up it would help to know what you are doing that is slow. Also you should know that filling a listview from a large recordset is probably the slowest method you could choose.
-
Sep 18th, 2020, 08:41 AM
#15
Banned
Re: ADO on Lan
sorry to post the same 2 times
Last edited by PhuongNam; Sep 20th, 2020 at 05:08 AM.
-
Sep 18th, 2020, 08:44 AM
#16
Banned
Re: ADO on Lan
Originally Posted by techgnome
IP addresses change... server names tend to be more static. Plus also if one machine physically needs to be swapped out, using a named server is the way to go.
As for deleting the mdb file ... 1) only if they know it's there; 2) if someone deletes it, the world is going to come to a screeching halt - fast; 3) there should be a data backup plan in place, if there isn't, then there needs to be one.
That said, I'm not a fan of shared file-based databases and would rather see SQL Server in some form or another, or even MySQL or Oracle. But, sometimes you gotta use what you have.
-tg
When I have free time, I will research the code and apply it to see how it works very well
https://www.vbforums.com/showthread....B-SimpleServer
However, when applying it from the Client sending SQL to the Server, how does the Server send back to the Client side ??? !!! I am confused and do not know how to write
Example: The following SQL From Client sends to Server
SELECT [SoLuong], [DonGia], [ThanhTien], [SoPhieu] FROM [DataBaseNhap]
After the server receives the SQL, how to send the Data back to the Client side ????
Regarding IP, set up static IP for better data access and connection
Because I write a small application using Ms Access * .mdb Or * .accdb is enough, so it is not necessary to use Ms SQL Server.
-
Sep 18th, 2020, 09:05 AM
#17
Re: ADO on Lan
Originally Posted by PhuongNam
When I have free time, I will research the code and apply it to see how it works very well
https://www.vbforums.com/showthread....B-SimpleServer
However, when applying it from the Client sending SQL to the Server, how does the Server send back to the Client side ??? !!! I am confused and do not know how to write
Example: The following SQL From Client sends to Server
SELECT [SoLuong], [DonGia], [ThanhTien], [SoPhieu] FROM [DataBaseNhap]
After the server receives the SQL, how to send the Data back to the Client side ????
Regarding IP, set up static IP for better data access and connection
Because I write a small application using Ms Access * .mdb Or * .accdb is enough, so it is not necessary to use Ms SQL Server.
Through the connection... you establish a connection to the server and issue commands through it, the results come back through that connection.
Same as how a web browser connects to a web server, gets the data, and brings it down to render on the client.
As for the IP ... I'm not convinced that static IPs result in anything faster or more stable than a named server connection. I will say this though, as far as I know, using an IP directly means you're going to go against a single machine, which means that load balancing is out. (You may not care about that, but someone else may) ... I beleive for load balancing to work you have to hit a named resource.
-tg
-
Sep 22nd, 2020, 11:53 PM
#18
Banned
Re: ADO on Lan
Originally Posted by techgnome
Through the connection... you establish a connection to the server and issue commands through it, the results come back through that connection.
Same as how a web browser connects to a web server, gets the data, and brings it down to render on the client.
As for the IP ... I'm not convinced that static IPs result in anything faster or more stable than a named server connection. I will say this though, as far as I know, using an IP directly means you're going to go against a single machine, which means that load balancing is out. (You may not care about that, but someone else may) ... I beleive for load balancing to work you have to hit a named resource.
-tg
On this vbforums.com I searched for 2 consecutive days to see if there is a sample code Server + Client (VB6) that uses MS Access as Database.accdb to access data via TCPIP, not to refer to writing applications without seeing
For example, from the Client side, I send to Server 1 SQL below
SELECT [TenHang], [CK(%)], [DonGia], [GhiChu], [SoPhieu] FROM [DataBaseNhap]
then after the server receives the connection to Database.accdb to access the data and send back to the client like ??? !!! details like ???
If you know, please help me
thank you
-
Oct 17th, 2020, 12:32 PM
#19
Re: ADO on Lan
Originally Posted by PhuongNam
On this vbforums.com I searched for 2 consecutive days to see if there is a sample code Server + Client (VB6) that uses MS Access as Database.accdb to access data via TCPIP, not to refer to writing applications without seeing
For example, from the Client side, I send to Server 1 SQL below
SELECT [TenHang], [CK(%)], [DonGia], [GhiChu], [SoPhieu] FROM [DataBaseNhap]
then after the server receives the connection to Database.accdb to access the data and send back to the client like ??? !!! details like ???
What you need in most remote-scenarios is called: "serialization" (to and from ByteArrays).
And an ideal ContainerObject with support for this is:
(who'd have thought) ... an ADO-Rs.
Since this question (remote-access to serverside DBs) is asked quite often recently,
I've now put an "as simple as possible" Demo-Project into the CodeBank:
https://www.vbforums.com/showthread....ss-via-http(s)
HTH
Olaf
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|