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

• cqrs examples , screencasts
• cqrs questions
• intro (also see video)