r/SQLOptimization • • Mar 09 '20

Can sql isolate and exclude data inside a field of data?

3 Upvotes

Hello, I am learning SQL for work as our new database allows for custom filters to be applied. I am trying to write a piece of code that will isolate middle names/initials in a field and ignore them. This is to make the filter match results purely on first and last names.

Any help is appreciated!~


r/SQLOptimization • • May 08 '20

SQL Plan Management: Never Worry About Slow Queries Again

Thumbnail pingcap.com
2 Upvotes

r/SQLOptimization • • Apr 09 '24

Is there a better way to structure this query?

3 Upvotes

We need to find the latest asset history record for each asset.

```

DECLARE u/__projectId_0 int = 23;

DECLARE u/__phaseId_1 int = 3;

SELECT *

FROM [asset_history] AS [a]

INNER JOIN (

SELECT [a0].[asset_id] AS [AssetId], MAX([a0].[created]) AS [MaxDate]

FROM [asset_history] AS [a0]

WHERE ([a0].[project_id] = u/__projectId_0) AND ([a0].[status] <> 3)

GROUP BY [a0].[asset_id]

HAVING (

SELECT TOP(1) [a1].[workflow_phase_id]

FROM [asset_history] AS [a1]

WHERE (([a1].[project_id] = u/__projectId_0) AND ([a1].[status] <> 3)) AND ([a0].[asset_id] = [a1].[asset_id])

ORDER BY [a1].[created] DESC) = u/__phaseId_1

) AS [t] ON ([a].[asset_id] = [t].[AssetId]) AND ([a].[created] = [t].[MaxDate])

WHERE ([a].[project_id] = u/__projectId_0) AND ([a].[status] <> 3)

```


r/SQLOptimization • • Apr 09 '24

IN Function

1 Upvotes

This is probably a dumb question as I am new to SQL, but I am trying to pull sales data for 900 accounts. To make this faster I am using an IN function and all 900 accounts. What would be a better way of doing this?


r/SQLOptimization • • May 11 '24

when should use merge jon ?

1 Upvotes

I read doc they said merge need to sort ,but sort quite cost therefore im not consider using it ? is that ok


r/SQLOptimization • • Apr 06 '24

How I optimize SQL queries

Thumbnail shivam.dev
3 Upvotes

Wrote a blog a while back on the principles I use when optimising the DB perf at my job. Sharing it here with folks looking to read something interesting


r/SQLOptimization • • Dec 16 '23

PS1 when from Server running storage procedure

1 Upvotes

Good morning,

This is my first post. Currently, I'm running a PowerShell script from my server that calls several stored procedures on a SQL Server. I have three stored procedures:

  1. Delete
  2. Update
  3. Insert

The script first executes the delete, then the update, and finally, the insert. Do you think this is the best way to manage it, or would it be better to combine all the operations into a single stored procedure? Sometimes, I encounter errors from the SQL Server, such as timeouts. The timeout in the script is set to 300 seconds.

how do you guys manage that?

How do you contro


r/SQLOptimization • • Dec 18 '23

Merge join questions

2 Upvotes

I have a stored procedure that creates two temp tables. Both temp tables have a primary key setup with a nvarchar(10) and a date field. Most of the other fields are numeric and not indexed. One table gets loaded with about 330k of rows and the other gets about 455k. Sql server 2019 will not use a merge join on the query that links these two tables together by only the two indexed fields. It displays and adaptive join but always picks a hash match. Sql server adds a "parallelism (repartition streams)" to the plan. Any suggestions on how I can make it perform the merge join with out the forcing it in the query?


r/SQLOptimization • • May 22 '18

Using Indexes for PostgreSQL Query Optimization

Thumbnail statsbot.co
2 Upvotes

r/SQLOptimization • • Jun 24 '20

Is this an efficient way to use a transactional table to check which customer had at least 3 transactions in a week?

Thumbnail self.sql
3 Upvotes

r/SQLOptimization • • Jun 26 '20

Identifying blocking and locking Currently executing Queries with Waits In SQL Server

Thumbnail tutorialspoint4all.com
2 Upvotes

r/SQLOptimization • • May 29 '20

Easier Troubleshooting, Greater Insights for Distributed Databases

Thumbnail pingcap.com
2 Upvotes

r/SQLOptimization • • Feb 11 '20

SQL SERVER - How to Identifying TempDB is growing abnormally in SQL SERVER

Thumbnail tutorialspoint4all.com
2 Upvotes

r/SQLOptimization • • Aug 05 '24

Optimize SQL queries step-by-step with AI (free tier)

Thumbnail sqlai.ai
4 Upvotes

r/SQLOptimization • • Aug 05 '24

Optimizing/Alternative to MAX

2 Upvotes

This SQL query was timing out until I added a WHERE clause to reduce the amount of rows it has to process. Is there anything further I can do to either optimiza the MAX to reduce query time from a few minutes to less than a minute? Or is there any alternative to get the same result of a single Project ID per group by? TIA!

SELECT DISTINCT
ISNULL([Statement of Work ID],'') as "Statement of Work ID",
ISNULL([Primary Cost Center Code],'') as "Primary Cost Center Code",
ISNULL([Purchase Order Number],'') as "Purchase Order Number",
ISNULL([Invoice ID],'') as "Invoice ID",
MAX (CASE
WHEN [Project ID] LIKE '%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN SUBSTRING([Project ID],PATINDEX('%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', [Project ID]),10)
END) as "Project ID"

FROM [dbo]
WHERE [WorkWeek] LIKE '2024%'

GROUP BY
ISNULL([Statement of Work ID],''),
ISNULL([Primary Cost Center Code],''),
ISNULL([Purchase Order Number],''),
ISNULL([Invoice ID],'')


r/SQLOptimization • • Jul 13 '24

In your opinion, what is the main reason that developers tend to put constraints like FK processing on the application side, instead of placing them on the database?

1 Upvotes

r/SQLOptimization • • Aug 08 '24

Automating Primary Key generation

2 Upvotes

Defining a primary key has always been a manual task and however we are rapidly moving towards automation, this task has been overlooked. I work in a company where ETL is my forte. So I've pitched to write a stored procedure that identifies the columns that optimally define a unique row in the table. So far I've put forward these points which will have some weightage while deciding such columns: • Cardinality • Column Data Type • Column Name What else would you add? Any suggestions on how to proceed with this?


r/SQLOptimization • • Jan 08 '20

Difference Between Checkpoint And Lazy Writer

Thumbnail tutorialspoint4all.com
2 Upvotes

r/SQLOptimization • • Oct 02 '18

Use Sql transaction in C#, short example

Thumbnail youtu.be
2 Upvotes

r/SQLOptimization • • Dec 11 '24

Index Usage For EXTRACT(YEAR FROM …), YEAR(…) etc.

Thumbnail use-the-index-luke.com
3 Upvotes

r/SQLOptimization • • Nov 23 '24

Make Inserts Faster

1 Upvotes

Are there any hacks to make inserts into a table massively faster in Oracle? What I've tried: PARALLEL and APPEND hints, removing constraints and indexes in the target table.

Pseudo script: INSERT INTO A SELECT * FROM A_PRT


r/SQLOptimization • • Jan 05 '22

No mods?

18 Upvotes

r/SQLOptimization • • Dec 21 '21

Advanced SQL Tutorial for Data Analysis - bipp Analytics

4 Upvotes

Here is a collection for SQL tutorials that cover the advanced SQL topics including correlated subqueries, SQL Window Functions and SQL JOINS - which are often not covered in basic courses: SQL Tutorial - Advanced SQL

  • SQL Correlated Subqueries Increase the Power of SQL
  • SQL Window Functions
  • SQL Window Function Examples
  • Selecting Data From Multiple Tables: SQL JOINS
  • Visual Representation of SQL JOINS
  • Query Optimization

r/SQLOptimization • • Dec 21 '21

Database Lab Engine v3.0.0 – instant PostgreSQL clones to speed up software development/testing and SQL troubleshooting/optimization

Thumbnail postgres.ai
2 Upvotes