Tag: performance

query execution performance issues with a NOT IN in where clause

I have a query such SELECT SOME_FILEDS FROM MY_VIEW MY_VIEW is a view retrieving data from other views, it takes 45 minutes to execute a query from MY_VIEW on a production server. By trying to invesetigate the problem i realized that one sub-view is slow. I know that creating many views is not the best […]

How can I fill a DataTable using a SqlDataReader as fast as when using SqlDataAdapter?

I have wrote a method which return a DataRow as soon as it has been fetched from a IDataReader source (using yield keyword). It works, but it is really slower than filling the DataTable using a SqlDataAdapter. public static IEnumerable<DataRow> AsEnumerable(this IDataReader source) { //create columns DataTable dt = new DataTable(); for (int i = […]

Is there any potential performance problems if I update by deleting and inserting

I’m using SQL Server and I have a table related N-1 to another table and I have a process that updates the information of the N table by removing all the related rows and inserting them again. I could do a search in the table and check if the row I’m going to insert already […]

Strange performance of SELECT COUNT(1)

I have a select query with some complex joins and where conditions and it takes ~9seconds to execute. Now, the strange thing is if I wrap the query with select count(1) the execution time will increase dramatically. SELECT COUNT(1) FROM ( SELECT …. — initial query, executes ~9s ) — executes 1min That’s very strange […]

Is there a Performance Impact for Accessing SQL CLR across DB on the Same Server?

I have a SQL CLR function MySchema.MyClrFunction in MyDataBase. I can access it from MyOtherDataBase on the same server via MyDataBase.MySchema.MyClrFunction and it works fine. Would I expect any performance gain by registering the assembly and creating the function again in MyDataBase so I can make an intradatabase call? Yes, I could test it myself […]

Can “NIC teaming” decrease contention, for multiple sites with separate application pools? (connecting server w/ IIS, and server w/ SQL Server)

SUMMARY: if sites have separate application pools, can their traffic avoid contention through “NIC teaming”? ((Let me know if this is better posted on http://networkengineering.stackexchange.com)) DETAILS: Our hosting provider has priced a scenario where NIC teaming could be done, between the server hosting our websites, and the server hosting our databases. Tech details (in case […]

SQL Server : Query performance analysis – Any Optimization ideas?

I have read multiple articles that recommends using SQL Server Profiler, so did I. I have a quite important query which makes the application freeze from time to time. The query includes multiple LEFT JOIN … ON … AND (… AND …) OR (… AND …). I have no authority on the relational model that […]

How to speed up this group by with CSV column?

I have a simple table: create table breaktypes (parent_id int, child_id int). The data is basically this: parent_id child_id 125 1 125 3 125 19 125 3722 126 32 126 44 126 13 126 14 127 266 Basically a list of parent_id values with child_id values that belong to it. I would like the data […]

NHibernate and SQL query performance issue: stored procedure vs native SQL

I have a performance problem with the execution of a SQL query on NHibernate. I tried to import the SQL code of a stored procedure in a SQL query, finding a large performance deterioration. Here’s an example of testing… In my mapping file I have these two definitions: <sql-query name=”sp_Test1″> exec MyStoredProcedure :Param1, :Param2, :Param3, […]

Having non clustered index on my column used for joins.Is it good or bad?

I have a question that suppose there is a column that I used to put joins with other tables, should I have a non-clustered index on that column to improve performance? The column is a nvarchar column which is not unique. Thanks,

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.