Tag: performance

SQL Inline or Scalar Function?

So I need an SQL function that will concatenate a bunch of row values into one varchar. I have the functions written but right now I’m focused on what is the better choice for performance. The Scalar Function is CREATE FUNCTION fn_GetPatients_ByRecipient (@recipient int) RETURNS varchar(max) AS BEGIN DECLARE @patients varchar(max) SET @patients = ” […]

How much database performance overhead when using LINQ?

How much database performance overhead is involved with using C# and LINQ compared to custom optimized queries loaded with mostly low-level C, both with a SQL Server 2008 backend? I’m specifically thinking here of a case where you have a fairly data-intensive program and will be doing a data refresh or update at least once […]

How can I avoid to get data again in the case 'select then update'

Currently, my table (table A) has around 10.000.000 records. Every day, there are 100 records come. They are new and not been processed. So, Process column = 0. I’m using SQL Server 2008. In my business, I need to do 2 steps: Getting data are new (Process = 0), do something, and insert to table […]

Too slow open connection to SQL Server on the first application startup

In my WPF4 desktop application I’m using connection to SQL Server 2008 R2 via ADO.NET Entity Data Model (edmx) and I payed attention, that when I start application for the first time (cold start) it takes too long time (about 10 sec) until application establish (open) connection to DB and could perform basic LINQ-query (no […]

What has better performance on SQL Server

I want to compare a number of values (up to ten) with a function that will return the smallest value of them. My colleague wrote the function like: set @smallest = null if @smallest is null or @date0 < @smallest begin set @smallest = @date0 end if @smallest is null or @date1 < @smallest begin […]

how can I simulate network latency on my developer machine?

I am upsizing an MS Access 2003 app to a SQL Server backend. On my dev machine, SQL Server is local, so the performance is quite good. I want to test the performance with a remote SQL Server so I can account for the effects of network latency when I am redesigning the app. I […]

SQL Server and performance for dynamic searches

I was wondering what were the best practices for making a query in sql with a dynamic value, lets say i have a Value(nvarchar(max)) value: “912345678” select * from AllData where Number like ‘%912345678%’ value: “Michael” select * from AllData where Name like ‘%Michael%’ value: “Street number 10” select * from AllData where Address like […]

Modify SQL trigger to work when inserted table contains more than one row

I’ve got a SQL trigger written for a table in SQL Server 2008. It works well when there is only one row in the ‘inserted’ table. How can I modify this trigger to work correctly when there are multiple rows? Performance is key here, so I’d like to stay away from cursors, temp tables, etc. […]

SQL Server Index performance for joins

I have 2 tables, Items and ItemDetails. Generally any query on this data is performed through the Items_join_ItemDetails view, which joins the two tables on the common ItemID field. Currently ItemDetails has a clustered index on (Date, ItemID) and a non-clustered index on just ItemID. Items has a clustered index on ItemID. When the view […]

Hibernate SQL In clause making CPU usage to 100%

In my java application I am using SQL server and Hibernate3 with EJB. When I tried to execute a select query with In clause, the DB server CPU usage reaches to 100%. But when I tried to run the same query in SQL management studio, the query is running without any CPU spikes. Application server […]

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