Tag Info

Hot answers tagged

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

Larger nvarchar (max) data items (over 8000 bytes or so) will spill over into text storage and require additional I/O. Smaller items will be stored in-row. There are options that control this behaviour - see this MSDN article for more details. If stored in-row there is no significant I/O performance overhead; there may be additional CPU overhead on ...


8

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases. (Usually, the symptom of this is that ...


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

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

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... ...


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

First and foremost do not use the IMAGE data type, is an old deprecated type: text , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. You ...


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 are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE: Connect # 739013 : Failover or Restart Results in Reseed of Identity So I bet that if you look in SQL Server's error logs, the rows ...


3

Try a filtered unique index, not a trigger. CREATE UNIQUE INDEX ixOnePrimaryEach ON dbo.Document(PersonID) WHERE IsPrimary = 1;


3

The only way to do this would be to put a SQLCLR function within the database which would then resize it. This wouldn't be recommended as this would be very expensive as any rows returned by the query would need to have their image resized over and over, even if the row wasn't used by the client application. A better idea would be to store a separate ...


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

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

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 ...


3

Indexing the biggest concern. From BOL: Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. If you can't index properly, you are going to have slow queries. ANd from a data integrity s=perspective, having nvarchar(max) will ...


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

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 ...


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.


2

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 ...


2

In addition to what Aaron Bertrand has said, you can rename your existing keys to something reasonable with sp_rename. Like so: exec sp_rename 'some random foreign key name', 'new better foreign key name', 'OBJECT'


2

It might help if you also had some table descriptions in your question, but here goes... A network has many blocks. You could do this very simply with a network_id in each block. That way there is no restrictions on how many blocks can reference a network, but you ensure that each block is only in one network. Each block belongs to a single client. ...


2

When inserts are rolled back, the identity values are NOT reset. This is one explanation of the so called "skipped" values.


2

This looks like the party relationship model (see http://stackoverflow.com/questions/4969133/database-design-articles-blog-posts-photos-stories). I recently went through something similar, combining different content types under a single table with subtables for each unique content type. I'm not sure about the entity framework, but in the new model you ...


2

I recommend not using Entity Framework with anything else other than SQLSERVER. And then LINQ to SQL may be better. lightspeed was recommended to me as a better product. I personally have not used it.


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 assume you are trying to push your changes back to the database from your entity model? If so you can do that from studio. (Right click in the entity modeler, save changes to the database.) If you have to recreate it - you can also just change where the EF is pointing in your configuration settings and push the model out to the database if you have create ...



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