ADO SQL Server 7 slow performance.

[RickC]

I need some advise. I've created a VB6 database program which uses windows NT 4.0 and SQL Server 7.0. My code contains ADO. I know my ADO code is correct and we've loaded all patches required on the NT and Clients. My problem is that on some clients, the data input is fast, but on others, you could notice a split second delay. Why is that. We've checked our codes, connections, patches, drivers, but still can't solve it.

Would appreciate any suggestions.

Thanks in advance

[frundatz]

Have you tried running performance monitor on the NT4 Server running SQL 7, focusing on CPU utilization, pages/sec and SQL i/o?

What types of queries are having difficulties? Are they updates, selects, deletes?

I think you would need to divulge a lot more information before anyone could hazard a guess.

[TEG]

Rick, was reading your post; I use 7.0 and ADO, never really noticed any speed problems, but I also couldn't help but notice that you are not using stored procedures and I was wondering why not? Also, in your connectionstring you don't show a provider; which means it is using the default "OLE DB Provider for ODBC" which would equate to ,,,,hmmm SLOW! You have a MS product you are interfacing to so why would you use ODBC? Try using the "Ole DB Provider for SQL Server" or SQLOLEDB.1 I believe you will be pleasantly pleased with the results using this provider.
Make these two changes in your code (stored procedures and use the native provider) and your code will be screamin'! I did the same thing as you first time I wrote an app... I Couldn't figure out why I even had a server; file serving was working just as fast for 30 clients... Not true anymore.
If you need help with stored procedures or want some code just ask... Good Luck and let me know how it turns out :)

[RickC]

frundatz thanks for replying. Yes I've tried
running performance monitor and seems normal.
Here are more info regarding our setup.

1.Here is the code in my VB program that adds the record to a table in SQL Server. All I'm doing is adding a new blank record and updating it with the users' input, very basic.

With adoPrimaryRS
.AddNew

!OPERATOR_NUMBER = Me.txtFields(0)
!OPERATION_SEQUENCE_NUMBER = Me.txtFields(1)
!LINE_NUMBER = Me.txtFields(2)
!WORK_ORDER_NUMBER = Me.txtFields(3)
!SERIAL_NUMBER = Me.txtFields4(4)
!FIRST_PASS_YN = Me.txtFields(5)
!MOVE_COMPLETE_YN = Me.txtFields(6)
!DATE_TIME = Now 'Me.txtDateTime

' Save the new record to the database
.Update
End With


2. Here is my ADO code for connection.

Dim db As adodb.Connection
Set db = New adodb.Connection

db.ConnectionString = "Data Source=Barcode;User ID=;Password=;"

db.Open

'Test to see if connection was established.
If db.State = adStateClose Then

'If connection was not made
MsgBox "Connection To The Database Could Not Be Established!", _
vbCritical, "CONNECTION STATUS"

Else

'Show status as connection being made.
frmAddData.sbStatusBar.SimpleText = "Quering The Database ..."

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select OPERATOR_NUMBER,OPERATION_SEQUENCE_NUMBER,
LINE_NUMBER,WORK_ORDER_NUMBER,
SERIAL_NUMBER,FIRST_PASS_YN,
MOVE_COMPLETE_YN,DATE_TIME
from PFDATA", db, adOpenStatic, adLockOptimistic

3. We have both SQL Server 7 and Exchange Server 5.5 running on the NT.

4. The NT server has 164 megs of ram and has
3.2 and 10 gig drives.

[RickC]

TEG Thanks for your reply.

I'll use your suggestions and will trying the following connection.

db.ConnectionString = "Driver={SQL Server};server=SQLSERVER;UID=sa;pwd=pwd;database=Barcode; Trusted_Connection=Yes;"

[TEG]

Without looking through the help files to see what Driver = {SQL Server} does; I'm not sure what that does; it would seem to me though that may be the ODBC driver again? Here is the connection string I use:
"Provider=SQLOLEDB.1;Password=;Persist Security Info=False;User ID=;Initial Catalog=;Data Source="
Where Initial Catalog is the name of your database and Data Source is the name of your SQL Server.

Your connection string may be fine try both and let me know!
Glad I could help... :)

[RickC]

TEG you gave good suggestions and you were right "Driver = {SQL Server}" is the ODBC driver. I went ahead and used your connection string and for anyone else reading this Initial Catalog is the SQL server name and Data Source is the name of the database.

Anyway it still didn't fix my problem. So my next step is to use stored procedures as you (TEG) suggested.

I created the insert stored procedure on the Server but need some help in coding (ADO) on how to run it in VB 6.0. Can you or anyone else reading this point me to a sight, or give me an example.

Thanks,
Help is greatly appreciated.

[jivang56]

Example for using Stored Procedure with ADO:
-----------------------
Dim Cmd As New ADODB.Command
Dim RS As New ADODB.Recordset
Dim Prm As New ADODB.Parameter



'Assume you have a connection 'adoConnection'
Set Cmd.ActiveConnection = adoConnection
Cmd.CommandType = adCmdStoredProc
'Name of the Stored Procedure
Cmd.CommandText = "FYIADM.CUR_CWC.CWC_INSERT"

Prm.Name = "ObjectType"
Prm.Type = adVarChar
Prm.Size = 15
Prm.Value = Null
Cmd.Parameters.Append Prm

Set Prm = New ADODB.Parameter
Prm.Name = "CUR_FKEY"
Prm.Type = adNumeric
'Prm.Size = 15
Prm.Value = lngFkey
Cmd.Parameters.Append Prm

Set Prm = New ADODB.Parameter
Prm.Name = "CUR_DKEY"
Prm.Type = adNumeric
Prm.Size = 15
Prm.Value = lngDkey
Cmd.Parameters.Append Prm

Set Prm = New ADODB.Parameter
Prm.Name = "Owner"
Prm.Type = adVarChar
Prm.Size = 30
Prm.Value = pstrOwner
Cmd.Parameters.Append Prm

Set Prm = New ADODB.Parameter
Prm.Name = "EndDate"
Prm.Type = adDate
'Prm.Size = 15
Prm.Value = pdtEndDate
Cmd.Parameters.Append Prm

'Execute the procedure
Set RS = Cmd.Execute


If Not RS.EOF Then

End If

Set Prm = Nothing
Set RS = Nothing
Set Cmd = Nothing

[RickC]

jivang56, Thanks for your reply. I'll try your example out.

[TEG]

Rick, thanks for the correction; sorry I crossed the two (must have been the late night with Star Wars).
jivang56 gave a nice example for using a stored procedure; But to follow up with a few comments more:
1.) The name of your parameters that you pass doesn't really seem to matter, what is important is that you pass your parameters in the exact order that they are declared in the stored procedure.
2.) You don't have to pass any parameters if you don't want to: for instance if you are just running a query.
3.) You may want to also set the direction property of the parameter object. The default direction is adParamInput; however, you may want to return a value to a parameter, in this case you would set the direction property to either adParamOutput or adParamInputOutput. In any case, in my opinion, it's good coding practice to always include the direction property if nothing more than for maintenance (when someone else is trying to read your code)
4.) ADO allows you to return multiple recordsets; simply put a semi-colon between select statements in the stored procedure and then use the NextRecordSet property of the recordset object to access this. This can be a very helpful feature.
5.) You may find it useful to create a function to append parameters to the stored procedure command object; keep in mind that this will have to handle multiple data types though!

Hope I've been helpful; I also have to say that i'm surprised you saw no difference using the native OLE DB driver over the ODBC driver??? Been a while since I used the ODBC driver, but I do remember it being slower for large recordset returns.

[RickC]

TEG thanks for the informative comments. I'll keep them in mind. I will work on the coding this week and keep you and jivang56 and anyone else posted on the results.

[RickC]

Got my program working using ADO and stored procedures. But the sluggish problem continues on a some of our clients. Even though our configurations are all the same with all our clients. Must be some kind of dll file or system file that's causing the problem. Or will the sluggish problem go away if I load windows 98 on our clients instead of windows 95. If anyone has an answer please reply.

Thanks in advance.

[TEG]

Rick, when you say sluggish, exactly what do you mean; from the beginning you have gone from using the ODBC drivers to using the native oledb drivers and from running live queries to running stored procedures.... So, are you saying that you have seen no performance gains at all??? Is sluggish considered .8 seconds compared to .3 seconds in returning a recordset; or are we talking 2, 3,4,5, 25 seconds?
And, how big is your database? How many tables, fields, records? How much ram is in your server?
When I went from ODBC to native OLE DB my performance went from an average return of 3 - 8 seconds to less than 1 second every time depending on the recordset size. I could definitely return 1 record in under .5 seconds every time.
Also, is there a difference in your client machines (i.e. 486, Pentium, PII's or PIII's)
Is there a difference in the network cards they are using? Are they on a different router or hub? What kind of Network?
My personal opinion would be although 98 is a giant 95 bug fix; unless you already are upgrading to 98 or own the licensing; trying 98 probably won't fix your problem and is not worth the money to try.

Last questions, which version of DCOM have you installed? Are you using MDAC2.0 or MDAC2.1? Microsoft just released their abortion fix 3 for Visual Studio and with this a 160 mb Service Pack. Have you tried this yet? ADO is the up and coming technology and is solid in a lot of ways; but, like any other Microsoft product it takes at least 3.0 before it's rock hard. You definitely have my curiosity peaked with this problem; as I am still trying to understand ADO 100% myself.

[RickC]

TEG, all I'm doing is inserting a record and it takes 2 seconds. The database has 1 table, 5 fields with less than 20 records.
The server has 32 megs and we've tried 128 megs and still worked the same. There's no real difference in our clients and we've looked into the routers and hubs possibilities. We've installed DCOM95 with MDAC2.0 which was included with the SQL Server 7.0 package we bought. Have not tried the latest Service Pack yet. Will look into installing MDAC2.1

[TEG]

OOOOOOOUCH! 2 Seconds for a one table one record insert???? !!@@#$@ WOW, somethings not right! I can insert a record and hit 15 different tables; do 3 queries and return 100 records in 2 seconds (100 records with 25 fields to each). One comment about your 32 megs of memory however; NT Server needs at least that much memory just to run; try running the task manager in NT to see how much memory it recommends; 32 just isn't enough.
You still did not mention about what kind of client machines you have (486, Pentium, PII, PIII) and now i'm wondering what your server is? And what kind of network cards and network you are set up on? Do you have access to the internet over your network or are you accessing using dial up? If it's over your network, what kind of download speeds do reach ( 25k/sec, 35k/sec or maybe only 1 k/sec???)
Our network is pretty fast, but not the greatest in the world; I'm running a P2 Celeron processor in the server with 64 mb of RAM (usually 128, but I swapped that with my development machine and have more on order) overclocked to 374 mhz; right now the development server sits on a 10Mb/s drop. Most of our clients are Pentium 166 or 200. We have SP4 installed. I'm wondering if maybe you have a network problem and are getting too many collisions or something in the traffic???
Keep me posted!

[RickC]

OUCH is a good word. Our clients are Pentium 166 or 300. Our network cards are 10/100mbs cards. We also use lines of 10Mb/s and 100Mb/s in some areas. We have internet access over our network and use T1 line. We threw out network traffic as a cause, because we also tried running the program well after hours. Our server is PII 300. We seem to think that the cause may be a dll or com file that's not getting loaded correctly on the clients with the sluggish problem. Also the VB program I created is loaded on the client created by using VB's package and deployment wizzard. I'll keep everyone posted on the problem. And again thanks for your reply.

[RickC]

Ok, we found a solution to fix the sluggish problem on the clients. If we load TCP/IP on the sluggish clients and use TCP/IP instead of Named Pipes as the way to connect to SQL Server, the problem is fixed. This is the way we defaulted the clients to use TCP/IP: 1. On the desktop, double click My Computer, Control Panel, ODBC (32bit); 2. Choose User DSN tab, Add, SQL Server; 3. Type a name and description for the data source, type the name of the server you are connecting to and click next; 4. On the Create a New Data Source to SQL Server window, Click on the Client Configuration button and choose TCP/IP and click ok; 5. Click next and change default database you want to use and click next; 6. Then click next and then Ok to finish. Yes the above 6 steps created an ODBC DSN connection that uses TCP/IP. Now the sluggish probem after doing this went away. Here's what I do not understand, I deleted or removed the DSN connection we just created above and our program still performed correctly without the sluggish problems. I hope someone can explain this to me. Looks like our clent is now set to use the TCP/IP libraries. Anyways it looks like Names Pipes connection was not working on the sluggish clients. And if someone can explain this to me thanks a bunch.