So, I?m importing inventory into POS 2009 & it?s getting slower & slower. I will end up with about 350,000 items in my inventory (were a book store). I have gotten several EXCELL files from my Suppliers & one big DB in ACCESS from my main book vendor, with my item info (about 12 fields each). Any suggestions on how to speed things back up. We don?t physically have all the items in the store, I just want all the info in my DB so when I do get them, my staff won?t be the ones entering all these into inv. I actually have a much bigger Book DB that I would like to import (969,000), but I?m afraid to put all them in, it may shut the whole thing down. MS told me that 09 uses SQL 2008 & should easily handle over 1,000,000 items however the more I import, the slower POS gets. Any suggestions or advice would be appreciated.
I think you are trying to use your POS system as a repository for data that doesn't belong there ("point of reference" versus "point of sale"). While this might work if you add maximum RAM everywhere and the full version of SQL Server, it's not your best choice. All searches and reports will have to have to parse all your data all the time.
Analogy: If you want to write a novel you aren't going to insert every word from the English Oxford Dictionary into your manuscript. You only need specific words for your story.
In your case 1M+ books isn't the data you want to import (or maintain) in your local POS database. You only want items you have purchased (in stock) or are actively purchasing and selling. All other data is readily accessible to you by seaching your supplier files (via online resources or supplier websites). Import new products as needed whenever orders are placed.
We have run into this issue in other markets where access to information far exceeds any local demand for it. We address this with custom import tools in POS or Manager which allow users to view/search supplier catalog data and import on demand. Example (US Hobby market customization):
formatting link
The other option is to deploy a utility to import PO's including new items (when found). In this case the spreadsheet data you import is limited to only you bought -- a very tiny subset of your supplier's data.
Stick to this plan and you'll have a concise story to tell that meets your store's sales and purchasing habits. Avoid being a repository of useless data that will never sell (and only clogs the pipes). Instead use the Internet as your virtual warehouse.
Retail Realm's import tool supports this scenario by importing PO data based on ordered product only or providing bulk import when warranted (all or selected data).
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.