Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
Sage50 OBDC - Frequency of querying Sage?
Sage50 OBDC - Frequency of querying Sage?
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.
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.
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Sage50 OBDC - Frequency of querying Sage?
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.
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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Sage50 OBDC - Frequency of querying Sage?
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?
Side note, do the newer Sage versions use a proper database? Is it worth the upgrade?
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Sage50 OBDC - Frequency of querying Sage?
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.
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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Sage50 OBDC - Frequency of querying Sage?
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.
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.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.
SG
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Sage50 OBDC - Frequency of querying Sage?
I believe they did.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Sage50 OBDC - Frequency of querying Sage?
>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?
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?
Re: Sage50 OBDC - Frequency of querying Sage?
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.
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.
Re: Sage50 OBDC - Frequency of querying Sage?
Thanks, I'll look into it.
Re: Sage50 OBDC - Frequency of querying Sage?
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.
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.
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Sage50 OBDC - Frequency of querying Sage?
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.
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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Who is online
Users browsing this forum: No registered users and 3 guests