SQL VIEW works on its own but not when combined

Question

SQL VIEW works on its own but not when combined

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

Matt Miller (4) wrote:

Since you're not at liberty to share the queries - the only input I would have is - eat the elephant one bite a t a time. The one query with 12 massive views is trying to do too much, so break it into pieces. In short - change your process to dump each of the 12 views into an actual table then group by from there. If that's too slow - then look at adding an index to said work table to improve the grouping. If that's still too slow - then look at how you might pre-aggregate some of the data ahead of time: since you mentioned 12 I am assuming 12 months, so did the older months actually change or do they stay static after some time. Using what you know about the data patterns is allowed - if the older data is static don't keep reaggregating over and over.

This is good advice. It's going to be extremely difficult for us to do anything except offer relatively vague suggestions since we're working without evidence or knowledge of the precise situation. There is no magic "run faster" switch that we can tell you about. It's all down to your code and your structures.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

thenewbee wrote:

Hi there, I have around 12 views and that works perfect if I execute them individually but when I combine them using UNION ALL it takes long time to execute and results in a time out error. Each view is bit complicated and brings back huge volume of data and they are grouped Please throw your thoughts on this Regards tnb

If they work well individually then you could query them individually. Just create a temporary table to insert the results into. Then insert the results into the temporary table one view at a time.

CREATE TABLE #myTempTable(Col1 int ...)

INSERT INTO #myTempTable
SELECT * FROM myView1;

INSERT INTO #myTempTable
SELECT * FROM myView2;

...

INSERT INTO #myTempTable
SELECT * FROM myView12;

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

I agree with the others about using the "Divide'n'Conquer" method. The only thing is that if the output of these views is massive, you could end up having some problems if you build the output for the 12 views into TempDB. With that, I'll suggest the idea of using either a "Scratch" schema or even a "Scratch" database, which should be in the SIMPLE recovery model and excluded from backups. In either case, it would also be helpful if you built a nightly "sweeper" scheduled job for either the "scratch" schema or database that deletes any and all objects in that schema or database that are over a week old.

This isn't just a recommendation on my part. It's what I've done with my systems (all of them) and it has many uses. For example, it IS handy to be able to save run logs (the output of the stored procedures themselves) from jobs to help troubleshooting if something goes awry and, since no one actually remembers to go clean up their stuff, the "sweeper" auto-magically takes care of that for them AND it doesn't clog up MSDB either. It also means that you don't have to grant privs for MSDB for people that need to be able to see the logs. Keep in mind these are VERY short term things and are NOT meant to be an audit system of any kind. They're just there in case something goes haywire and you need to see what the run results were. And, yeah... someone would actually have to create the table in code in then change the proc to write status of steps. It's not as difficult as one would think and, with a little forethought, you might even write out the duration of sections to assist in determining why a stored procedure is suddenly or continuously taking so long. We do all of that an it has been immensely helpful.

And, of course, you can also use it for things like this post is about without clogging up TempDB, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thenewbee SSC Eights! Points: 861 More actions · Answer 4

Thanks all once again..

How can I attach the execution plan

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

thenewbee wrote:

Thanks all once again.. How can I attach the execution plan

Ya know... since they've updated this forum to new software, that's a really good question. I hope they fix that really soon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thenewbee SSC Eights! Points: 861 More actions · Answer 6

Please see a sample script for 3 tables, is there any area for improvement. I noticed a hash join in the execution plan and was thinking nested join is better than hash.

The record count in each table is below

Table Cnt

Table JB 140732

Table PD 19329

Table SD 5889

/****** Object:  Table [dbo].[JB]    Script Date: 01/05/2019 12:19:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JB](
[SCHEME_CODE] [int] NULL,
[PLAN_ID] [int] NULL,
[STAT_KEY] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_JB] PRIMARY KEY CLUSTERED 
(
[STAT_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PD]    Script Date: 01/05/2019 12:19:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].PD(
[PLAN_ID] [int] NOT NULL,
[PLAN_START] [datetime] NULL,
[PLAN_END] [datetime] NULL,

 CONSTRAINT [PK_PD] PRIMARY KEY CLUSTERED 
(
[PLAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[SD]    Script Date: 01/05/2019 12:19:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SD](
[SCHEME_CODE] [int] NOT NULL,
[SCHEME_NAME] [nvarchar](255) NULL,
[GSA] [nvarchar](255) NULL,
[PAY_METHOD] [nvarchar](255) NULL,

 CONSTRAINT [PK_SD] PRIMARY KEY CLUSTERED 
(
[SCHEME_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IDX_JB_PLAN_ID]    Script Date: 01/05/2019 12:19:23 ******/
CREATE NONCLUSTERED INDEX [IDX_JB_PLAN_ID] ON [dbo].[JB]
(
[PLAN_ID] ASC
)
INCLUDE ( [SCHEME_CODE],
[STAT_KEY]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IDX_JB_PLAN_SCHEME]    Script Date: 01/05/2019 12:19:23 ******/
CREATE NONCLUSTERED INDEX [IDX_JB_PLAN_SCHEME] ON [dbo].[JB]
(
[SCHEME_CODE] ASC
)
INCLUDE ( [PLAN_ID],
[STAT_KEY]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IDX_JB_STAT_KEY]    Script Date: 01/05/2019 12:19:23 ******/
CREATE NONCLUSTERED INDEX [IDX_JB_STAT_KEY] ON [dbo].[JB]
(
[STAT_KEY] ASC
)
INCLUDE ( [SCHEME_CODE],
[PLAN_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IDX_JB_STAT_KEY]    Script Date: 01/05/2019 12:19:23 ******/
CREATE NONCLUSTERED INDEX [IDX_JB_STAT_KEY] ON [dbo].[PD]
(
[PLAN_ID] ASC
)
INCLUDE ( [PLAN_START],
[PLAN_END]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IDX_PD_PLAN_DT]    Script Date: 01/05/2019 12:19:23 ******/
CREATE NONCLUSTERED INDEX [IDX_PD_PLAN_DT] ON [dbo].[PD]
(
[PLAN_ID] ASC
)
INCLUDE ( [PLAN_START],
[PLAN_END]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IDX_SD_INFO]    Script Date: 01/05/2019 12:19:23 ******/
CREATE NONCLUSTERED INDEX [IDX_SD_INFO] ON [dbo].[SD]
(
[SCHEME_CODE] ASC
)
INCLUDE ( [SCHEME_NAME],
[GSA]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO