Printable View
I'm using ADO in a 3-tier environment with MS-SQL server. My problem is my program is super slow. Anyone knows how will I can make it faster? I run the program in a client and gets the informations through the database server.
I need your very help. Thanks.
Cecile
The more u read, the more u do not know
Is this a real 3-tier, or a two tier. I know you use the vb program as client, the sql server as database server, but what's the middle part?
in the meanwhile, i'll give you some tips to speed things up
TIP 1
Only select the data you need. You're using several tiers, so data is probably sent over a network. Also, execution is faster if you type the columnnames rather than using select * from table.
Key thing to remember, if you don't need the data, don't retrieve it.
TIP 2
Try to use as much stored procedures as possible. Stored procedures are compiled and will execute faster than sending an SQL statement.
TIP 3
(only if you're using COM+ as the middle tier)
Make sure to pass your parameters byval if they don't need to be sent back. This saves you a roundtrip for every parameter. The default however is byref.
TIP 4
Test the application on several places on the network. A slow network is in most cases a great bottleneck. Also, try the same test at different times on the day, like at morning when there,s noone else working.
TIP 5
Make sure the server machine can handle the job. If the SQL server aslo servers as a file server, proxi server, mail server or whatever might be running there, it will effect the performance of the server
Tom Cannaerts
[email protected]
Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook
Hi Mr Tom,
It is 3-tier in a sense it uses ODBC. They say removing this one will make it faster but i don't know how will i use API for replacement.
What is stored procedures and how will i code that?
Thanks for your help. surely this is a big help.
cecile
The more u read, the more u do not know
Well, ODBC isn't really that slow, it all depends on what you are using it for. Actually, ODBC proves to be faster when it comes to handeling large amounts of data. If you do want to get rid of ODBC, I would suggest using OLEDB, which has a very good provider for SQL server (SQLOLEDB)
Stored procedures can be compared with (parameterized) queries in Access, but go way further. it,s the nearest thing to programming you have on SQL server. They can of course exist of a simple select statement.
To create on, you can use Enterprise Manager or Auery Analyzer. Both tools come with SQL server. The syntax is auite simple:
CREATE PROCEDURE sp_ProcedureName
@PARAMETERS datatype
AS
COMMANDS
Let,s say your selecting a customer by the ID. Your procedure could look like this:
CREATE PROCEDURE sp_RET_Customer
@CustomerID int
AS
SELECT CustID, Name, Country
FROM Customers
WHERE CustID = @CustomerID
If you now call the procedure like this
exec sp_RET_Customer @CustomerID=7
you should get the curstomer with id 7
This is faster because the server optimizes the execution.
You can also do other things (like inserts, deletes, type conversions, creation of tables, and many other neat tricks. if you want to find out more about them you can always consult the SQL server books online help file which came with the server, or you could buy a book. (theirs a book from Robert Vierra called Programming SQL server, it's from WROX, and its a really good book)
Tom Cannaerts
[email protected]
Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook