procedure Speed up

Is there any chance that you could do the calculations in the query instead of in the code?  In other words, instead of selecting a set of records to work on and then processing them one at a time, do the INSERT something like below?  
Also, the BDE has been deprecated for quite a while now and SQL Server Express is free and a lot more powerful.  Paradox has had known issues for a long time, although it does still work if it isn't heavily loaded. ;-)

INSERT INTO DummyTable
( 
Field[0],
field[1],
field[2]
)
SELECT field[0], 
       Some Calculations, 
       Some Calculations]
FROM   table1
WHERE whatever);

Select allOpen in new window

Of course it is slow. If you are using large tables or queries then you need to try to avoid loops (for, while, repeat, until) as much as you can. Simply do all calculations with SQL.
SQL example for Funct1:

Select Sum (Table_Sum) as Total_Table_Sum From (SELECT Sum(Field1) AS Table_Sum
FROM Table1 Union Select Sum(Field2)  AS Table_Sum From Table2);

Also make all While(s) disappear from the code (switch to SQL), you will notice instant gain in speed.

Regards,
B

This is the real source code of Function1

any improvements should be done to speed it up?

function CalcQTY(PPPStore   : String;PPPNumber : String;PPPSource : String; WhatEx : Integer; Exclude : String; EndDate : TDate) : Real;
var
  GOrders, LOrders, First, LDInvoices, DInvoices, AdjustPlus, AdjustMinus, GroupMake, GroupMakeMinus, MI, TIN,TOUT : Real;
  SDate : String;
  CalcQTYQuery : TQuery;
begin
  SDate := DateToStr(EndDate);
  CalcQTYQuery := TQuery.Create(Form1);
  CalcQTYQuery.DatabaseName := 'MyDataBase';
  { Starting the job }
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "GlobalOrderList.db" d, "GlobalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '")  AND (D1."Date" <= "' + SDate + '") AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    GOrders := CalcQTYQuery.Fields[0].AsFloat
  else
    GOrders := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "LocalOrderList.db" d, "LocalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' +  PPPSource + '") AND (D1."Date" <= "' + SDate + '")  AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    LOrders := CalcQTYQuery.Fields[0].AsFloat
  else
    LOrders := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '")  AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "1.0")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    LDInvoices := CalcQTYQuery.Fields[0].AsFloat
  else
    LDInvoices := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '") AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "0.0")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    DInvoices := CalcQTYQuery.Fields[0].AsFloat
  else
    DInvoices := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "Adjust.DB"  WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND ("Adjust.DB"."Date" <= "' + SDate + '")  AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND (QTY > "0")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    AdjustPlus := CalcQTYQuery.Fields[0].AsFloat
  else
    AdjustPlus := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "Adjust.DB"  WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND ("Adjust.DB"."Date" <= "' + SDate + '")  AND (QTY < "0")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    AdjustMinus := CalcQTYQuery.Fields[0].AsFloat * -1
  else
    AdjustMinus := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "FirstTime.DB"  WHERE (Number = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    First := CalcQTYQuery.Fields[0].AsFloat
  else
    First := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(D1.QTY) FROM "MI.db" d, "MI List.DB" D1 WHERE (d.Store = "' + PPPStore + '") AND (D1."MI Number" = d."MI Number") AND (D1."Part Number" = "' + PPPNumber + '") AND (D1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    MI := CalcQTYQuery.Fields[0].AsFloat
  else
    MI := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(d1.QTY) FROM "Move.db" d, "MoveList.db" d1 WHERE (d."Out Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    TOUT := CalcQTYQuery.Fields[0].AsFloat
  else
    TOUT := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(d1.QTY) FROM "Move.db" d, "MoveList.db" d1 WHERE (d."In Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    TIN := CalcQTYQuery.Fields[0].AsFloat
  else
    TIN := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "GroupMaker.DB"  WHERE ("GroupMaker.DB"."GroupNumber" = "' + PPPNumber + '") AND ("GroupMaker.DB"."Source" = "' + PPPSource + '") AND ("GroupMaker.DB"."Store" = "' + PPPStore + '") AND ("GroupMaker.DB"."Date" <= "' + SDate + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    GroupMake := CalcQTYQuery.Fields[0].AsFloat
  else
    GroupMake := 0;
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add('SELECT SUM(D.QTY) FROM "GroupMakerList.DB" D, "GroupMaker.DB" D1 WHERE (D."Part Number" = "' + PPPNumber + '") AND (D."Part Source" = "' + PPPSource + '") AND (D1.Number = D."Maker Number") AND (D1.Store = "' +  PPPStore + '") AND (D1."Date" <= "' + SDate + '")');
  CalcQTYQuery.Open;
  if not CalcQTYQuery.IsEmpty then
    GroupMakeMinus := CalcQTYQuery.Fields[0].AsFloat
  else
    GroupMakeMinus := 0;
 
CalcQTYQuery.Free;
 
  Result := (GOrders + LOrders + AdjustPlus + First + MI + TIN + GroupMake) - (LDInvoices + DInvoices + AdjustMinus + TOUT + GroupMakeMinus);
  
end;

Select allOpen in new window

Anyway, consider switching to some kind of database server. I guess that you plan, if you have spare time it will take you a week of reading and a week of coding.

Regards,
B.

I upgraded to Absolute Database, so now I have two copies of my App.

one using BDE, Paradox
another using Absolute Database

 Guess what?

as for the slow function; nearly same execution time for both databases (paradox & absolute)

BDE Paradox:  21 Minutes , 31 Seconds
Absoulte Database : 22 Minutes , 17 Seconds

 okay

 I used ImMemory Tables for both functions in Absolute Database copy of my Application, and all functions and tables I used in this calculations changed to Inmemory Tables and queries, imagine the result??

 a slight performance improvment for Absolute Database

BDE Paradox:  20 Minutes , 43 Seconds
Absoulte Database : 19 Minutes , 22 Seconds

I know Absolute Database is not too fast, but evidently has it's advantages. I used to get speed increase in it when I deleted excessive indexes on the table. But anyway I am surprised I expected ABSDB to be significantly faster than Paradox. It can also mean that there is a slow part in the code. If above function is working then try to get rid of the others While and For (s) messing anywhere where messing with the queries.

B.

remove this :

DummyTable.DisableControls;
 DummyTable.EnableControls;

senad: removed and same performance

   Actualy i just used them yesterday only, before that I didn't call these methods and same slow performance

FactorB

  What you suggest for a database which migration from BDE will not take much coding?

Enabled controls update screen and waste processor power. Looks better as it is. Theoretically I am right, practically need to be tested.

How about changing field type from AsFloat to Integer ??

fields should be a floating values, because it is Items costs

Rtwoolf wrote the best review about migrating from Paradox, it was really long and substantial. The end point was it you make something for a smaller market consider Firebird or Postgre, I would add Firebird over Postgre as smaller footprint and really light, plus there is embed version that works with dll and without classic server. Postgre over Firebird as more mature, feature full and better documented...  If I was choosing, I would take Firebird. I simply like them. :)

Integer (small integer) should do just fine.
If they are item costs then it is perhaps best to change the field type to currency.

Note: As a passing comment, your column name selections are less than
          desirable in that they include spaces and one of them ("Date") is a reserved
          word.  This will cause you now end of grief and additional maintenance
          issues.
Question:
          Is there an index on each of the tables on the columns used in the WHERE
          clause (except for the cases or qty columns) preferably in the order that they
          appear in the WHERE clause?

Passing observation:
          You can change the code so that you don't do more work than necessary by
          changing:
CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add(
to  

CalcQTYQuery.SQL.Text := (
Also, if you set up the queries as Constants with %s where the parameters are, you can use a statment like
CalcQTYQuery.SQL.Text := FORMAT(YourSQLConstant, [PPPNumber, SDate, etc.]);
If this were a SQL Server database, I would recommend using a stored prcoedure to accomplish these calculations and, in fact, I would probably feel comfortable in refactoring your SQL so that all of the calculations are, effectively, accomplished in significantly fewer (if not a single) SQL statements.  However, my memories of Paradox are that complex SQL chokes, so I am leary of refactoring the SQL.

8080_Diver: answering the question: most of columns appear in the WHERE clause are not indexes BUT some of them such as Master-Detail tables like in:

(d1."Order Number" = d."Order Number")

d1."Order Number" is an index in d1 Table.

Do you mean that having field names with spaces or a keywords makes the Query slow to execute?

field names should not contain spaces.
kewords are also prohibited.

yes senad, but after all, they are working just put the field name between " " and it will work

you wouldn't beleive what is 'supposed' to work and why it 'doesn't work'...
I had nothing but trouble with quotes so I try to avoid them.
:-)

Why I got this error here?


SQLError.jpg

either you should give a name to the subselect or it can't do subselects

select X from (select X, Y, Z from table) as A

note the as A

if you want performance then Absolute is definetly not the way to go

you queries would take 0.2 sec on MSSQL or Oracle ...
That's what you get with a server database, it's designed for speed !

Absolute is designed for embedding, not speed

remove the first 'from' and 'select':
SELECT XXX,XXX,XXX FROM XXX ORDER BY XXX,XXX,XXX;

don't agree with you geert,absolute is very fast too ....

Greet so you don't agree that the code is also slow? same code will take 0.2 sec on MS SQL or Oracle?

senad, o ?
would you equip a desktop pc with
the processing power of a server with 4 cpu and ultra fast scsi drives ?

absolute is limited by the money you spend on a the desktop pc, or a notebook
in other words slow drive, and 1 processor

the network is the bottleneck with client/server

you wouldn't do it like this with sql server or oracle.

you would write a stored procedure which does all the queries on the server

the way you do this in the procedure is a other scope
seems like you want the sum of the qty column over several tables

you just need to call the function to get the result
using a storedproc component

or use a snapshot (materialized view) to select from
the materialized view gets rebuilt when a insert/update/delete happens
a select is instantanious

ah, but it doesn't have procedures and functions
and neither materialized views

so you are very limited for tuning options

create function totalsum return number is 
  temp number(53,20);
begin
  select sum(calc_GOrders)+sum(calc_LOrders)+sum(calc_AdjustPlus)+sum(calc_First)+sum(calc_MI)+sum(calc_TIN)+sum(calc_GroupMake))-(sum(calc_LDInvoices) + sum(calc_DInvoices) + sum(calc_AdjustMinus) + sum(calc_TOUT) + sum(calc_GroupMakeMinus)) as Total_Table_Sum 
    into temp
  From 
  -- rest of query from FactorB
 
  return temp;
end;

Select allOpen in new window

I agree that Absolute has nice features and is pretty SQL-92 compliant, but is also slow, maybe faster from some other file-system databases (this is where most people are impressed when switching from Paradox to Absolute for example), but no close to a speed of a server. Sorry to say that, but server drives in tenth gear compared to old school databases (no offenses I still like and use them).

Well guys,

 even if my Application is a desktop application and no need for server database, SO using a server database for a desktop application, will make a huge difference in performance?

if yes, even on a single proccessor pc?

besides all of this, have you ever considered what would happen if
some customer were to ask for a restore of the data from last week
and you had allready altered your exe ...
and forgot to take a backup of the exe then

what Data has to do with exe?

backup and restore is a database engine issue

I know paradox doesnt have data backup option

To backup/restore Paradox, I used to disconnect database and then copy from and to all paradox related files.

Extensions listed from:
http://www.delphi32.com/info_facts/faq/faq_89.asp

.DB       Paradox table?
.FAM       listing of related files (like a table's .TV file)?
.LCK      Lock file?
.MB       Blob file?
.PX       Primary index?
.TV       Table view settings (not used by BDE)?
.VAL       Validity checks and referential integrity?
.Xnn       Secondary single-field index, numbered?
.Ynn       Secondary single-field index, numbered?
.XGn       Composite secondary index ?
.YGn       Composite secondary index ?

Maybe there is another method ...

if it is like this, then make your data files and Folder1 and when need a back copy the whole folder to a backup folder and use a good naming method to name a back folders with date, or use ini files

and copy backup folder to data folder when a restore needed.

is there an exact answer for this

Using a server database engine such as MSSQL , Oracle, MySQL for a desktop application, will make a huge difference in performance? even if my application using single-user mode only?

if yes, even on a single proccessor pc?

yes, there will be better performance
especially when taking into account stored procedures, triggers, snapshots etc

with ordinary flat tables you need to move the data from the database to the exe, then process

with a server database some tables may be in cache (memory) thus skipping this step

do you want to focus on writing a fast app or writing code to process the data fast ?
it looks like you have a problem with processing the data fast, because of the limited capabilities of absolute
this is allready a specialisation of server databases, so why reinvent the wheel

i'm not saying absolute is bad, wrong or slow for what it is designed for
i think you have actually bumped into the limitations of absolute and you obviously need more than they can offer
so i would suggest: take it to the next level (or 2 levels)

a stress test usually points this out

your question should be:
can my program/database process my requests for data/processing at the speed i want on a given system
did you write a test for this for absolute ?
if not, then you just ran into the failure of such a test

Yes, checked with Absolute DB/Firebird (2.2 GHz Pentium IV 1 GB of RAM), plus Firebird was coded with stored procedures that gave extra speed. Just don't use embedded version of Firebird as I read somewhere that is slower than Classic or Super Server.

just look at it like this (with rough maths):
on a windows system every exe gets a slice of cpu time based on priority

lets say we have on your system 20 exe running, equally divided is 1/20 per sec of CPU time

now you have 1 exe to do all so that's 1/20 of a sec

now change that to 1 server database (1 service) + your exe
that's allready 2/20 of cpu time per sec (= 2 times as fast)

and normally a server database gets higher priority so 3/20 of cpu time per sec

<<<
Geert_Gruwez:

<<<with ordinary flat tables you need to move the data from the database to the exe, then process>>>

  And the same when using Delphi with Direct Access components to Oracle for example? or proccessing data with oracle enging will be on Database executable?

<<<do you want to focus on writing a fast app or writing code to process the data fast ?>>>

  A code to proccess the data fast.. But when I asked this question , I got from answeres here that I have two problems:
1- database engine I use is slow
2- my code accessing the data and doing calculation is also slow (because Absolute database give slower result than BDE , Paradox)

I have two copies of my application now, one using BDE, Paradox, and the other is Absolute databse, and both are slow, BUT Absolute database is slower by 30% for the above procedure. thats why ppl here told me something is slow also in your code.


<<<a stress test usually points this out
your question should be:
can my program/database process my requests for data/processing at the speed i want on a given system
did you write a test for this for absolute ?
if not, then you just ran into the failure of such a test>>>

  I considered my slow procedure as a stress test, so Paradox and Absolute faild :/

well 8080 driver, what about Oracle XE then? free and limited to 4GB, 1 processor, 1024 RAM, same as SSExpress.

the migration should be done anyway, so why not choosing #1 database engine !

@i7mad,
Obviously presented from a person with Oracle experience. ;-)  Since I haven't worked with Oracle for some time now (last time was in about 2000 ;-), I am not up on the Oracle offerings, so I offered what I do know. ;-)  I suppose I could throw Teradata into the mix as well because I know that there is a "Demo" version available and it can do some really serious stuff . . . I rather enjoyed working with Teradata. ;-)
In any case, there are free versions of stronger databases out there and moving to one (almost any one of them will do) will provide enhanced performance . . . assuming that the database is designed well and the SQL, Indexes, SP's, etc., are well designed.

Thanks guys! you were very helpful

the XE is a magnifisant free database and nearly no limitations (yeah, the 4Gb, but for a desktop app, this is more than sufficient)
i use it on my notebook
at work, i have the full oracle, the same queries work

great tool

nice answer....(it was an answer,right?)
however for desktop DB nothing compares to Absolute database.
access is also good choice.
sql server is used by those who need a server.Not desktop database.
Oracle ?
In my opinion (just my opinion) is a lousy database.
For the reason that you must take time (and I mean a lot's of time) to learn it.
And in the end you find out that the SQL server from MS is just as good only taking you 20% of the time you need to get worked up with Oracle.Oracle is for masochists who like to use F keys to get an orgasm.Dr. Freud would label is as an obsessive need to press on mothers tit.
On work we use Oracle enterprise...I can tell you first hand SQL 2008 MS outfarts it all the way...
F... the database where you are required to go to school to mess with it.
I did try all and I am telling you my experience.Nothing I cant do on MS SQL 2008 that I can do on Oracle.Oracle is ripe for junkyard in my opinion.Sales tell the tale.Well see in 5 years time the market shares...
Please don't debate this as this is just my opinion...

...Oracle  will follow Paradox fate...

@senad,
sql server is used by those who need a server.Not desktop database
Have you worked with SS2005Express or SS2008Express?  They far exceed Access as a desktop database.  (I use Access for prototyping but then convert to something else for real use.)
As for Oracle being a lousy databse . . . well, I have worked with Oracle and many others and, in the end, they all have their strengths and their weaknesses.  In some cases, you need a lot of training to get started but that usually means that you have enough training so that you don't screw things up as much.  On some  (e.g. Access), virtually anyone can start doing database work . . . and they do, with the end result that people create these god-awful "databases" that are not normalized, have weird columns as indexes, and are a pain in the a** to work with because they don't have any knowledge about what a relational database actually is.
Condemning a database engine out of hand is like saying that being or not being a vegetarian is the only way to go . . . it is a sign of a religious attitude toward or a religious bias against a database.  Beware of religious fanatics who would constrain your choices based upon their beliefs! ;-)

I have worked with 2005 and must agree with you.Only thing is that it is a server database.I am about to test the 2008 one now.Just undecided weather to use developer or the free one....On the other hand Access is contrary to what many think a very good database indeed.A surprisingly good one.It lacks many things of course but the JET engine rocks.
As for Oracle I do not think it is a lousy database in terms of the performance or the security.
What bothers me is that you really have to do some hard study before you can effectively use it.And I mean some real hard paper work.However Oracle has also some beautiful tutorials to get you going but that takes time.Also the SQL needs revisiting too.As for the database engine itself,Oracle rocks when it comes to large databases and more than 600 PC's connected.But judging from what I have seen in the SQL 2008 Microsoft has dangerously narrowed the gap.The only thing now is to see which will bite the market share more.SQL 2008 is much cheaper so that is going to be an important factor.
For the desktop database in my opinion Absolute database is the number 1 choice.Really good database.As for religious freaks,I am not that kind of a fanatic.I do test the stuff.
My biggest fault is that I expect programs to be at least 50 % intuitive to use.I hate manuals.I hate manuals for the video recorders,TV's...you name it.If I must use a manual to get something going then ...That is actually my only bias towards Oracle.Silly I know,not worthy of Oracle 'reputation' but that is how I feel.I just don't feel like learning i.e reinventing the wheel.Others can use whatever they like of course...Free choice,ha,ha...

@senad,
I have worked with 2005 and must agree with you.Only thing is that it is a server database.
Read my responses again carefully.  SQL Server 2005/2008 Express is NOT a server database.  Yes, SQL Server 2005/2008 Standard/Developer/Enterprise are server databases but not the Express editions.
The SQL Server Express editions, as you put it, rock as local databases and far exceed the power of Access.  The only things Access really has going for it, IMHO, as far as a (semi-)proffesional developer doing work are:

• It has a decent reporting capability built in;
• It has a reasonable forms development capability built in;
• It has a nice database diagramming tool.

However, SQL Server Express has:

• A nicer diagramming tool because, although it looks pretty much the same, you can create multiple diagrams, one each for sections of the database;
• Stored Procs;
• UDFs;
• Much stronger SQL capabilities;
• Standard datatypes;
• The ability to work with its Big Brother SQL Server databases for replication, etc.;
• Geographic, Date, and Time datatypes, if you go with SS2008Express.

As for needing to be intuitive rather than needing to use the manual . . . well, IMHO, the "intuitive" nature of VB and Access get more people introuble and leads too many people to think that getting something working is the same as designing and developing a good product.  I have worked with too many "self taught" "programmers" who taught themselves extremely bad habits while working in VB and with Access.  I have also seen some real garbage code and databases that were created by these people who have worked with "intuitive" products and never read manuals or learned about application and database design.
Intuitive works great for a VCR or DVD . . . but it is, IMHO, extremely questionable as a design criteria for professional development tools such as databases, programming languages, and, even, data transmission tools.  If you learn from an intuitive tool, you may think like the man who only has a hammer . . . to a man with a hammer, every problem looks like a nail whether it is or not.  Unless you go to the manuals and textbooks, you may try to design all databases the same way and an OLAP, OLTP, and an embedded database are all very different animals.

what about oracle XE? is it a server database?

@i7mad,
I don't know for sure . . . but I would think that it probably is not.  
Neither is the Teradata Demo Database. ;-)  Oh, yeah, by the way, the CD that the Teradata Demo Database comes on has the coolest query tool!  It is my Weapon of Choice for ad hoc queries because it saves teh query, the named connection, the success/failure status of the query, the number of rows, the run date and run time, the execution time, the user ID, the connection type, the DBMS time, the fetch time, AND lets you enter some notes about the query.  (All of that is saved in a nice little Access database, by the way, which means that you can link to the table(s) and do some reports and such ;-).  It also lets you search those columns (which is handy of you remember accessing a table in one version of an ad hoc query but you can't remember when that was . . . you can search for the table name and find all the queries in the database that have accessed it ;-).
That query tool lets me switch between projects in order to fight a "fire" and then I can get back to where I was before the fire alarm.

well,express editions run sql server service so in my opinion they are server databases (on PC's).On a single PC they behave like server databases.
perhaps you mean 'running on the sever itself' ? well that's  true.They are not built for server structure...