Tag: performance

Improving performance of wildcard date pattern searchs

I have a wildcard search that I need to improve performance: mycolumn like ‘%-%%-%’ There is a way to do that? (for exemple, with Full-Text Index?) Obs: I have to find dates in my text that follows the pattern, like 21-07-2016. EDIT I have a legacy database that I cannot change the structure. So, I […]

Left Outer Join Query Extremely Slow – SQL Server

Huge issue with an SQL Server Query! So the query below utilizes JOIN commands in the query, but takes no less than 15-20 minutes to run! No idea how to better optimize this code 🙁 Any ideas on how to fix this issue? SELECT dbo.DB_Timetrack.[JOB #], dbo.DB_Timetrack.[Last Name], dbo.DB_Timetrack.[First Name], dbo.DB_Timetrack.HOURS, dbo.DB_Timetrack.BEGDATE, dbo.DB_Timetrack.LOCATNID, dbo.DB_Timetrack.DESCR, dbo.DB_Timetrack.CODE, […]

MS SQL Query all table too slow

I’m encoutering a problem with my query. i’m in MS SQL and I try to get all associated table from my patient. From here,everything is going fine. I build up my query first then I execute it. When I execute it, It takes more than 2 min to finish. It’s way too long And I […]

Minimum and maximum memory usage in SQL Server?

I’ve been looking around but my google-fu isn’t too good these days it seems. I’m trying to find more information about the minimum and maximum memory settings for SQL Server instances. If you open SQL Server Management Studio, right-click on an instance, and go to the ‘Memory’ tab in the left-hand menu, you can find […]

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

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