Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Sage50 OBDC - Frequency of querying Sage?

Sage 50 general help forum - Free help and support for all general issues
Post Reply
sageNoob
User
User
Posts: 36
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Sage50 OBDC - Frequency of querying Sage?

Post by sageNoob » 09 Feb 2021, 11:56

Sage UK - version 25.1.128.0

Hi Bruce and Community,
I've been building quite a few local apps using the Sage 50 ODBC interface and Python. This is usually quite slow because we still have many invoices in our database and the OBDC queries very long.

I know that Sage 50 is a flat file database and transfers the data multiple times on the LAN. After cloning my Sage and clearing a lot of my database, all my queries are significantly faster (milliseconds instead of minutes). With that sort of speed, I could potentially create a much faster WebApp to read my data from Sage, ditching the clunky UI of Sage 50. It can also allow mobile browsing of sage invoices and products.

My only issue is whether it's a good idea to load data from Sage over and over, using OBDC? Refreshing every 2 minutes or so? Will it cause possible data errors or table locking?

Thanks.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4407
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Sage50 OBDC - Frequency of querying Sage?

Post by brucedenney » 09 Feb 2021, 12:05

Reading data is unlikely to cause issues.

However, if you have a huge amount of network traffic this could cause issues.

I would do the following 2 things to mitigate any issues.

1/ Run the process on the PC that has the data so it does not travel over the network.
2/ Run the process as infrequently as is operationally viable.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

sageNoob
User
User
Posts: 36
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Sage50 OBDC - Frequency of querying Sage?

Post by sageNoob » 09 Feb 2021, 16:12

Thanks Bruce. I do think 1) is the way to go. Operationally, I'm thinking retrieving the INVOICE and INVOICE_ITEM Tables every 2 minutes should be sufficient and the STOCK table every 1 hour or so (we only updated stock quantities once in the morning). I've noticed that the OBDC queries work on live data as well i.e. when an invoice is being edited.

Side note, do the newer Sage versions use a proper database? Is it worth the upgrade?

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4407
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Sage50 OBDC - Frequency of querying Sage?

Post by brucedenney » 09 Feb 2021, 16:42

Every 2 mins seems very frequent to me.

Users might find performance is degraded. Try it and see.

Sage 50 has been slowed down with the last couple of releases (the transaction rate), I think this is because of issues with large transaction rates and Sage Drive (Sage Remote). The ODBC drivers do not seem to be slowing, but they were very slow to start.

I think they will never put a decent database back end on it, because if they did they would remove the opportunity to upsell to Sage 200 and that would be against the interests of their shareholders.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

SomeGuy
User
User
Posts: 15
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Sage50 OBDC - Frequency of querying Sage?

Post by SomeGuy » 11 Feb 2021, 08:04

brucedenney wrote: 09 Feb 2021, 16:42 Every 2 mins seems very frequent to me.
I' agree with this every 2 minutes seems a tad excessive but it depends on how frequently the data is changing and the specifics of your use case. I worked on a project a few years ago where we extracted the data from Sage in to SQL Server and had created CRM integration to this. The data was refreshed once every 24 hours as an overnight process but that was deemed to be OK as it wasn't vial that the data was bang up to date in that case.
brucedenney wrote: 09 Feb 2021, 16:42 I think they will never put a decent database back end on it, because if they did they would remove the opportunity to upsell to Sage 200 and that would be against the interests of their shareholders.
I recall quite a few years back Sage were going to replace the flat files with a proper database and actually did a lot of work on it. They reached out to some 3rd party developers and there was a beta program. It did show a lot of promise but for some reason the whole thing was cancelled and swept under the carpet.

SG

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4407
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Sage50 OBDC - Frequency of querying Sage?

Post by brucedenney » 11 Feb 2021, 10:46

I believe they did.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

sageNoob
User
User
Posts: 36
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Sage50 OBDC - Frequency of querying Sage?

Post by sageNoob » 12 Feb 2021, 17:43

>I' agree with this every 2 minutes seems a tad excessive but it depends on how frequently the data is changing and the specifics of your use case.

Well, we are entering 1-3 new invoices every hour or so. My idea was mainly for someone to see the latest invoices on the go - as the warehouse is using the sage invoices as also the picking lists (our orders go the next day). The overnight update would be a great idea if we were operating 1 day ahead of schedule. Unfortunately we are pretty much dependent on sage throughout the day for orders going the very next day.

I think I can curb it to 30 minutes update, that sounds sufficient?

>from Sage in to SQL Server and had created CRM integration

That sounds like a viable option - do you have any guide or resources I can follow to implement this? We are using a VM to run a server for our sage, do you think it would be okay to set this up on the same VM?

SomeGuy
User
User
Posts: 15
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Sage50 OBDC - Frequency of querying Sage?

Post by SomeGuy » 15 Feb 2021, 07:04

sageNoob wrote: 12 Feb 2021, 17:43 I think I can curb it to 30 minutes update, that sounds sufficient?
Sounds OK to me but it depends on how soon after entering a new invoice you need it to be visible in the warehouse. Obviously a delay could be anywhere from 0-30 minutes depending on the exact timings of the data entry and the sync operation. If you can wait for up to 30 minutes then the timings sound OK. If not then more frequent syncs may be required. My suggestion would be to start off with a 30 minute interval and see how well it works for you. As long as you make sure the sync interval is configurable in your integration it should be easy to adjust it up or down as necessary.
sageNoob wrote: 12 Feb 2021, 17:43 That sounds like a viable option - do you have any guide or resources I can follow to implement this? We are using a VM to run a server for our sage, do you think it would be okay to set this up on the same VM?
I don't have a guide I'm afraid. It was a good few years back and I was working for a different company at the time.
Basically we used SQL Server Integration Services (SISS) to extract the data from Sage via the ODBC driver, write it in to some tables in our database and set this up on a 24 hour schedule to happen in the small hours of the morning to avoid any contention with users during the day. Our CRM integration then simply queried the tables in our database. I'm afraid that I don't recall the specifics beyond that but it was relatively easy to set up.

sageNoob
User
User
Posts: 36
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Sage50 OBDC - Frequency of querying Sage?

Post by sageNoob » 15 Feb 2021, 12:33

Thanks, I'll look into it.

Davx420
User
User
Posts: 1
Joined: 02 Sep 2021, 18:50
Sage Version: Other

Re: Sage50 OBDC - Frequency of querying Sage?

Post by Davx420 » 02 Sep 2021, 18:58

Sorry to bump an old post, but you shouldn’t have any issues with a 2 minute frequency.

We are currently and have been running six different applications which use ODBC with some large queries. All run every 30 seconds! These aren’t even staggered so the querying happens pretty much at the same time.

Queries are run locally where the data is stored, with another six network users working concurrently and no adverse issues, slow downs or data corruption.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4407
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Sage50 OBDC - Frequency of querying Sage?

Post by brucedenney » 03 Sep 2021, 11:03

The frequency that you can poll the data is not really the issue it is the time to return data.

If you try doing it in real time in an application, then the time it takes to do something such as populate a dropdown list with products using odbc can easily be 2 or 3 seconds even with only a few thousand products, which is far far too slow for a user interface.

The bottom line is that you can not query more frequently than it takes to return the data. If you did, then the queries would stack up on top of each other and eventually crash.

I have seen plenty of queries on large data sets that can take minutes to return, try reading some of the projects tables and you will see.

If you have simple carefully crafted queries that use indexed fields and only return a small amount of data then the time to return data is pretty small so frequency can be increased.

If you have a query, returning the entire audit trail, then this will take a lot longer than all customer accounts starting with A.

From an operational point of view, most of these systems are better if they are designed to have plenty of headroom for the system to sow down as it grows. Each version has a different performance, the next upgrade, the next record added, the new AV, another process slowing down the server, any of these could be the straw that breaks the camels back.

I recognise that it is possible to run some queries with a short frequency. However, based on my past experience, I would always leave a big margin, so that the changes the future will bring do not break things.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot], Bing [Bot] and 0 guests