Tag: performance

sql select performance (Microsoft)

I have a very big table with many rows (50 million) and more than 500 columns. Its indexes are period and client. I need to keep for a period the client and another column (not an index). It takes too much time. So I’m trying to understand why: If I do: select count(*) from table […]

SQL Server Index Scan and Index Seek having the same performance

I’ve got four queries typed up right now trying to select a count from a very large table. The queries are essentially this. SELECT count(PrimaryKeyIntColumn) from dbo.TableName SELECT count(PrimaryKeyIntColumn) from dbo.TableName where PrimaryKeyIntColumn >= 0 SELECT count(PrimaryKeyIntColumn) from dbo.TableName where PrimaryKeyIntColumn IS NOT NULL SELECT count(PrimaryKeyIntColumn) from dbo.TableName where PrimaryKeyIntColumn BETWEEN 1 and 2147483647 When […]

Reset the ID counter on a stored procedure in SQL Server

I’m developing a system that manages work orders for vehicles. The ID of work orders is composed as follows: OT-001-16. Where OT- is a string, 001 is the counter, followed by – character and finally the number 16 is the current year. Example: If the current year is 2018, the ID should be OT-001-18. The […]

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

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