The current problems that I am having is. When a item is scanned it takes 30 seconds to come back with a product. I am wanting to run a windows 2003 server running msde and in the future switch it to sql 2005. My database is around 130 megs and growing. In 2 years it will be around 1gig or larger. I have 2 clients currently but may grow up to 5 clients.
Typically the fetch of an SKU (roughly the same thing as what you are doing) takes about 275 ms using a full blown copy of SQL Server. It shouldn't be a bunch slower for MSDE unless you are running into connection throttling somehow (MSDE only permits at most 5 running threads at one time).
Are you certain that the delay is really happening inside MSDE, and not somewhere else in your system?
Poke around in the Task Manager a bit. See if anything there attracts your attention. Be sure to check to see how much memory various processes are using, how much memory the machine has used, and how busy the CPU is.
Are you familiar with Performance Monitor? It can help a bunch trying to diagnose this kind of problem.
Well I just tested it by scanning some items. It uses about 30%-70% of the processor. This is running on a windows 2003 with sql server 2000. The hardware is a xeon 2.4 512 megs of memory. This machine is very effecient and works like a wal-mart. It comes up as fast as you can scan. But uses a lot of the processor. My other sites like I said our pentium 4 with 512 running xp pro and msde 2.8.
So the question is what should I roll out for a server to replace the desktop p4 machines? Also is there something I should be looking at in sql itself? The % of processor was with only 2 machines scanning items which is not very uncommon.
The biggest problem is that MSDE is intended to be a run-time package for applications developed with Microsoft Visual Studio. You are sorely hampered trying to figure out what your performance problem is due to an almost total lack of tools.
The official gospel from Microsoft is that you need to report the problem to your application vendor, and have them either re-tune your database for you or sell/re-sell you the tools to allow you to tune it yourself. Once you get a full copy of SQL Server (even the BackOffice version which is somewhat limited compared to the full-featured versions), you'll be much better equipped to deal with this kind of problem.
Right now you are kind of like the merchant that buys a vending machine that is serviced by the seller... You have no keys, no tools, no access to the inside of the machine, and all you can do is report malfunctions to your vendor... A nice, simple, black-box arrangement where you can't monkey with things even should you want to!
MSDE gives the end user OSQL.EXE and BCP.EXE, but not much more... No Enterprise Mangler, no Query Analyzer, no Profiler. This is plenty for the folks who want a "data appliance", but doesn't give you much in the way of tuning tools.
I'm pretty sure that the Developer Edition of MS-Access allows run-time distribution of MSDE, but I haven't checked the licensing on it lately.
a 130MB database.. is much of it in only a few tables? I'm wondering if it really is indexed properly.. A product lookup is basically just a 'select' query right? maybe a few joins or such..
Also, there are some free management utilities for people without enterprise manager.
Check out http://www.aspfaq.com/show.asp?id=2442
Some cost money, but not nearly as much as any real version of sql server.