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

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


6

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


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


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


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

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

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

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

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


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


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



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