Tag: performance

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

SQL Azure – Intermittent Long Running Query

I have a query that is intermittently running very slowly in SQL Azure Runs in < 1 second in SSMS Is submitted through Entity Framework through the application Is NOT calling a stored procedure Is passed parameters Using DMVs determined that it runs and then enters a “Suspended” state The wait type is indicated as […]

tsql analytic query

I have fact and date dimension tables. I need to write a query that return for every date member its aggregation function (sum(amount)). But this aggregation function needs to be filtered based on the date. FACT table: InvFactMain Dim table: Dates Currently I have this query : SELECT D.DATE ,( SELECT sum(inline.InventoryRetailAmount) FROM InvFactMain inline […]

Query optimization in EntityFramework 6

While profiling my application (and DB), I found a huge query (of about 200 lines) in the profiler that looks like: exec sp_executesql N’SELECT [Project2].[UserID] AS [UserID], — FROM ( SELECT [Limit1].[UserID] AS [UserID], — CASE WHEN ([Extent4].[MemberID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM (SELECT TOP (1) [Extent1].[UserID] AS […]

EF generated query performance tuning required. Or a database issue?

I have a search query generated by EF 6 which sometimes causing performance issue for the search criteria which yields large results. The query performance is unpredictable, sometimes it performs good and sometimes its not.The following is the query caught in sql profiler, which queries a view exec sp_executesql N’SELECT [Extent1].[LastName] AS [LastName], [Extent1].[FirstName] AS […]

SQL query is running 10 times faster on local desktop SQL Server Express than on SQL Server in Azure

We are currently running two instances of SQL Server. For development purposes, we run a local DB on a desktop PC in our office. The PC has following stats: 8 GB Ram AMD Athlon 5350 APU with Radeon(tm) R3 2.05 GZ 64 Bit Windows 8.1 Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Express Edition (64-bit) […]

SQL server slower on faster machine

We have two servers with the same operating system (Windows Server 2012 Standard, Microsoft Windows NT 6.2 (9200)) and same SQL server (12.0.2000.8), and the database is restored from first server on second. First machine: Inter(R) Xeon(R) CPU E5420 @ 2.5GHz 2.49 GHz Ram: 8.00 GB Second machine: Inter(R) Xeon(TM) CPU 3GHz 2.99 GHz(2 processors) […]

SQL Server 2008 R2 improving query performance

I have a query: SELECT 2015, d.TransactionQuater, atc1bf.atc1_id, atc2bf.atc2_id, atc3bf.atc3_id, atc4bf.atc4_id, bf.BU_id, mbf.Manufacturer_id, pbf.Product_id, SUM(d.Units) AS Units, SUM(d.ExMnf_LC*Units) AS ExMnf_LC FROM dbo.FF_Data_2013_short AS d INNER JOIN ATC1ByFCC atc1bf ON d.FCC = atc1bf.FCC INNER JOIN ATC2ByFCC atc2bf ON d.FCC = atc2bf.FCC INNER JOIN ATC3ByFCC atc3bf ON d.FCC = atc3bf.FCC INNER JOIN ATC4ByFCC atc4bf ON d.FCC = […]

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