Tag: performance

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 […]

Is this an effective and efficient SQL Query or is there a better way?

We have a passwords table which references a user table. Records never get deleted from the password table so if a user changes their password, a new entry with a more recent Created date gets inserted. The hash of the password is salted with various things, most importantly the created date of the actual record. […]

MySQL versus SQL Server Express Performance Comparison

I have a somewhat complex query with roughly 100K rows. The query runs in 13 seconds in SQL Server Express (run on my dev box) The same query with the same indexing and tables takes over 15+ minutes to run on MySQL 5.1 (run on my production box – much more powerful and tested with […]

SQL Server Multiple Joins Are Taxing The CPU

I have a stored procedure on SQL Server 2005. It is pulling from a Table function, and has two joins. When the query is run using a load test it kills the CPU 100% across all 16 cores! I have determined that removing one of the joins makes the query run fine, but both taxes […]

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