performance - Most efficient method for persisting complex types with variable schemas in SQL -

-

what i'm doing

i creating sql table provide back-end storage mechanism complex-typed objects. trying determine how accomplish best performance. need able query on each individual simple type value of complex type (e.g. string value of city in address complex type).

i thinking store complex type values in 1 record xml, concerned search performance of design. i need able create variable schemas on fly without changing database access layer.

where i'm @ now

right thinking create following tables.

 table:  schemas    column name       data type    schemaid          uniqueidentifier    xsd               xml                 //contains schema document of given complex type    deserializetype   varchar(200)        //the full type name of c# class document deserializes.   table:  documents    column name       data type          documentid        uniqueidentifier    schemaid          uniqueidentifier   table:  values                            //the documentid+valuexpath function pk    column name       data type          documentid        uniqueidentifier    valuexpath        varchar(250)    value             text

from these tables, when performing queries series of self-joins on value table. when want entire object documentid, have generic script creating view mimics denormalized datatable of complex-type.

what want know

i believe there better ways accomplish trying to, little ignorant relative performance benefits of different sql techniques. don't know performance cost of:

1 - comparing value of text field versus of varchar field. 2 - different kind of joins versus nested queries 3 - getting view versus xml document sql db 4 - doing other things don't know don't know affecting query but, experienced enough know exist

i appreciate information or resources these performance issues in sql recommendation how approach general issue in more efficient way.

for example,

here's example of planning on doing.

i have c# class address looks like

public class address{      string line1 {get;set;}      string line2 {get;set;}      string city {get;set;}      string state {get;set;}      string zip {get;set; }

an instance constructed new address{line1="17 mulberry street", line2="apt c", city="new york", state="ny", zip="10001"}

its xml value like.

<address>    <line1>17 mulberry street</line1>    <line2>apt c</line2>    <city>new york</city>    <state>ny</state>    <zip>10001</zip> </address>

using db-schema above have single record in schemas table xsd definition of address xml schema. instance have uniqueidentifier (pk of documents table) assigned schemaid of address record in schemas table. there 5 records in values table represent address.

they like:

documentid                              valuexpath        value 82415e8a-8d95-4bb3-9e5c-aa4365850c70    /address/line1    17 mulberry street 82415e8a-8d95-4bb3-9e5c-aa4365850c70    /address/line2    apt c 82415e8a-8d95-4bb3-9e5c-aa4365850c70    /address/city     new york 82415e8a-8d95-4bb3-9e5c-aa4365850c70    /address/state    ny 82415e8a-8d95-4bb3-9e5c-aa4365850c70    /address/zip      10001

just added bounty...

my objective obtain resources need in order give application data access layer searchable , has data-schema generated application layer not require direct database configuration (i.e. creating new sql table) in order add new aggregate root domain model.

i open possibility of using .net compatible technologies other sql, require such suggestions adequately substantiated in order considered.

how looking solution @ architectural level? breaking head on complex graphs , performance until discovered cqrs.

[start evangelist mode]

  • you can go document-based or relational storage. both! (event sourcing)
  • nice separation of concerns: read model vs write model
  • have cake , eat too!

ok, there initial learning / technical curve on ;)

[end evangelist mode]

as stated: "i need able create variable schemas on fly without changing database access layer." key benefit read model can fast since it's made reading. if add event sourcing mix, can drop , rebuild read model whatever schema want... "online".

there nice opensource frameworks out there nservicebus saves lots of time , technical challenges. depends on how far want take these concepts you're willing/can spend time on. can start basics if follow greg young's approach. see info in links below.

see


Comments

Post a Comment

Popular posts from this blog

Javascript line number mapping -

-
magically formatted comments change reported line number of javascript errors in browsers; this: //@line n "f" n line number , f file name. unfortunately, //@line appears ungoogleable. know there documentation on feature, , browsers support it? (i found references here , here .) from can find available in mozilla based browsers. it using js_setoptions command can turn these options on or off depending on values passed. https://developer.mozilla.org/en/js_setoptions
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ...
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more