Tag: performance

Table Relationships – Access Front End with SQL Server Backend

When our IT department converts Access databases to SQL Server the relationships do not transfer over. In the past, I have provided ERDs that they can use to build the relationships. In this case, I didn’t. What are the possible consequences of defining the table relationships in the MS Access Front End versus on the […]

Linq to entites (EF6) return latest records of each group using Row number in T-SQL server

I’m using Linq to entities to get most recent updated record of each group. But actually when I checked in sql profiler my Ling query generated many sub-query so that It really take too much time to complete. To solve this performance problem, I already wrote native T-Sql mentioned below so that I’m looking for […]

SQL Calculating Aggregated Totals-To-Date

Suppose I have a table, my_table, with 2 columns: date, value. And I want to calculate a 3rd column: aggregated_value_year_to_date. Currently I’m doing this: SELECT date, value, (SELECT SUM(value) FROM my_table AS Match WHERE Match.Year = Base.Year AND Match.date < Base.date) AS aggregated_value_year_to_date FROM table AS Base Are there any better options? Particularly is there […]

Recommended placement of tempdb and log for SQL Server OLTP database(s)

Suppose the following configuration: Drive D … Data, Drive E …. TempDB, Drive F … Log. and suppose all drives are on separate spindles with respective drive controllers. Concerning performance; is the above configuration optimal, decent, or not advisable? With budgetary constraints in mind, can any of these DB’s share the save drive without significant […]

Coalesce vs Sargability

The company that I work for uses a third party software, and we don’t have any exposure to the application code, so I cannot change much the DB objects. I’m trying to do some work around in a view that is not Sarg, there is a coalesce to replace nulls to 0 within the view, […]

Lambda query translate .count as select rows (EF6 code first)

I have 2 simple tables. Post is the master and has n votes. I want to return posts with their vote count. This is my code: db.posts.Select(p=> new PostlDto{Title= p.Title, VoteCount= p.Votes.Count}) It is ok that I have 1 call to get posts and several calls to get counts. but the problem is that each […]

Fastest way of performing Bulk Update in C# / .NET

I’m trying to figure out whats the best possible way to perform a bulk update via my mini console application in SQL server. I have written my own way of bulk update like following: SqlCommand command = new SqlCommand(); command.Connection = new SqlConnection(“Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI”); command.Connection.Open(); for (int i = 0; i < items.Count; […]

How to speed up this TSQL query?

I have a TSQL select query that is running “slow” SELECT CustomerKey ,ProductKey ,RepresentativeKey ,ReportingDateKey ,SUM(i.InvoiceQuantity) AS InvoiceQuantity ,SUM(i.InvoiceQuantityKg) AS InvoiceQuantityKg ,SUM(i.BrutoInvoiceLineAmount) AS BrutoInvoiceLineAmount ,SUM(i.EndOfYearDiscount) AS EndOfYearDiscount ,SUM(i.NettoInvoiceLineAmount) AS NettoInvoiceLineAmount ,SUM(i.TotalLineCostPrice) AS CostPrice ,SUM(i.MarginAmount) AS MarginAmount FROM FactInvoices i WHERE i.DossierKey =2 AND i.ReportingDate BETWEEN ‘2016-01-01’ AND ‘2017-12-31′ GROUP BY CustomerKey ,ProductKey ,RepresentativeKey ,ReportingDateKey I’m […]

What factors that degrade the performance of a SQL Server 2000 Job?

We are currently running a SQL Job that archives data daily at every 10PM. However, the end users complains that from 10PM to 12, the page shows a time out error. Here’s the pseudocode of the job while @jobArchive = 1 and @countProcecessedItem < @maxItem exec ArchiveItems @countProcecessedItem out if error occured set @jobArchive = […]

My subquery is slowing down the process. Any optimization method?

I have written codes using subquery but it is too slow. I am looking for a solution that can optimize the process of this query. It is intended to return 6 columns – PO Number, PO Line Number, Amount for each PO Line, Count of PO Lines per each PO Number, Sum of Line Amounts […]

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