Community
Showing results for 
Search instead for 
Do you mean 
Reply

S....L....O....W database access

Accepted Solution Solved
New Member
Posts: 14
Country: United States
Accepted Solution

S....L....O....W database access

I am writing a C# program against a PAD file.  The purpose is to expose some of the ACT data (read only) is a web service.

 

The database access is painfully slow.  I am using the Framwork.LogOn(connection string, user, password) access method.

 

The typical login takes 20-40 seconds.  I am working on a fast network with great hardware, so I am wondering - is this the best way to access the data?  How can I improve performance?  And what is taking so long?


Accepted Solutions
Solution
Accepted by topic author rlieving
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 98
Country: United States

Re: S....L....O....W database access

Rlieving, 

 

I was under the impression that it was taking 40seconds per query or SDK function call like GetGroups. 

 

A LogOn() time of 40 seconds is probably on the slow side but I wouldn’t consider it outside the realm of ‘normal’, especially if it is a large database with multiple users accessed though a network. 

 

The .PAD file is only a pointer file to the location of the .ALF and .ADF ‘database files’ which are driven and modified though the SQL server receiving its commands from the ACT UI or the ACT SDK.  

 

So, you can’t have ACT without SQL, but I think I understand that you are not connecting to SQL directly. 

 

LogOn is connecting to the SQL server that is hosting the database you are attempting to gain access too using the user name and password provided.  Just like if you were logging on to the database inside the ACT program.

 

Hope this helps,

Carlton Jones
Sage Software

View solution in original post


All Replies
Copper Contributor
Posts: 98
Country: United States

Re: S....L....O....W database access

Rlieving, 

 

 

I would approach this from a SQL performance point of view. 

I would take a look at my anti-virus and see if you could add some exceptions like sqlserver.exe and sqlbrowser.exe.  

 

 

Hope this helps,

Carlton Jones
Sage Software
New Member
Posts: 14
Country: United States

Re: S....L....O....W database access

Carlron: 

 

Thanks Carlton.  Your answer leaves me with more questions.

 

WHY would you think this is a SQL performance issue?  We have SQL loaded on this machine (in addition to the ACT flat file) and data access isn't slow.

 

WHERE would you think that the issue is happening?  On my machine?  On the server?

 

WHAT is your expectation for opening a connection to a database? 

 

Disabling virus protection COMPLETELY (just turned it off)  on my machine showed no better access time. It takes 30-40 seconds to retrieve one contact from the Framework.Group.GetGroups().  Is this normal?

 

If you meant messing with the anti-virus on the server side - well...that isn't going to happen here (or pretty much anywhere.) 

 

So, I am still stuck. 

 

Copper Contributor
Posts: 98
Country: United States

Re: S....L....O....W database access

Rlieving, 

 

WHY SQL- When connecting to the server's database or performing any sort of lookup ( GetGroups) you're connecting to the servers SQL instance though the SQL server back-end of ACT.  

 

WHERE do I think the issue is – My guess (and I would try this) is that if you setup a local database (on your machine using the local SQL instance installed on your computer – that installs with ACT) with some test data and ran your SDK program you’d probably not have such a long delay between queries – leading me to think the where is the connection between your computer and the server. Another test would be to run the SDK program on the server, and I realize that isn’t’ always possible. 

 

WHAT is my expectation – Ultimately the SDK can retrieve information from the servers database only as fast as your connection or perhaps more importantly only as fast as SQL is allowed to pass that information to you. Performance depends on a lot of factors. You should try some routine maintenance on the database (Tools -> Database Maintenance -> Check and Repair…) but make SURE to make a back up first.

 

Hope this helps,

 

Carlton Jones
Sage Software
New Member
Posts: 14
Country: United States

Re: S....L....O....W database access

Yes, that was a somewhat helpful response.  I cannot test on the server until next week, but will give that a try and see if that improves access time.

--- 

However - I still didn't get a sense of how long you thought a connection should take.  Is 30-40 seconds normal?  Should we have higher expectations?  What is the normal time for access and retrieving group data?  

 

As a benchmark - we have several web-based applications that deliver large amounts of data from SQL Servers and they load in less than one second.  The browser takes longer to load on the desktop than the page.  

 

The question I have is that I do not access Act through a SQL connection.  We are using the ALF (via PAD) file here, so it isn't going through the standard SQL connection.  I use a PAD connection string that points to a file....not a SQL connection string that connects to a database.  This is a small company and all the servers are onsite.  We don't really have any other network issues regarding speed.

 

Once the connection happens, the data comes very quickly over the wire.  It's just the connection that is causing the problem.  Your suggestions seem geared more towards data access - which is not the problem.

 

Timing:  

Declare new framework (less than 1 sec)

Log On (40 seconds)

Get Groups (group count: around 700 - approximately 1 second)

 

What else could be causing the slow connection?

 

Solution
Accepted by topic author rlieving
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 98
Country: United States

Re: S....L....O....W database access

Rlieving, 

 

I was under the impression that it was taking 40seconds per query or SDK function call like GetGroups. 

 

A LogOn() time of 40 seconds is probably on the slow side but I wouldn’t consider it outside the realm of ‘normal’, especially if it is a large database with multiple users accessed though a network. 

 

The .PAD file is only a pointer file to the location of the .ALF and .ADF ‘database files’ which are driven and modified though the SQL server receiving its commands from the ACT UI or the ACT SDK.  

 

So, you can’t have ACT without SQL, but I think I understand that you are not connecting to SQL directly. 

 

LogOn is connecting to the SQL server that is hosting the database you are attempting to gain access too using the user name and password provided.  Just like if you were logging on to the database inside the ACT program.

 

Hope this helps,

Carlton Jones
Sage Software
New Member
Posts: 14
Country: United States

Re: S....L....O....W database access

Yes, this is very helpful.

 

I appreciate your patience and sticking with me.  Thank-you.