Technical and Installation Discussions

[samoroso]

I want to ensure I am doing everything right here as I can connect and retrieve data, but it is horrendously slow.  Simple query takes over 1 minute to process.    I am connecting using a C# Web Application using the OdbcConnection class.  Here is my connection string I am using below:

string connect = "DSN=SOTAMAS90;Description=MAS 90 4.0 ODBC Driver;UID=DTA|ABC;PWD=password;Directory=\\path\\MAS90;Company=ABC;CacheSize=4;DirtyReads=1;BurstMode=1;SERVER=NotTheServer";

The query I am executing is:

"Select TOP 20 CustomerNo From AR_Customer"

Again, it's a simple query as I'm just working everything out at this point.  I have also tried creating a SYSTEM DSN and it doesn't seem to help performance much either. 

Any tips or advice would be greatly appreciated.  If you need anything else, please let me know also.

Thank you. 

[BigLouie]

Welcome to Sage Community!

1. Take out this part "CacheSize=4;DirtyReads=1;BurstMode=1"

2.  Change this: "Select TOP 20 CustomerNo From AR_Customer" to this "Select * From AR_Customer"

[samoroso]

Thanks for the kind welcome and quick response.  I followed your advice and have not seen any improvements.  In fact, the last time I ran it, I received a ODBC Driver "Timeout expired." error.

[BigLouie]

I have used:

strConnectionString = "DSN=SOTAMAS90;UID=XXX|YYY;PWD=ZZZ"

Set Connection = CreateObject("ADODB.Connection")

Connection.Open strConnectionString

Where XXX = user ID, YYY = Company Code and ZZZ = password.

[samoroso]

For reference, this is the code I am using:

 OdbcDataReader dr = null;

string strQuery = "Select * From AR_Customer";

OdbcCommand cmd = new OdbcCommand(strQuery, cn);

Then using the DataReader to loop through and display results.

[cspirz]

This is what I use: {partial)

            builder.Append(("Driver=MAS 90 4.0 ODBC Driver;UID=" + ""));
            builder.Append((";PWD=" + ""));
            builder.Append((";Directory=" + str));
            builder.Append((";Prefix=" + str2));
            builder.Append((";ViewDLL=" + str_MASPath));
            builder.Append((";Company=" + "ABC"));
            builder.Append(";LogFile=\\PVXODBC.LOG");
            builder.Append(";CacheSize=32;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SILENT=1;SERVER=NotTheServer");

I don't connect with DSN, seems to work better.

Other than that, maybe use Stopwatch and/or profile the assembly to see where the time is going?

[samoroso]

Thanks.  That does make it run a little bit faster, but it's still unusable for now.  Most of the time I noticed is taking place during the opening of the connection (around 20 seconds).  Anyone have any idea why this would be happening?

[samoroso · Accepted solution]

Okay, we moved our solution "closer" to the server in which the database resides.  That resolved the issue.  Thanks to everyone who contributed.

[ageorgiou]

"ODBC;DSN=SOTAMAS90;Driver=MAS 90 4.0 ODBC Driver;UID=XXX;PWD=xxx;Company=XXXX;Directory=mydirectory"

When i connect is poping up a window asking to choose the company the user name and password.

After i do that i select data with no problem.

How do i eliminated the need to popup widow asking me to select the company and the user and pass?

thank you in advance on this one.

Andrew 

[Heather]

Not my area of expertise.  But I can tell you that the following ODBC connection string works for me in Access.  No popup.  I'm running v4.2.

ODBC;DSN=SOTAMAS90;UID=ZZZ|XXX;PWD=;Directory=M:\MAS90;SERVER=NotTheServer

ZZZ is the user id

XXX is the company code