none
EF questions for professional project RRS feed

  • Question

  • Hi all,

    I'm totally new to EF and i would like to make some questions to decide if i could use EF for a certain project using (SQL Server DB).

     

    The project will use a 300+ Gygabyte DB with more than 1000 tables. The DB is mainly for local use (calculations). Most of the info will be read only with low use rate, but certain tasks with small tables need to be fast. There will be initially one server but eventually we'll use a CLUSTER.

    My uqestions are:

    I've read that ORMs are problematic with big DBs with lots of tables (in runtime and in develop time). Is there any way to use EF only for a part of the DB? (the one that i will use more intensively) and leave the rest of the DB for conventional use throught ADO.NET?

     

    In case that is not possible, is it good to use 2 DBs instead of one? One with the intensive info (used through EF) and the other with the not intensive one?

     

    Is the performance loss too big when using EF instead of pure SQL? How does the size of the DB and the number of tables affect the performance in this case?

     

    If i wanted the best performance possible, should i forget using EF in the end even if i use it in the begginning so i can develop the application faster (and then switch the routines to pure SQL progressively)?

     

    I would appreciate any information.

     

    Thanks in advance ^^

    Sunday, November 6, 2011 7:42 PM

Answers

  • Hi,

    Do you have to move those data client side to perform the calculation, and then move the results back to server side ? If you need to perform calculations but need only aggregated results client side (is this what you mean with "local use") I would go for stored procedures to avoid moving data back and forth.

    EF is not an all or nothing approach. You could have 1000 tables but use EF for 10 tables you'll need client side.

    Don't split databases based on this technical choice. Would you move a table from one database to another just because you are using now EF rather than ADO.NET to access it ? IMO keep a single database if it makes sense to put all those tables in the same db (not sure if they are all related).

    It's hard to give figures. Your best bet would be to see how well it performs under your own condition.

    I would keep EF for what needs to be exposed directly to users (also a general guideline with EF or ADO.NET is to try to filter data as early as possible i.e. avoid to show thousand of rows to a user so that he can filter them out to have something usable, have the user to define a filter before fetching rows), would use stored procedures for server side calculations on massive data producing aggregated data then made available client side (possibly with EF).

    Try perhaps to first see among those 1000 tables (?!), those :
    - that are part of the same logical domain (or are they all connected ??)
    - those that should be exposed client side (or those that can always stay server side, possibly used in server side only calculations)

    Good luck. Was just my 2 euros as this is quite difficult to answer ;-)

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Edited by Patrice ScribeMVP Sunday, November 6, 2011 10:39 PM
    • Marked as answer by Imanol Sunday, November 6, 2011 11:58 PM
    Sunday, November 6, 2011 10:37 PM

All replies

  • Hi,

    Do you have to move those data client side to perform the calculation, and then move the results back to server side ? If you need to perform calculations but need only aggregated results client side (is this what you mean with "local use") I would go for stored procedures to avoid moving data back and forth.

    EF is not an all or nothing approach. You could have 1000 tables but use EF for 10 tables you'll need client side.

    Don't split databases based on this technical choice. Would you move a table from one database to another just because you are using now EF rather than ADO.NET to access it ? IMO keep a single database if it makes sense to put all those tables in the same db (not sure if they are all related).

    It's hard to give figures. Your best bet would be to see how well it performs under your own condition.

    I would keep EF for what needs to be exposed directly to users (also a general guideline with EF or ADO.NET is to try to filter data as early as possible i.e. avoid to show thousand of rows to a user so that he can filter them out to have something usable, have the user to define a filter before fetching rows), would use stored procedures for server side calculations on massive data producing aggregated data then made available client side (possibly with EF).

    Try perhaps to first see among those 1000 tables (?!), those :
    - that are part of the same logical domain (or are they all connected ??)
    - those that should be exposed client side (or those that can always stay server side, possibly used in server side only calculations)

    Good luck. Was just my 2 euros as this is quite difficult to answer ;-)

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Edited by Patrice ScribeMVP Sunday, November 6, 2011 10:39 PM
    • Marked as answer by Imanol Sunday, November 6, 2011 11:58 PM
    Sunday, November 6, 2011 10:37 PM
  • Thanks for the response. My main question was that about EF being able to be used only for specific tables and that was answered perfectly.

     

    What i meant as local use is that there is no client calculation. All is done continuously by the server. In other words, imagine there are no clients at all.

     

    For a better description, the server continuously receives economic data, filters it, stores it, analizes it, then decides investments and executes the orders.

     

    Thank you.


    • Edited by Imanol Sunday, November 6, 2011 11:48 PM
    Sunday, November 6, 2011 11:48 PM
  • I would probably won't use EF then to favor local stored procedures (either Transact SQL or CLR). It would allow to keep all the calculation server side but would avoid to materialize the data as entities, change them and then save them again.

    IMO EF is best used to expose database as objects in *user* applications (i.e. the time used to materialize/persist entities is short enough by human standards). For massive calculations, bulk imports etc... I won't use EF to favor the overall throughtput.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Monday, November 7, 2011 10:38 AM
  • You may use EF just to import the stored procedures and map their results to "complex types". I've tweaked the .tt to better handle the procedures with OUTPUT parameters and I think I'd actually use this part of EF even if I did not plan to use any entities and used stored procedures to both query and update the data in the DB.
    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Tuesday, November 8, 2011 3:06 PM