I have made a client server environment with Microsoft Data Engine on the client side and SQL Server 7.0 on the server. The software has 35 clients and one server. All the clients have Windows 98, and Windows 2000 Server is the operating system of the server. The OS of the server can be changed. The software is designed using Microsoft Visual Basic 6.0.
The present system is such that three of the most active nodes have Microsoft Data Engine as the database server, which is then connected to the SQL Server (main), but still, I am not getting the speed. Please suggest to me the ideal way to increase the performance of the software.
I am not sure why you would have MSDE on the clients if you have a SQL Server, unless the clients are periodically disconnected from the network (or if you have a slow network between clients), or you have a unique functionality requirement that doesn't fit with the normal client/server approach.
If the clients are permanently connected, and you are on a LAN, and assuming you have an adequately configured server, most people get good performance having the clients communicate directly with a centralized server bypassing the need for a local data repository. SQL Server is capable of processing hundreds (and much more on larger severs) of transactions a second when used this way. Performance problems in this situation are usually tied to a difficult to query database design, inappropriate SQL, or inadequate optimization (such as indexing). This however is dependant on the functionality of your application which hasn't been described in your question so I make not judgment if any of these relate to your situation.
You may have a valid reason for using the architecture you have described, in which case you will need to provide more information.
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2003