Tag: performance

Need to tune the select query without adding index

I have a query related to Indexes and Execution plan. Below are few tables that I am using Table 1 : TestReviewResult Column_name |Type |Length TestReviewResultId |int |4 TestNumber |int |4 ReviewerUserId |int |4 Current Index index_name index_description index_keys TestReviewResult_PK clustered, unique, primary key located on PRIMARY TestReviewResultId Table 2: TestReviewFinding Column_name Type Length TestReviewFindingId […]

MS SQL Query changed plan and became incredibly slow

We have a query that has been running for over 3 year without any problems. The query is a standard one from our ERP system (Microsoft Dynamics AX) and it goes like this: SELECT SUM(B.COSTAMOUNTPOSTED),SUM(B.QTY) FROM INVENTTRANSPOSTING A,INVENTTRANS B WHERE ((A.DATAAREAID=N’01’) AND (((A.ITEMID=@P1) AND (A.INVENTTRANSPOSTINGTYPE=@P2)) AND (A.TRANSDATE>@P3))) AND ((B.DATAAREAID=N’01’) AND ((((B.VOUCHER=A.VOUCHER) AND (B.DATEFINANCIAL=A.TRANSDATE)) AND (B.INVENTTRANSID=A.INVENTTRANSID)) […]

WCF Service Query time vs Direct Query to DB 6x slower

First question here 🙂 I am working with a WCF service that gets queries from a WinForms application, converts it to XML, sends it to a SQL Server DB, returns the query data as XML, converts it to a DataTable and sends it back to the WinForms application. I tested the speed difference between this […]

Query really slow when changing join field

I’m using SQL Server 2008 and I noticed an enormous difference in performance when running these two almost identical queries. Fast query (takes less than a second): SELECT Season.Description, sum( Sales.Value ) AS Value FROM Seasons, Sales WHERE Sales.Property05=Seasons.Season GROUP BY Seasons.Description Slow query (takes around 5 minutes): SELECT Season.Description, sum( Sales.Value ) AS Value […]

SQL Server In Memory Tables performance for autocomplete search performance

I am using SQL Server and sometimes the performance of it is not enough. I just want to know if MS SQL Server could handle searches on tables with a couple millions of rows with a good performance (below 1 sec). For example, I need to do an autocomplete search that should work fluently and […]

SQL Server loads disk and cpu – how to investigate

I often observe that my SQL express instance consumes substantional amount of CPU and disk bandwidth (please see typical illustration below). Searching for the clues I went to profiler, but observed very little activity – basically there are 4 connections that are doing simple queries in a timely manner (once per minute) and are used […]

SQL Server update statement performance

I have problem in optimizing an SQL query to do some data cleansing. In fact, I have a table which is a sort of referential of a multiple special characters and word. Let’s call it ABNORMAL(ID,PATTERN) I have also another table INDIVIDUALS containing a column (NAME) which I want to clean by removing from it […]

Column Indexing and DELETE query performance

I have a table with few Columns like ID (PK), Name, created_time etc. and periodically I DELETE rows from this table by using this simple DELETE query. DELETE FROM my_table WHERE created_time < ‘some time’; Just want to know what will be the performance impact on INSERT, SELECT and DELETE if I make or not […]

Slow CTE Subquery in MSSQL

I have a Query which takes on my SQL 2008 DB 1.4 seconds. Then I tried to do a subquery using this template: ;with __myResults as ( … my initial query … already has a column: row_number() over (ORDER BY …) as RowNum ) select * from __myResults where RowNum between X and Y If […]

Why is this SQL CAST performatic?

I was refactoring a heavy and very badly written stored procedure in SQL Server when I saw this code in a WHERE clause: SET @SELECT = @SELECT + ‘ CAST(LOG_MODULO.DataHora AS DATE) >= ”’+ @DATAINI + ”’ AND LOG_MODULO.DataHora <= ”’+ @DATAFIM +’ 23:59:00” ‘ ; LOG_MODULO.DataHora is a DATETIME column, @DATAINI and @DATAFIM are […]

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