Re: PostgreSQL
- From: "JMumf" <jmmfrd@xxxxxxxxx>
- Date: 2 Mar 2006 16:48:49 -0800
Scott:
In general any SQL database will handle all of those things better. In
most cases a lot better.
I have a table I converted to MSSQL about 200,000 records. It take
about 15 seconds to read the whole database and
produce a report. With TPS we are talking minutes for the same report.
In this case I tested reading the whole table. Usually it will be a
partial read and be much faster.
That said the way to do things is sometimes different when you use a
SQL database.
In most cases better. This is especially true with LARGE tables. On
small ones it really
doesn't make any difference. Look at ICETIP.COM for some good info.
Look at CWHandy Tools
he has some good info. CWMag always has good stuff.
One big thing is that you do not want your database doing a full table
scan.
You want it to only deliver the data you need. This means Filtering is
good even
when not on a Indexed field (it should be done on the Server). With
TPS files filtering can be
BAD if not on a Keyed field. Also stay away from incremental Locators.
An Entry Locator is much better.
I know people who only creates Primary Keys and Foreign Keys intially
on SQL databases. Then they use a
Profiler to determine if they need other Indexes once the user has a
lot of data in it. Remember you can create a Clarion Key in you
dictionary for a SQL database when a Index\ Key does NOT exist (used
for ordering).
This is VERY important ALWAYS make sure you have a PRIMARY key in each
TABLE of a SQL database without this you will have MAJOR problems. I
like to use an IDENTITY field as my Primary key. I can still have
another Unique Id in the table and make it a Unque Key. I know someone
that does this so they can use AutoNumbering and get the value of the
Identity PRIMARY Key.
Another rule is to let the server do as much work for you as possible:
Like let SQL handle Ref Integ. for you.
These are just a few items.
Overall once you start using SQL you will wonder why you ever used TPS
files.
Having a sever database engine opens up a lot of options.
I often verify my results using SELECT COUNT(*) on the SQL Engine and
compare my
Results with SQL. When doing a Process I like to do a Select Count(*)
and use that for
RecordsToBeProcessed. Make your % done accurate.
I have not used PostgreSQL myself, but have heard good things.
You really have a lot of options it really depends on what your
customer has access to.
Some good options:
MSSQL - SQL 2005 Express for small databases is free. Once it get
large then you must spend the big bucks.
Sybase SQL ASA (A lot of people really like this one, but it is not
free)
Firebird - Open Souce I hear good things about this
Pervasive SQL - more like TPS, but a real database Engine, They have a
WorkGroup Engine for small systems
Oracle - I've heard it is harder to use but great for a huge database
Mimer - I know some one that uses this. He states it is VERY standard
PostgreSQL - Has been around a long time as OPEN SOURCE.
Sounds like Scott above uses it you should get pointers
Find out how he handles Identity fields. The main thing is how you get
the value of these fields when inserting a Parent with children.
You can always use the method I stated above if you must.
Good luck,
JMumf
.
- Prev by Date: Re: Export Clarion Data File
- Next by Date: Clarion Magazine Summary For Friday, March 03, 2006
- Previous by thread: ClarionMag Subscription Special - Two Days Left
- Next by thread: Clarion Magazine Summary For Friday, March 03, 2006
- Index(es):
Relevant Pages
|
|
