I've been experiencing intollerable slowness in both the Windows client and the Web client using version 7.1.1.7329.

Here are some timings:

• Click on any project in the project tree:  19-30 seconds.
• Double-click a feature, defect, or task:  19-25 seconds.

Pretty much anything I clicked on took between 19 and 35 seconds.  Sometimes it took over 80 seconds (especially messing around on the users tab).  I discovered that turning off the filter made things much faster, but STILL slow (7-10 seconds).  So, I decided to examin the tables.  I was shocked to find that the few tables I looked at (Projects, Defects, Features, & Tasks), had NO INDEXES!!! aside from the primary key on the <TableName>ID field.  So, I added my own indexes.

My timings are now:

• When filter is on:
• Click on any project in the project tree:  8-12 seconds.
• Double-click a feature:  ~9 seconds.
• When filter is off:
• Click on any project in the project tree:  1 second (or less).
• Double-click a feature:  5-8 seconds.

Note that I have very few items in my database:

• projects = 70
• defects = 12
• features = 111
• incidents = 0 

I got these numbers by running the following queries:

select count(*) from projects

select count(*) from defects

select count(*) from features

select count(*) from incidents

I highly recommend to axosoft to exam how your code is querying the data and make appropriate indexes.  Generally, an index for each foreign key and an index for any "order by" clauses.  I added indexes on the tables with ProjectID + (whatever I filter by), so I had about 5 or so indexes per table on those 4 tables.

I also noticed that the function fnProjects does a "LIKE" filter on the "Path" field.  That's going to be very slow, especially since there's no index on Path and even if there were one, it couldn't be used because of the "LIKE '%" comparison.  You might want to try something like having a ParentProjectID field and if the ProjectID passed in is not null, do a recursive query (SQL Server 2005 only though).  I don't know if it'd be faster, but it'd be worth experimenting with.  You do need something for the tree structure to be indexed.  Also, search all your functions, stored procedures, and views for any use of the "LIKE '%" comparison and see if you can figure out a way to accomplish the same thing in a way that takes advantage of indexes... even if it means adding new columns.  You even might try adding a new child table.  Maybe something like a one to many from Projects to this new ProjectPaths table.  The ProjectPaths table would have many entries for one project.  Each entry for a project would have a ParentProjectID, then a number following that, indicating the order of inheritence.  Of course, the child table would use indexes (ProjectID + ParentProjectID + InheritenceOrder).  There wouldn't be any textural, brute force searching and it'd all be indexable.... just some ideas.

This is an AWESOME product, but it's WAAAAAY too slow.  At the very moment that the speed issues are taken care of, I'm going to press hard for my employer to purchase a bunch of licenses.  We have about 19,000+ desktops (obviously, not all developers).  I can't recommend it yet because the slowness is severely impacting my productivity.

Good Luck!

Quinn, unquestionably, there's something wrong with the setup.  Having 19-30 second response times to have a project refresh or open an item would be completely unacceptable to the most patient of users.  I'm sure as you can imagine, we would have -0- sales if that were the case for everybody, so lets find out what's wrong with the setup.

We can try to diagnose here or you can call our support guys.  But here are some questions to answer:

• Is SQL Server on the same machine as OnTime?  If no, are there any routers, firewalls, etc. between the OnTime machine and SQL Server?
• Provide detailed specs on the SQL Server machine - CPU, memory, OS, SQL Version, etc.  Also, is it being used for anything else?
• Do you have any personal firewalls or network analysis software installed on the machine running OnTime windows?

 

---

Hamid Shojaee
Read the Ship Software OnTime Blog
Axosoft, LLC

Thanks for the response.

Here are answers to your questions:

1. SQL Server (Express 2005) and OnTime are on the same server.
2. Server Specs:  Celeron 1.7Ghz, 512MB RAM, 3 60GB internal IDE drives, Windows Server 2003 Web Edition.  This server is hosted by ev1servers, is being used as my personal server for my own web sites, e-mail server (MailEnalbe Pro), Visual SourceSafe HTTPS server, and OnTime.  The usage of this machine is extremely low.  My own personal activity accounts for more than 90% of it's usage.  At the moment, the server has been up for about 27 days, more than 25 of those days are logged as idle time (by viewing processes in Task Manager).
3. I run the Windows Client from two machines (work and home).  Both have similar slow performance (as well as the web client).  At home, I have the default Vista firewall.  At work, I'm behind a corporate network and use an ISA server for web access.

Extra info you'll probably ask next with more that you might not have thought to ask: 

• The server is in Texas and I'm in Tennessee.
• Server bandwidth to the internet is 10mb.
• Home connection is 5mb down, 512kb up.
• Office connection is (according to speedtest.net): 812kb down, 2.354mb up.
• Home PC:  Windows Vista Ultimate, 3.02Ghz Intel, 2GB RAM, ~1.5TB hard drive (with multiple HDs, SATA, IDE, USB 2.0), hundreds gigs free.
• Office PC:  XP Pro SP2, 1GB RAM, 160GB HD, 85GB free, 2.13Ghz Intel Core2.
• Note that the home computer was using a direct SQL Server connection (when I had v7.0.x).  I haven't upgraded my home computer since I upgraded to v7.1.x.  I don't recall what the timings were at home, but they were very slow.  I have no network analysys software installed either at home or on the office PC.  I'm also using the server component to access the server from work since HTTP traffic is all that is allowed through the corporate network.
• I'll also add that I created a seperate app pool for the OnTime2007RemoteServer virtual directory.  And the virtual directory is configured for ASP.NET 2.0.  No other web apps are using the app pool.
• Also, I've been monitoring the server usage during these 19-30 second requests and the server's CPU may spike at 40% but floats around 15-20% during the duration of the 19-30 seconds, then sinks down to 3-5% (It's normal state).  Also disk activity shown in perfmon does show a small bump, but nothing significant.  Memory usage doesn't move.
• On the client machine, CPU usage drops to 0% during the wait and memory doesn't move.

Keep in mind that when I added indexes to your tables, it got much faster (but, still needs help).  Regardless of any performance tips you can give me, those database performance improvements I suggested are still valid and I hope y'all are considering adding them or something like them to you database design.

Thanks!

It's highly unlikely that any of the indexes you've made have had any impact on the performance.  Here are the most likely reasons for the slowness you are seeing:

• OnTime Windows edition going directly to SQL Server is essentially a client/server product.  As such, it relies on LAN like connectivity.  A typical LAN is 100Mb or 1000Mb.  In your case, the best you can possibly hope for is your slowest connection, which at home is 5Mb and at work it's 0.8Mb.  This is your limiting factor for OnTime Windows edition.  To give you an idea on what performance would be like if you had your database server on the LAN, you'd get SUB-1 Second response times for switching projects and SUB-2 Second response times for opening items.
• Fortunately, there's OnTime Web, which if installed on the same server as the SQL Server (or at least on a server that has high-speed connection to the SQL Server), you can get much better performance.  Here too though, you can't expect super fast performance with a 0.8Mb connection.  You should get pretty decent performance from home.  Be sure to use Internet Explorer 7 or FireFox 2 as we don't support IE 6 due to performance issues it has with executing Javascript.
• Lastly, if you definitely want to use the OnTime Windows client against a remote SQL Server, use OnTime Remote Server.  The OT Remote Server should be installed on the same machine as your SQL Server.  Be sure to use SSL.  This should be faster than if you just connected directly to SQL Server as OT Remote Server will compress the data before putting it on the wire and on low-bandwidth scenarios, that improves performance signficantly.

Hope that helps.

---

Hamid Shojaee
Read the Ship Software OnTime Blog
Axosoft, LLC

Pre-reply:  I want to say I really appreciate your quick responses and I really like this software.  Overall, I like the service and the software, so keep that in mind if the response below looks like it's trying to be confrontational.  It's not.  It's hard to be to the point succinctly and sound polite in text simultaneously.

Indexes not impacting the performance??  Really??  Do you know what could have caused the speed up I experienced after adding the indexes?  Could it have been a coincedence?  I'll take your word for it, but I'd be really surprised if it didn't considering any query with joins will HAVE to do a table scan... the absolute slowest way to query... On the other hand, if OT is doing some other processing which is significantly more intensive than the queries, then perhaps a large improvement in the queries would still be a small impact on the whole process.  I could see your point there.

• Unfortunately, putting it on the LAN is not an option, especially since I need to access it from both home and at work (or anywhere else I may be). I'm not using the direct SQL Server connection.  If I can sell my employer on this, then that situation may change, but speeding this up will greatly affect my ability to sell them on it.
• I've got OnTime Web installed on the server (same server as SQL Server).  I just logged onto my web server via Terminal Server and launched the browser from there (IE7), so I've got browser, web server, and SQL Server all on the same machine -- to eliminate any network bottleneck issues.  Using the web client (on the server), switching between projects took 9 seconds consistently, with or without filtering.  Of course, that's partially unfair to OnTime, because even though we eliminated network issues, we added CPU issues by moving the browser usage to the server.  So, from my office computer, the web performance is much better:  1-3 seconds to switch between projects (with filtering on).  Selecting a feature line takes 3-5 seconds.  Clicking the edit button for that feature takes 15~17 seconds.  This is all using the web UI.
• OnTime Remote Server:  Yes, I'm already using that.  All my reported times with OT for Windows are with that.  I have no preference to use a direct SQL Server connection or OT Remote... just as long as I can access it and it's not slow.  At work, a direct SQL Server connection is not even possible, which is why I'm using OT Remote Server.

I'm pretty sure bandwidth is not the issue.  One of my primary projects I'm working on is very very similar in nature to OT for Windows.  It's a .NET 2.0 Winforms application giving the user read/write access to hierarchically stored data.  Like OT, it has a tree view on the left, and an item view on the right, with a similar display (details view, grouping view).  Also, like OT, the data is stored on a remote SQL Server and like OT with the OT Remote, all access to the data is via web services (also .NET 2.0).  The client runs on machines in TN.  The IIS server is also in the same building on the LAN and one DB server is in Arizona and another is in Virginia... all very responsive (true, not a fair comparison).  The following is about as fair a comparison as can be:  I have also replicated the DB structure and have it on my server in TX and have the web services running on my server in TX and when running the app at home (or at work hitting the TX server) (same server I've got OT on), it's very responsive... The amount of data I transfer is significant compared to OT because each record is actually a file.  I can drag a folder from my desktop onto my app and have 64 files and about 20 or so folders copied up to my server and get a UI response showing the results in the tree view and the icon view in less time than it takes to double-click an issue in OT.  I can similarly select a folder from my app and drag it to my desktop and copy a whole folder structure down in about the same amount of time as copying them up.  Of course, the size of the files makes a difference.  But my point is that I'm using much more bandwidth than OT for Windows should be using, so I don't think bandwidth is the issue.  I could see it being a factor if OT for Windows is very chatty with the server for a single operation (maybe calling a web service, waiting for response, processing, calling another web service, and so on before updating the UI).  If that's the case, maybe OT could be optimized in a later release?  If that's not the case, maybe there's some other configuration issue involved that I've not set up properly (I'm guessing/hoping that the later is the case! :)

Since I was already using OT for Windows with your recommended set up (OT Remote on same box as SQL Server, not using SQL Server direct connect), do you have suggestions?  I greatly prefer a Windows UI over a browser UI for multiple reasons, so I'd like to stick with OT for Windows rather than using the web version.  Project switching for filtered lists seems to be faster in the web version, but editing items seems to be about the same.  Overall, I'm getting roughly the same performance out of both, but a vastly superior UI from the Windows client.

Thanks again!

Quinn, thanks for the kind words.  I totally understand the need to be direct and how that might come accross.  I appreciate the sincerity.

"Indexes not impacting the performance??  Really??  Do you know what could have caused the speed up I experienced after adding the indexes? Could it have been a coincedence?  I'll take your word for it, but I'd be really surprised if it didn't considering any query with joins will HAVE to do a table scan"

Yes, it's highly unlikely that the indexes you made affected the performance of SQL Server.  Remember, you only have 111 items in your largest DB.  For 111 items, having indexes is often times even less efficient.  However, your assessment that OnTime doesn't have indexes is not accurate.  As you mentioned before, we do have primary key indexes and that's where most of the joins are and therefore, it's unlikely that table scans are running.  But even if they were, we're talking 111 items.  There is no query in OnTime that takes more than 1 second with 111 items, on practically any hardware.  So chances are that it was a coincidence.  In low-bandwidth scenarios, the slightest usage of bandwidth by other applications would significantly impact your performance.  So lets say Windows did a check for updates during one test while there was no activity during your 2nd test.  That would explain big perf differences.

I can't respond to your claims of performance with other applications.

With respect to further suggestions, what you might want to try is signup for our hosted service (it's free for single-user or 30-day multi-user trial).  We also include the ability to install Remote Server (from the hosting dashboard page - click Login on any Axosoft page to see your hosting dashboard page after signing up).  See if that helps perf issues and let us know. 

 

---

Hamid Shojaee
Read the Ship Software OnTime Blog
Axosoft, LLC

Thanks,

I'll try the hosted account (already have one but switched to hosting my own because I was not aware that I could do the Remote Server).  I'm currently using the free, 1 user license.

I enabled the remote server on my hosted account.  Here are the results from the Windows client from work:

• Filtered on "All My non-closed features"
• Clicking on a project in the tree view = 9 seconds.
• Double-clicking a feature 7~12 seconds.
• Saving a feature 7~12 seconds.
• Unfiltered
• Clicking on a project in the tree view = ~1 second, unless there's been no activity for a minute or two, then ~4 seconds.
• Double-clicking a feature 7~12 seconds.
• Saving a feature 7~12 seconds.

This is about the same performance I'm experiencing on my own server.  The hosted account has roughly the same number of items.  I can't query it, but I'm pretty sure it's under 300 items total.

I installed the Windows client on my server and remote controled it via Terminal server (10mb connection both ways) and pointed it to the hosted db.  Here are my timings:

• Filtered on "All My non-closed features"
• Clicking on a project in the tree view = 6 seconds.
• Double-clicking a feature 8~9 seconds.
• Saving a feature ~13 seconds.
• Unfiltered
• Clicking on a project in the tree view = 1~2 seconds.
• Double-clicking a feature 7~8 seconds.
• Saving a feature ~8 seconds.

The good news is that neither my own server nor the hosted one is taking 30-ish seconds for anything anymore, though saving an item takes ~18 seconds when hosted on my server.  What's odd is my own server was taking 30-ish seconds for most clicks and up to 80-ish on occassion and even timed out once... until I added indexes to the tables, then it got closer to what I'm experiencing on the hosted account.  After creating the indexes, a significant speed increase was apparent.  After deleting the indexes, it did not slow down.  I'm not going to make any conclusions about the indexes, just reporting the order of events.  Anyway, the point being, the time that the indexes were created is when the performance improved, whether or not the indexes had anything to do with it.  Nothing else was changed so I don't know why it got less slow.

Conclusion:

• My server is still slow, though not as horribly bad as it was with no configuration changes.
• The hosted account is about the same except on saving an item it's about 1/3 faster.
• Using the Windows client on a 10mb connection has no significant speed improvement against the hosted account, so we can rule out bandwidth, unless axosoft's bandwidth is smaller than 10mb.

Thanks!

Hi Quinn,

Hamid asked me to run some quick results using remote to our own hosted account. We have a 5mb connection:

o                                Filtered on

§                                 Clicking on a project in the tree view = 3-4 seconds with 1100 records.

§                                 Double-clicking a feature 2~3seconds.

§                                 Saving a feature 2~3seconds.

o                                Unfiltered

§                                 Clicking on a project in the tree view = 4 second 1800 records

§                                 Double-clicking a feature 2~3 seconds.

§                                 Saving a feature 2~3 seconds.

Also using filter and clicking on a project I get 282 records in 1~2 seconds.

One thing I would like to test is me connecting to your hosted account with remote server. If possible send me the account information to arasha@axosoft.com and I will run a quick test. I like to test on your data set which should narrow down the issue.

Thanks in advance,

Arash

The connection information has been e-mailed to the address arasha@axosoft.com.

Thanks!