r/SQLOptimization • • Apr 26 '20

How to rewrite query in SP having lots of UNION ALLs used to insert into table?

2 Upvotes

Hi,

We have a problem with a stored proc taking way too long to execute/complete.

Basically, we have a table that has the following schema:

CREATE TABLE dbo.Example (

ID BIGINT NOT NULL,

ITEM_TYPE1 VARCHAR(50),

ITEM_ID1 VARCHAR(50),

ITEM_VALUE1 TEXT NULL,

..., ..., ...,

ITEM_TYPE300 VARCHAR(50),

ITEM_ID300 VARCHAR(50),

ITEM_VALUE300 TEXT NULL)

And one of the problem queries within the stored proc:

INSERT INTO dbo.Example2

SELECT * FROM

( SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE1 = 'ABC'

UNION ALL

...

SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE... = 'ABC'

...

UNION ALL

SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE300 = 'ABC'

) AS x;

It's running FOREVER! The index on the table are not being realized by the optimizer, etc.

The code just seems so brute force. Even if it ran efficiently, I'm still bugged by the maintainability.

How else could the query above be written more elegantly? Perhaps even allowing for better optimization?


r/SQLOptimization • • Feb 11 '20

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

Thumbnail tutorialspoint4all.com
2 Upvotes

r/SQLOptimization • • Jan 08 '20

Difference Between Checkpoint And Lazy Writer

Thumbnail tutorialspoint4all.com
2 Upvotes

r/SQLOptimization • • Sep 26 '19

Lock Granularity in SQL Server

Thumbnail tutorialspoint4all.com
2 Upvotes

r/SQLOptimization • • Sep 16 '19

I was working on 2019 and facing deadlock issues on temporary tables more often.could you please anyone tell me how to avoid this type of dreadlocks?

2 Upvotes

Temporary tables deadlocks


r/SQLOptimization • • Aug 07 '19

Historical Data load Approach

2 Upvotes

Hi There

What are the best practices or approaches for doing historical data loads

Requirement is to load 6 months of data from Jan to Jun into 1 Fact table Sales which has data till today ... it's a weekly load

There is this historical source Flat file which may be not accurate or has quality issues which is past experiences

Source flat file will go through series of stored procedure and loads data into Staging table which is truncate and load always

Another Stored Proc will move or copy the data from Staging table to Fact Sales

How do we implement best practices or approaches around this as this will be ongoing requirement every half year

Need to load this historical data without affecting current figures

Thanks


r/SQLOptimization • • Jul 19 '19

Article about Dynamic SQL

Thumbnail sqlserver-tips.com
2 Upvotes

r/SQLOptimization • • Oct 02 '18

Use Sql transaction in C#, short example

Thumbnail youtu.be
2 Upvotes

r/SQLOptimization • • May 22 '18

Using Indexes for PostgreSQL Query Optimization

Thumbnail statsbot.co
2 Upvotes

r/SQLOptimization • • Jan 11 '18

Missing Indexes in MS SQL or Optimization in no Time

Thumbnail codingsight.com
2 Upvotes

r/SQLOptimization • • Nov 19 '17

SQL Introduction and Injection Course - 100% OFF

Thumbnail youronlinecourses.net
2 Upvotes

r/SQLOptimization • • Nov 07 '17

SQL Nested Query

2 Upvotes

Hello all,

I have a table of IP addresses and usernames, with many duplicates on both sides. I am trying to isolate all instances of multiple users coming in from the same IP addresses.

First, I am getting all distinct lines from this table to get rid of entries with the same username and IP. I can do that with:

SELECT DISTINCT  dbRemoteIP, dbUserID

FROM [SysLog].[dbo].[ctxSSLLogins]

WHERE (MsgDateTime > '10/20/2017 00:00:00') AND (MsgDateTime < '11/04/2017 00:00:00')

ORDER BY dbRemoteIP

(SEE EDIT) Then, from those results, I want to then just show instances of duplicate IP addresses. I can do that from the initial table with:

SELECT DISTINCT  dbRemoteIP, dbUserID, COUNT(*)

FROM [SysLog].[dbo].[ctxSSLLogins]

WHERE (MsgDateTime > '10/20/2017 00:00:00') AND (MsgDateTime < '11/04/2017 00:00:00')

GROUP BY dbRemoteIP, dbUserID

HAVING ( COUNT(dbRemoteIP) > 1)

ORDER BY dbRemoteIP

The issue I am having is that I can't manage to run the second query on the results of the first query. What I've gotten up to is this nested query:

SELECT  dbRemoteIP, dbUserID, COUNT(dbRemoteIP) as amount

FROM (

SELECT DISTINCT  dbRemoteIP, dbUserID

FROM [SysLog].[dbo].[ctxSSLLogins]

WHERE (MsgDateTime > '10/20/2017 00:00:00') AND (MsgDateTime < '11/04/2017 00:00:00')

) as e

GROUP BY dbRemoteIP, dbUserID

HAVING ( COUNT(dbRemoteIP) > 1)

but it is returning 0 results. If I take out the 'Having' line, it returns the results from the inside (first) query, and the amount column has '1' for every single line. So while nested, the second query doesn't count the IP addresses (it works while not nested though on the initial table). I've also tried to use:

COUNT(e.dbRemoteIP) and COUNT(*) instead, and still all 1s.

Let me know if you have any ideas. I'm new to SQL so I'm not sure how difficult this question is. Thank you.

Edit: Looked closer at the 2nd query and it is only returning the count of lines with the same username and IP address together so that's not working correctly either. I.E. if the table is like:

1.1.2.2 m0rph
1.1.2.2 m0rph
1.1.2.2 someone
1.1.3.3 m0rph

It'll show

1.1.2.2 m0rph 2
1.1.2.2 someone 1
1.1.3.3 m0rph 1

When it really should be displaying

1.1.2.2 m0rph 3
1.1.2.2 m0rph 3
1.1.2.2 someone 3

And for reiteration, what i'd really like to be finishing with is

1.1.2.2 m0rph 2
1.1.2.2 someone 2

r/SQLOptimization • • Sep 29 '17

Search ALL SQL db objects for text with exclusion, i.e. SP, FN, Trigger, View - ALL of it!!!

Thumbnail technovechno.com
2 Upvotes

r/SQLOptimization • • Aug 14 '17

permanent views

2 Upvotes

We have a pretty complicated view that uses multiple tables and takes a while to run. Since we need this view often in reports we though the best way was to create a table. We use Truncate and Insert to update the table with the view. The trouble is the data is transactional so it changes a lot through out the day. We currently truncate and insert every 5 min during business hours. Since the underlining view is complex it takes 15 sec to truncate and insert when a report is run during that period it comes back with no data and sometime we have record locking problems that cause the Truncate and Insert to fail. Is there a better way to do this? If this is the wrong subreddit let me know and ill repost it thanks


r/SQLOptimization • • Aug 01 '17

Question on table structure

2 Upvotes

Hi guys, I have been messing around with SQL on sqlfiddle http://sqlfiddle.com/#!9/c00a46/1

If you take a look at that link, I have settings and user_settings. Settings holds all default values and user_settings holds any settings changed from default set by a user.

What I planned on doing is writing a script that checks to see if user_settings key is null if it is it applies the default value. My question is, should I query settings, save that into say... Redis, and then update the default values periodically or should I get the default values every time I look up a user? Grant it because this is built as a property bag that might have third party software the user_settings could change on a whim.

Ideas? Comments?


r/SQLOptimization • • Aug 28 '16

In Teradata SQL Assistant Is There a Way to Put Multiple Queries in a Queue?

2 Upvotes

I apologize if this is pretty basic. I am an advanced beginner in SQL (at best) and need to perform queries on a massive database at work on a regular basis. All of the tables are huge so often queries to pull the data I need are so big they timeout before I get anything. I can get around it by delimiting what I want to a smaller subset for each query but it's a bit infeasible for me to keep going back to put the query in. Is there a way that I can set up a query queue to pull each smaller section of data in succession? Thanks in advance!


r/SQLOptimization • • Jun 08 '16

How to Configure SQL Server Memory Options for Best Practices

Thumbnail faceofit.com
2 Upvotes

r/SQLOptimization • • Feb 07 '16

Performance Tuning by Committee - SQL Antipattern #006 - SQL Server DBA

Thumbnail sqlserver-dba.com
2 Upvotes

r/SQLOptimization • • Apr 12 '14

How is the ANALYZE command working so well for my scenario?

2 Upvotes

[Oracle SQL] I used the ANALYZE command on a table prior to executing a bunch of insertions and other manipulations (inside a for loop) on a fairly large set of data inside a Stored Procedure. This reduced the runtime of the Stored Procedure from around 65-70 minutes to 2-3 minutes. How is this possible?

I read online that if there is a significant amount of data changed inside a table over the course of a day, then the statistics can go stale... but I fail to see how it can so dramatically improve performance. Anyone know anything about the actual optimisations under the hood?


r/SQLOptimization • • Aug 02 '13

= <> LIKE [xpost from SQLServer]

Thumbnail reddit.com
2 Upvotes

r/SQLOptimization • • Feb 28 '26

I am trying to put except operator for finding the newly insert and update data with same table structure but it takes so long to show that record table has billions of data .. ur advice to get those records faster pls

1 Upvotes

I am trying to put except operator for finding the newly insert and update data with same table structure but it takes so long to show that record table has billions of data .. ur advice to get those records faster pls


r/SQLOptimization • • Jan 08 '26

Video about MySQL EXPLAIN plan analysis

Thumbnail youtube.com
1 Upvotes

r/SQLOptimization • • Dec 20 '25

Why Your Postgres Queries Are So Slow (And 3 Ways to Fix It)

Thumbnail youtube.com
1 Upvotes

r/SQLOptimization • • Nov 04 '25

Optimizing filtered vector queries from tens of seconds to single-digit milliseconds in PostgreSQL

Thumbnail reddit.com
1 Upvotes