Tag: performance

update sql server rows, while reading the same table

I have a database in SQL Server 2012 and want to update a table in it. My table has three columns, the first column is of type nchar(24). It is filled with billion of rows. The other two columns are from the same type, but they are null (empty) at this moment. I need to […]

Is it a good strategy to add a lot of filtered indexes?

I have the following situation: There is a service that any given time can call the same SP in parallel 1 up to 40 – 50 times in the same time. When called ones the SP performs fine (800ms to 1s). As the number of calls grow the times grow. The stored procedure runs on […]

Performance of Delete then Insert vs Merge in SQL Server

I have a SQL Server 2008 query which looks like this Delete from Table1 INSERT INTO TABLE1 SELECT * FROM Table2 And I would like to improve the performance of this query by some means. So, would be a nice approach to replace the Delete and Insert query with Merge statement which handles Delete, Update […]

SQL Server – Query Taking Long Time with Hard Coded Dates

To preface, this query is using db_links into an oracle environment and is currently being executed in TOAD against the SQL Server database. I initially had some date variables that the user can enter the date range, and when using these variables, the query executes in 15 seconds. When I hard code the same dates […]

Performance with datetime search on many records

I have an application that is very depended on date. It stores thousands of records per day and almost every query needs to check the date field to make sure it matches the search date. But as the application grows, the performance is dropping. Changing the application to enhance performance is required, but will take […]

How to Improve “having sum” query

I have this query: Select DescFornecedor, DescUsuario, Classificacao, Sum(Tempo) as Tempo, Case Classificacao When ‘Agendamento’ Then 2 When ‘Aguardando cadastro’ Then 3 When ‘Descarte’ Then 8 When ‘DesistĂȘncia’ Then 7 When ‘Em Pausa’ Then 4 When ‘Em Volta’ Then 10 When ‘Entrevista’ Then 1 When ‘Filtro’ Then 5 When ‘Outros’ Then 9 When ‘Recusa’ Then […]

What is better way to update SQL Server database using Linq?

I’m using SQL Server 2012 and Linq-to-SQL. I have this method to update one row in the database after checking some conditions. I wrote it in two ways. Which one is faster and less resource intensive? (SQL query, CPU count…) Query #1: In this one I used IEnumerable internal static bool CheckSecretCodeLoginkError(String License, String SecretCode, […]

Most efficient way to retrieve master/detail (nested) data

My question is somewhat of a follow-up on this one: Efficient Way To Query Nested Data. However, I’d like to generalize it a bit, and potentially update it as it’s been 6 years and there may be new and better alternatives (?). I have a typical master/detail relationship in a MS SQL Server database (although […]

SQL spatial STIntersection spatial Index

Im trying to split polylines by points which have a small buffer around them. Currently i have over 370,000 lines and 320,000 nodes and the query is running really slowly (ive left it for 3 days and it still hasnt completed). I have tried forcing a spatial index using with (Index(SI_tempPD)) but i get the […]

Dynamic SQL Optimal Paging

For several years, I’ve been creating my dynamic sql paging by wrapping it into a CTE with ROW_NUMBER(). This method has been mentionned sevral times in stackoverflow and it offer good performance. However, since sql server 2012 has introduced offset and fetch with even better performance, I decided to see if I could optimize my […]

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