9 replies to this topic

[Bob Filipiak]

I have been spoiled by the low overhead that old green screen Unix version of PRO-IV has as compared to Windows. We are in the middle of a migration from PRO-IV to a software package that uses MSDE or as one person put it: "SQL Lite".

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.

Thanks

Bob Filipiak

[Bill Loven]

Bob,

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.

Bill, HTH.

[Bob Filipiak]

Bill,

Currently, the answer is yes regardiing db and app.

However we have realized that a separate server is most likely the only way to go.

50M records, wow. How fast are your querries run??

Bob

[Bill Loven]

Bob,

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.


Bill HTH.

[Richard Bassett]

Bob,

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.)

[Bob Filipiak]

Guys,

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!!

Thanks

Bob Filipiak

[George Macken]

Hi Bob

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

Rgds

George

[Joseph Bove]

Bob,

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.

hth,

Joseph

[Richard Bassett]

Bob,

As I'm quitting here for today, I just searched to see what MSDE was out of curiosity.. MS web site says..

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.

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..
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

[Bob Filipiak]

Richard,

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.

Bob Filipiak.

PS> No one here is planning to just open up the checkbook for new, expensive hardware - yet.