Tag Info

Hot answers tagged

14

Go through standard normalization techniques, mainly to 3rd normal form. Some ORMs can pick up on the relationships defined at the database level so you won't have to manually. I would really go the other way and ask what should I know about ORMs. Make sure you know: - How do I profile the queries? - How can I override the ORM and write my own query? - ...


13

I don't know if this design has a name, but it is: horrible to retrieve data with, horrible to modify, impossible to guarantee referential integrity. Can't think of any advantage, but if I could, I'm sure none would trump these three. Multiple many-to-many tables would be much better compared to this design, although I won't insist that would be the ...


10

It depends on what the queries are. ORMs are usually really good at CRUD, as they are usually simple. The more complex the query, the greater the chance of a bad query. You can tweak the generated queries by tweaking the LINQ statements. Sooner or later though, you'll get tired of fighting and use SQL queries or stored procedures for anything that is ...


8

Never let the ORM create or update the schema. Always use its generated SQL as a template but go through it before making the change (I have seen our ORM make redundant indexes, use bad data types...all that bad stuff) Be aware of what your ORM cannot do. Django for a while did not support group by through the ORM which was a pain (still is, legacy systems ...


5

Welcome to database hell! NoSQL is often championed as a solution to these types of applications. However, your problem here is clearly that the programmers have no idea what they are doing. Furthermore, it looks like your management is afraid of change, or unwilling to take the hard decisions which you correctly list and which are likely to fix the root ...


5

I would strongly suggest metadata in the connection in order to trace back to the application. In the connection string, there is an Application Name. There is also session data which can be used in the form of CONTEXT_INFO http://stackoverflow.com/questions/323494/sql-server-modifying-the-application-name-property-for-auditing-purposes Of course all ...


5

There are a few things I notice right away about a legacy database that differs from a database created by ORMs like Sequel and ActiveRecord for Ruby. The use of a primary key in all tables called 'id'. Yeah, that can be overridden, but id is the default. The use of plural names for the tables. The use of underscores ("snakecase") to separate the words ...


5

Step 1 - get an explain plan, in particular EXPLAIN EXTENDED will show you what SQL the query optimizer actually generated. Maybe additional indexes will help - but if you "outsource" your SQL generation then you really are at the mercy of whatever was easier for the ORM developer, not what was best for your application. Another thing you might try is ...


4

Individual queries are ok One of the biggest performance 'issues' with ORM tools (Entity Framework, Linq, LLBLGen, NHibernate, etc...) is not so much the performance of the individual queries that are executed (most are just CRUD calls which are retrieving a single record back based on a primary key). Incorrect use of Lazy Loading = Bad Performance It ...


4

Typically, when a query is "interfering" with another query it is either blocking or deadlocking. Either of these would not be visible via a default SQL Profiler trace. If you are experiencing deadlocks, you will want trace flags 1204 and 1222 turned on in SQL Server so the deadlock output gets sent to the errorlogs. You could also re-run the trace and ...


3

Foreign keys can indeed speed up queries if they are trusted. If the engine knows that every value in ParentID in the Child table refers to a valid PK in Parents then it won't even look at that table to check. However, relational constraints are a good idea in general regardless. One philosophy behind constraints in the database is to use them as a ...


3

That is how it tends to work in small companies, but that is how it always used to work for small companies: at a small scale an expert database person was often seen as a grandiose expense when all the devs can do the basics. This is not really a factor of ORMs and noSQL becoming popular recently. ORMs still have datastores behind them that someone needs ...


3

To answer your first question, NHibernate does not handle execution plans. SQL Server handles execution plans. If the dynamic SQL produced by NHibernate is parameterized, the plans will be classified as "Prepared" and will be reused assuming the parameters provided in each subsequent execution could produce the same optimized query plan. If the dynamic ...


3

ORMs aren't usually the problem themselves, it's how they're used. SQL Server generates the execution plan. NHibernate can generate a query that causes SQL to generate a bad execution plan. If you use variables in your queries, then execution plans can be reused. String concatenation usually prevents plan reuse. See this question for more info.


2

It depends ( :) ) a bit on what OR mapper you're using, so spend some time researching what db features the OR mapper in question support/don't support. E.g. Microsoft's OR mappers don't support all of SQL Server's built-in datatypes, don't support some of the newer/advanced TSQL features (recursive queries, optimizer hints etc spring to mind). In theory, ...


2

Besides the parametrization issue which has already been mentioned, all ORMs can cause really crappy plans to be generated. This is done most often when there are lots of search options causing a really large WHERE clause in the SQL Server query which is full of OR statements which the SQL Server can't do a whole lot with.


1

A Foreign Key constraint is primarily there to preserve integrity, though there are some performance benefits. The good thing about foreign key constraints is that they are always in force in the database, instead of depending on the ORM to enforce it. The software platform for your application can change, gaining or losing features, but if the database ...


1

You have supertype (content) which may be one of different suptypes (article, video,review). One approach to model such relationship in database is : CONTENT(content_id, content_type_id, PK(content_id), UNIQUE(content_type_id,content_id)); ARTICLE(content_id, content_type_id, other attributes, PK(content_type_id,content_id), ...


1

If you don't want to go for a full EAV solution, you could try something like this: product_base ------------ id product_group_id (other fields) product_option_types -------------------- id description product_options --------------- id product_id (fk to product_base.id) product_option_type_id (fk to product_option_types.id) ...


1

On PostgreSQL I could probably actually make a design like this work, guaranteeing referential integrity, read performance, and the like. This being said I see some very significant problems here that may have evaded review. I would heartily endorse the recommendation to do something, anything, else. The first is that your csv columns have variable arity, ...


1

I have faced similar situation for a procedure call with input Blob parameter. Same piece of code was working just fine on 11.2.0.2, but began to throw "java.io.IOException: I/O Exception: Connection reset by peer: socket write error" on 11.2.0.3. I have downgraded from ojdbc6.jar to ojdbc14.jar+ocrs12.jar now it is working. I don't know why but it seems ok ...


1

I'm probably off-base here with my response, because I'm not really going to address finding the offending code, other than saying... Knowing the executed SQL from the trace, leads to knowing the associated ORM entity, which leads to Find All References in Visual Studio. Hopefully the code is DRY, but that's a separate topic. I'll focus on SQL performance ...


1

Suppose you've a blog every blog have some users and posts. Every post belongs to a user. So in that case you can create a relation on user BELONGS TO posts in yii framework. relation in yii can be defined as : 'VarName'=>array('RelationType', 'ClassName', 'ForeignKey') In case of post every post belongs to a user. So relation looks like this. ...


1

Linq to SQL and Linq to Entities (Entity Framework) are some different implementations. The first one is older and more lightweight ORM then the second one. Somebody likes using the first one because of performance. Here is some links to find out differences between these technologies: Entity Framework vs LINQ to SQL LINQ to SQL vs ADO.NET Entity Framework ...


1

MariaDB 5.3 can handle such queries. The optimization is covered by the switch optimizer_switch='derived_merge=on' in the current 5.3.2 release, and this optimization will become default (no setting necessary) in the imminent 5.3.3 release More info on optimizer in 5.3


1

I know LINQ queries are composable, but have you tried playing with the order of LINQ operations to see if it could affect the query generator? This question may be better served over on Stack Overflow. var failsForAcct1001 = db.Failures.Where(x => x.AccountId == 1001).OrderBy(x => x.FailureId).Take(50);



Only top voted, non community-wiki answers of a minimum length are eligible