first create model on application, entity framework generates sql creating table. first generates column type varchar(20), second generates longtext.
example
[stringlength(20)] public string code { get; set; } public string codetwo { get; set; } questions
there's difference between these 2 declarations(space allocation)? (even if store same value "test" has 5 characters.)
if know field has variance of it's length between let's 10-15 characters, best approach limiting max length or let "unlimited"(space allocation) ?
thanks in advance. sorry poor english.
translated answer of user @marconcílio souza , on same question asked in language.
when entity framework generates tables in database check types of each field, in case of type string when specify size same specification bank corresponding type.
in case of its
[stringlength (20)] public string code {get; set; } the corresponding mysql varchar (20), when same string type , declared without fixed size entity framework allocate as possible type in database in case of mysql , longtext.
the columns of type blob longtext inherently variable length , take no storage when not used. space required them not affected if null value in case of use such 'text' test 'set' allocation , size of passed string.
* advantages / disadvantages of blobs vs. varchars *
all comments in paragraph referring varchar type valid char type too. each comment ends blob + or varchar + mark indicate type of data better.
- know maximum length of data?
with varchars need declare maximum length of chain. blobs not have worry it. blob +
- you need store long strings?
a single varchar limited 32k bytes (i.e., 10 thousand unicode characters). maximum size blob (according service guide);
- page size 1kb => 64 mb - page size 2kb => 512 mb - page size of 4 kb => 4gb - page size of 8kb => 32gb
blob +
- you need store many long text columns in single table?
the total line length (uncompressed) restricted 64k. varchars stored online directly, can not store many long strings in row. blobs represented blob-id, , uses 8 bytes 64k maximum. blob +
- you want minimize call between client , server?
varchar data fetched along other line data in search operation , several rows sent on network @ same time. every single blob needs search operation open / fetch. varchar +
- you want minimize amount of data transferred between client , server?
the advantage of blobs line blob-id, can decide whether or not seek blob data. in older versions of interbase there problem varchars sent on network in declared full length. problem has been fixed in firebird 1.5 , interbase 6.5. draw (blob + older versions of server)
- you want minimize space used?
varchars compressed rle (indeed entire line compressed except blobs). maximum of 128 bytes can compressed 2 bytes. means empty varchar (32000) occupy 500 + 2 bytes.
blobs not compressed, empty (ie null) blob occupy 8 bytes of blob-id (and later rle compressed). non-empty blob may stored on same page other data line (if appropriate) or in separate page. small blob fits data page has overhead of 40 bytes (or little more). big blob has same 40-byte overhead in data page, plus 28 bytes overhead on each blob page (30 bytes in first). blob page can not contain more 1 blob (ie blob pages not shared data pages). example. 4k page size, if store 5k blob, 2 pages of blob type allocated, means lose 3k of space! in other words - larger page size, higher probability small blobs fit on data page, more wasted space if separate blob pages needed large blobs. varchar + (except varchars extremely large declared length, or tables lots of null blobs)
- you need table extremely large number of rows?
each line identified db_key, 64-bit value, 32 bits, 32 bits , balanced id used locate line. maximum number of theoretical way of rows in table 2 ^ 32 (but various reasons maximum true lower). blob -ids allocated same address space db_keys, means more blobs in table, less db_keys remain face queues. on other hand, when stored lines wide (e.g. contain long varchars), fewer lines fit data page , many db_key values remain unasigned anyway. varchar +?
- you want performance?
because large blobs stored outside data pages, increase "density" of lines of data pages efficiency , cache (reduce number of / o operations during search). blob +
- you need perform search on contents of text columns?
in varchar can use operators such '=', '>', among them, of (), case sensitive , departure case insensitive containing. in cases index can used speed search. blobs can not indexed, , restricted taste, starting , containing operators. can not directly compare blobs operators '=', '>' etc. (unless use udf), can not, example, join tables in blob fields. varchar +
- you want search content of these texts containing?
containig can used perform case-insensitive search content varchar field. (no index use) because can not set collation order blob columns, can not use insensitive search case national characters in blob columns (only lower half of character set case insensitive). (alternatively, can use udf). firebird 2 allows set text wrapping (and binary) columns. varchar +
- you need capital contents of text column?
you can use built-in upper () function on varchar, not blob. (also cast, min, max can not used blobs) varchar +
you can not sort blob column. (e group by, distinct, union, join on) unable concatenate blob columns. varchar +
there no built-in conversion function (cast) converting blob varchar or varchar blob. (but can write udf purpose.) since firebird 1.5 can use builtin substring function convert blob varchar (but clauses , can not exceed 32k). draw
you can not assign value blob directly in sql command, example. enter values guide (myblob) ( 'abc'); (but can use udf converting string blob). varchar +
firebird - 0.9.4 has functionality draw
- you need security on these text columns?
to recover table data, must granted select privilege. retrieve blob, need know blob -id (stored in table), firebird / interbase not check if have blob table rights belongs. means know or guess right blob -id can read blob without rights table. (you can try isql , blobdump command.) varchar +
more details
Comments
Post a Comment