Posted 15 July 2005 - 06:46 PM
The same amount of data is in use, an inventory that exceeds 141,000 skus.
Under Unix, the response time is a few seconds; but once WIndows gets in the way, I could row a boat across the pond to the UK, get a spot of tea, and row back before I get a response.
So, if any of you are using SQL with at least 125,000 records in your db, and have superb performance in retrieval of large number of records, I would appreciate knowing which "flavor" of SQL you are using, and the approximate amount of records involved.
Posted 15 July 2005 - 07:56 PM
Our data bas has about 50,000,000 rows with andout 100 users.
We are on Oracle10g Standard edition 2, which is about the same price as SQL Server.
Our response time is very fast.
Are you running your app on the same box as your data base?
If you are, then windows and your data base are fighting each other over memory.
Our data base is on one server and our app is on another.
Posted 15 July 2005 - 08:19 PM
We have loads of indexes which really speeds up the queries. SQL Lite is a SYBase product and you are moving to a Microsoft Product. That means your access to your data is thru ODBC amd not API.
We also use a lot of stored procedures to do our queries and then print our reports or create screen dispalys from the results.
Posted 18 July 2005 - 11:12 AM
There are major-league international banking systems out there in ProIV. Installation sizes vary of course but there are certainly installations with billions of rows in total. The ones I've worked on use Oracle 9 and 10.
Total sizes of tables has little relevance to the performance of queries as long as your physical organization of tables is appropriate and the DB optimizer is exploiting that physical organization properly for your queries (as Bill says the issue is typically indexing as far as ProIV is concerned).
The major commercial databases (ie. Oracle, SQL/Server, DB2) have the technology to deal with multi-terabyte and even petabyte data. I would expect the free databases not to scale as far but I don't think any database will have a problem with the volume of data you're looking at.
Incidentally, Oracle has certain advantages if you are working with ProIV - it's the only database for which ProIV still has a "native" driver and there are other performance-related features in ProIV that relate specifically to Oracle.
Also, FWIW, it's certainly not always the case that putting your app on a different machine to the database will improve performance. If the server has sufficient CPUs, CPU horsepower and memory to run both, then you will typically get more thoughput by running both on the same machine - because you remove the need to format and funnel gigabytes of data across the network via TCP.
Large-scale systems often do have the app server separate from the DB server but have a "private subnetwork" between them to handle the traffic loads predictably. (We're talking here about much larger systems than you are interested in.)
Posted 18 July 2005 - 01:10 PM
Thanks for the replies, but unfortunately, the app is out of a can, and not in Pro-IV, which does me little good. I do not have access to the source, so I can not tweak the queries.
I can easily imagine db's with billions of rows. One thing I have noticed is that this version of SQL (Microsoft Database Engine) has some things in common with Access, that being all tables stored in ONE file, as opposed to multiple files as in Pro-IV. I wonder if some of those performance hits we are experienceing are not due to this situation.
I have always known that a GUI environment is bloated with overhead. I would much rather have RAW speed that all of that "pretty stuff".
I guess it a recomendation for a server shot up on steroids - maybe something in the Xeon family. Multiple processors??? Lots of memory!!
Posted 18 July 2005 - 04:34 PM
So your new solution is good looking, not really performing and possibly high maintenance !!!
multiple processors - Some software and DB suppliers charge per CPU - so with the more powerful server you could also be caught for additional licence fees and support etc.,
Your data volumes seem to be very low and hard to believe this is the problem.
Can you perhaps trial the application with smaller data volumes and establish if there is a break-point where the load spike up
Maybe you need a DB Cosultant to monitor the Database while its under load and identify the bottlenecks, are their tools available to generate stats etc.,
Does the "new" application supplier have any recommended h-w
specs with corresponding data volumes. Have you spoke with any other customers they have regarding their hw- usage
can you get a trial of the system on a new box to really ensure it'll be the solution
hope this helps
Posted 18 July 2005 - 05:11 PM
You actually can tweak the queries to an extent. Most of the SQL databases have analyzers that you can turn on to trace the efficacy of the SQL.
You may find that the query coming from the database requires a full scan on the table of 141,000 SKUs. This may be solvable by adding an index.
That said, 141,000 SKUS is not a lot of data. It may be that the server simply does not have enough RAM or that the SQL database is not being told to use as much RAM as it should. Basically, you want to check your RAM cache allocation. In general terms, caching enables significant performance gains in SQL databases.
Posted 18 July 2005 - 06:03 PM
As I'm quitting here for today, I just searched to see what MSDE was out of curiosity.. MS web site says..
Basically what that suggests to me is that MSDE is probably an intentionally performance-restricted thing that is not intended to support multiple read-write users..
Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is the free, redistributable version of SQL Server that’s ideal for client applications that require an embedded database, new developers learning how to build data-driven applications, and Web sites serving up to 25 concurrent users.
I don't know if my guess is accurate but it'd be interesting to know if that's your understanding or what it was sold to you as
Posted 18 July 2005 - 07:38 PM
The app vendor recommends MS SQL server for more than 10 concurrent users. They say MSDE is acceptable for less than 10. We are planning only 4 to 6 users. I think another poster hit it on the head, in the sense that an entire file is read prior to drawing an inventory screen. There at least 3 indexes; and i make that assumption from the fields that can be imported into the app that are REQUIRED. This is in addition to the primary key which I believe is a long integer (an AUTO-NUMBER field). I just do not like waiting 60-90 seconds for a ascreen to be drawn. This hit even (albeit a slightly lower time factor) appears on a 3.0 ghz machine (usually 45-60 seconds). When one has an impatient customer, 60 seconds can seem like hell.
PS> No one here is planning to just open up the checkbook for new, expensive hardware - yet.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users