Integer and Boolean Fields in SQL Server 2008

CORUS · 2014-08-06 16:47:19

Hi,

I'm testing Mormot with SQL Server 2008 and when I define TSQLRecord with an Integer or Boolean fields those are translated to SQL Server 2008 as bigint. Sample:

type
  TPerson = class(TSQLRecord)
  private
    fName: RawUTF8;
    fAge: Integer;
    fHasFacebook: Boolean;
  published
    property Name: RawUTF8 index 80 read fName write fName;
    property Age: Integer read fAge write fAge;
    property HasFacebook: Boolean read fHasFacebook write fHasFacebook;
  end;

Are translated to SQL Server as:

CREATE TABLE [dbo].[Person](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](80) NULL,
    [Age] [bigint] NULL,
    [HasFacebook] [bigint] NULL,
PRIMARY KEY CLUSTERED 
...

To save space in the Database, it is possible to configure Mormot to change this behavior?
For example I would like that a Delphi Boolean field becomes a bit field in SQL Server
or and Delphi Integer field becomes also a Integer (4 bytes) in SQL Server?

Regards,

Josep

ab · 2014-08-06 20:16:52

Are you sure it will effectively save space in the DB?
We are in the 21th century.
Stop thinking like in the 20th century: http://dba.stackexchange.com/a/4969
When I see how MS SQL uses its disk space (in comparison to SQLite3 for instance, or our in-memory database engine), I doubt you would see any difference.
And in a 64 bit server, an integer, a boolean or a bigint value will always use a 64 bit register at processing level (i.e. at CPU level).

In all cases, this is by design, for efficiency and simplicity of the DB / ORM layer.

What you can do is create the table by hand, with all its fields, before launching the mORMot engine on the DB.
Then the ORM won't re-create the table at startup.
Then it should work as expected, and use as little space as you expect.
Numerical fields should stay numerical, and text fields should still be text field.

ab · 2014-08-07 19:03:50

If you take a look at http://www.sqlservercentral.com/article … ning/2753/ (the link at SO).

If you use smallint, int or bigint with MS SQL Server:
- disk space use is almost the same;
- performance is the same.

About disk spaces, here are some numbers:

Name	    Rows	 Reserved	Data	  Index size	Unused
Byte32	1,000,000	13,000KB	12,880KB	56KB	64KB
Byte64	1,000,000	16,904KB	15,808KB	88KB	8KB

So I would not fear using bigint instead of int when mORmot creates tables.

CORUS · 2014-08-08 09:00:49

Hi Arnaud, First of all thank you very much for all the work you've done with mORMot and the great interest that you take to answer our questions.

I began in computing a lot of years ago, when we stored files in diskettes, and so the space was very important.

As you say we are in the 21st century and we have to start thinking differently.
So I must change my mind, because I want adopt a "mORMot"

Regards,

Josep

ab · 2014-08-08 10:06:10

I began with a ZX81, and 580 bytes of free RAM for my programs...
So I know exactly what you have in mind!

By itself, our mORMot units try to always use as little RAM and CPU as possible.
This is one of the keys for scaling, and highest business ROI.
But for the DB back-end, this is another story, since all those modern beasts tend to consume a lot of resources... much more than our little rodent...

houdw2006 · 2015-07-01 11:31:22

Hi, ab,

I read all the messages from http://dba.stackexchange.com/a/4969. It seems to me that most people agree that to keep the row size as small as posible is really matter (table size , index page size, database size, time taken for backup, log and etc. are all connected with the row size).

The mORMot is a very goog framework, and the porfamance is really impressive. I think most of us select mORMot as the software foundation, not just for toy programs, we want to make real application. For a real working table, suppose every row takes 40 more bytes, one 100 million rows would take more 3800MB of disk space, and will cosume more memory for caching as well. So I have a sujestion here that can the mORMot provide us a data type mapping list for table creation? So we can adust the delphi data type maping to which DB datatype according to one's expectation (perhaps a list for the cardinal data types is enough).

I really like the feathure of AddMissingTables, and would like not to miss it. We want the Fish and bear's paw all at once.

Dewen

ab · 2015-07-01 15:52:53

The row size could be set using the "index ###" attribute at published property definition, so you can already set it.

IMHO this "row size" is a bad design choice for today's hardware.
PostgreSQL does not expect any "size" for string columns, just use TEXT. And storage is optimized. The same with SQlite3, and NoSQL databases (especially in MongoDB + WiredTiger engine).

If you want more tuning, you could just run your own optimized CREATE TABLE, then CreateMissingTables would use the existing optimized configuration.
If the column type affinity matches the values (e.g. TEXT, INTEGER, FLOAT), it will work with no issue.

mORMot Open Source

#1 2014-08-06 16:47:19

Integer and Boolean Fields in SQL Server 2008

#2 2014-08-06 20:16:52

Re: Integer and Boolean Fields in SQL Server 2008

#3 2014-08-07 19:03:50

Re: Integer and Boolean Fields in SQL Server 2008

#4 2014-08-08 09:00:49

Re: Integer and Boolean Fields in SQL Server 2008

#5 2014-08-08 10:06:10

Re: Integer and Boolean Fields in SQL Server 2008

#6 2015-07-01 11:31:22

Re: Integer and Boolean Fields in SQL Server 2008

#7 2015-07-01 15:52:53

Re: Integer and Boolean Fields in SQL Server 2008

Board footer