Hot answers tagged entity-framework
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 ...
5
Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.
I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works ...
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 ...
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
The best thing is to normalize the tables. Create a contact_type table with a record for each contact type. Then create a contact_type_xref junction table that contains the identifier from the contact table and the identifier from the contact_type table. Then load the contact types associated to each contact into the contact_type_xref, and remove the ...
4
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
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 ...
4
No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:
CREATE TABLE dbo.foo(bar INT PRIMARY KEY);
CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES ...
4
There is a lot of info missing in your question
Can a pet have multiple owners? eg we have a pet, not I have a pet
Do you expect more pet types (eg Rabbit, Bird, Fish)?
The problem with your current model (as shown) is the Pets table: it adds no value currently. Of course, your simplified design now hides information that probably makes it useful...
...
3
I had a similar problem in the past. I needed to send up to 100K numbers to SQL Server 2005 as fast as possible. After lots of benchmarks I think the fastest approach in my environment was to pack numbers in binary format. That needed less network packets and parsed very fast using very little CPU. Erland Sommarskog included the C# code I used to pack ...
3
Okay, first thing, I need to pick at this a little bit:
We went down the route of making these reads readUncommitted since the
operations on the data are absolutely not mission critical.
Using READ UNCOMMITTED/NOLOCK should only be considered when the accuracy of the results is not critical, because that's what the transaction isolation level ...
3
There are many ways to do this, but the most important thing to know is that there is a name for what you want to do: Object Role Modeling. It's a process for creating conceptual models using natural-language semantics.
This is a pretty good (if old) overview of the process.
The Wikipedia page for ORM has a pretty complete list of the available tools, many ...
2
There are big differences between the two. The datatypes are different (e.g. Oracle doesn't have the ENGINE part, int(5) is invalid for Oracle, LONGTEXT is invalid for Oracle and so on) and part of the syntax as well. Oracle does not have an "autoincrement" column. It has a different concept using sequences.
If you want to find all the differences you will ...
2
20k records is not that many. Are you SELECTing other data from other tables as part of your mailout which is slowing things down? Are you keeping your transaction open longer than you need?
Otherwise, can you use one of the opimistic concurrency isolation levels: Snapshot or Read Committed Snapshot? They should allow you to read without blocking.
1
If you have access to a db version control system (ala Red Gate), you can have it create a deployment script fairly easily.
If not, just create a file and script any ddl changes you make and store them in a file, then when you are ready (and have backed up prod) you can run the script against prod to make your changes.
Be aware that certain changes can ...
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
This appears to be a fairly standard multi-site case. Model the data as if there is only one location. This will get the basic model down correctly. Then add a location column to the tables where location is important. Options you may want to use are:
Simple foreign key. (Transactions for incoming/outgoing stock.)
Multi occurrence foreign key. ...
1
I ran 2 tests for a comma-delimited list as well as a table variable type. I took the first answer above and thought that table variable types were ok and have of course read by now that they are not valid for 2005. However, I think it can still be valuable information for this post. I took the original post requirements literally and assumed updating ...
1
You should go for proper indexing of records as in SQL server 20000
records is not much more ,you can easily pick in a single select
statement. If possible in single statement then why should go for
multiple.
So better solution is that if records are not retrieved fast then
create the indexes as per the need to optimize your select statement.
...
1
I have a person table.
Fine (just changed the name of the PK column):
person
------
person_id PK
firstname
lastname
email
telephone
...
...
and a user table as a child. They must be 1:1 relationship, because an user cannot be repeated twice.
If the relationship is 1:1 (assuming that person is the supertpe and user is the subtype, you can ...
1
Yes. Linq-To-Sql and EF submit dynamic (Ad-Hoc) queries to the database. You can prove this by running a profiler trace and debugging the app.
The good news is that you'll always get the most up-to-date query plan, based on current statistics. The bad news is that the plan will have to be compiled on each execution. This is rarely good, and only when the ...
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
