Tag: performance

Using EntityFramework for search is MASSIVELY slow. How can I improve the speed of the query?

I’m using Entity Framework 5.0.0 in an MVC 4 web site. Part of the site allows the user to search for records in the database. The search may contain up to 10 search criteria that may touch (generate a where clause) 4-5 tables and returns data from 14 tables. The search page offers many fields […]

SQL query to select prior record based on value

Using SQL Server 2012 Express. The SQL query below is horribly slow and need advice to enhance performance or do it more efficiently. It queries a table called PriceDaily and queries the Date column for a given date e.g. 2009/11/27 (the date can be any date and will actually be determined by a variable). Sorted […]

SQL Server Query Performance with a lot of data

I have the following query which inserts the results in another table Select Distinct * From( select t.RuleId ,t.Table3Id,Null as RiskLeveltypeId, (case when r.Count>=t.highlimit then 60 else case when r.Count>=t.mediumlimit then 30 else case when r.Count>=t.lowlimitthen 15 ELSE 0 end end end) as Score ,CreatedUser,GETDATE() as CreatedDate,CreatedUser as LastActivityUser,GETDATE() as LastActivityDate, t.Table2Id, t.Table1Id, CardId, 249 […]

Ms SQL Procedure running slow

DECLARE @userId INT = 1001 SELECT , Logins.IP , COUNT(LoginsOther.UserId) AS OtherUsersOnThisIp , MAX(Logins.DateHappened) AS LastUsed , Logins.UserId FROM Security.UserLogin AS Logins LEFT JOIN Security.UserLogin AS LoginsOther ON LoginsOther.IP = Logins.IP AND LoginsOther.UserId <> Logins.UserId WHERE Logins.UserId = @userId GROUP BY , Logins.IP , Logins.UserId So, I have Table UserLogin with columns: Id int, DateHappened […]

Fastest way for return rows count with where clause

I have big log4net database table with this structure. CREATE TABLE [dbo].[Log] ( [Id] [INT] IDENTITY(1,1) NOT NULL, [Date] [DATETIME] NOT NULL, [Thread] [VARCHAR](255) NOT NULL, [Level] [VARCHAR](50) NOT NULL, [Logger] [VARCHAR](255) NOT NULL, [Application] [VARCHAR](50) NOT NULL, [Server] [VARCHAR](50) NOT NULL, [Message] [VARCHAR](4000) NOT NULL, [Exception] [TEXT] NULL, [UserName] [NVARCHAR](50) NULL, [CorrelationId] [VARCHAR](255) NULL, […]

what resource my query is using and on which it is waiting for

I have a stored procedure which take the date range as an input and work on some CTE tables to produce the final result. the current problem is that the stored procedure is running for the last 3 days and there is nothing i can see on the sp_who2 procedure i.e blkby or anything. raughly […]

Creating an SQL Server Sandbox

There are some features in our LOB application that allow users to define their own queries to retrieve data for reports and listings within the app. The problem that we are encountering is that sometimes these queries they have written a really heavy (and sometimes erroneous) and cause massive load on the server. Removing these […]

Advice on sql server index performance

I have “UserLog” table with 15 millions rows. On this table I have a cluster index on User_ID field which is of type bigint identity, and a non clustered index on User_uid field which is of type varchar(35) (a fake uniqueidentifier). On my application we can have 2 categories of users connection. 1 of them […]

Sql query is running slower while using loop

I am a newbie with sql. Please don’t mind my question. Below is the query i am currently working on. This is a simple query that fetches value from one table and insert them + other values into another Two Tables. Now the problem is that, when i ran this query for 10 seconds, Total […]

SQL Server : estimated query cost does not correspond to real execution time

This is my story: I have a long running query, it takes about 10s to execute. In order to optimize it I’ve created an index that I thought might help – it’s creation was recommended by the sys.dm_db_missing_index_group_stats system view as well. But the server decided not to use the index. Out of curiosity, I […]

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