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


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

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

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

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

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


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

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


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

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


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.


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

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


2

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


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

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.


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


1

First off, it's a bit of a rubbish framework that doesn't acknowledge multiple relationships between entity types! If you have this many foreign keys, chances are you'll have more (or fewer) in the future. The solution @Wil details will allow you achieve this without schema changes. One work-around which may fool your framework would be to define views in ...


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



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