VIEW vs. DynamicSQL

Question

Post reply

VIEW vs. DynamicSQL

tony28

SSCertifiable

Points: 7108
More actions
April 30, 2013 at 3:04 am

#292767

Hello,
I have few questions,
I would like remove DSQL, but our design table is not good, but redesign table is for future, because few programs ( korean source, sometimes we dont have source... long story) still use direct statement in source.
So now we have separate table, but there is column line_code I dont know who designed this - Korean 😀
so my questions is for someone who understand about query optimazing ...
here is declare of one table , there is different just end of table for example CPM01, CPM02, according to help by opc.three I added check constraint for line code
USE [AdventureWorks2008R2]
GO
/****** Object: Table [dbo].[TB_MASTER_CPM01] Script Date: 12/28/2008 01:33:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_MASTER_CPM01](
[ORDER_DATE] [char](8) NOT NULL,
[COMMIT_NO] [char](4) NOT NULL,
[BODY_NO] [varchar](12) NOT NULL,
[CAR_CODE] [varchar](3) NOT NULL,
[LINE_CODE] [char](5) NOT NULL,
[STATION_ID] [char](5) NOT NULL,
[WORK_SEQ] [int] NOT NULL,
[POINT_SEQ] [int] NOT NULL,
[POINT_SER] [int] NOT NULL,
[WORK_TYPE] [varchar](2) NOT NULL,
[ITEM_CODE] [char](5) NULL,
[ALC_CODE] [varchar](4) NULL,
[WORK_CODE] [varchar](6) NOT NULL,
[WORK_POS] [char](1) NOT NULL,
[WORK_QTY] [int] NOT NULL,
[LIMITV_LOW] [decimal](5, 2) NULL,
[LIMITV_HIGH] [decimal](5, 2) NULL,
[OPTION_VALUE] [varchar](20) NULL,
[MASTER_DESC] [varchar](50) NULL,
CONSTRAINT [PK_TB_MASTER_CPM01] PRIMARY KEY CLUSTERED
(
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[BODY_NO] ASC,
[CAR_CODE] ASC,
[LINE_CODE] ASC,
[STATION_ID] ASC,
[WORK_SEQ] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ORDER DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TB_MASTER_CPM01', @level2type=N'COLUMN',@level2name=N'ORDER_DATE'
GO
ALTER TABLE [dbo].[TB_MASTER_CPM01] WITH CHECK ADD CONSTRAINT [check_CPM01] CHECK (([LINE_CODE]='CPM01'))
GO
ALTER TABLE [dbo].[TB_MASTER_CPM01] CHECK CONSTRAINT [check_CPM01]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SEQ] DEFAULT ((1)) FOR [POINT_SEQ]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SER] DEFAULT ((1)) FOR [POINT_SER]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_WORK_QTY] DEFAULT ((0)) FOR [WORK_QTY]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_LOW] DEFAULT ((0)) FOR [LIMITV_LOW]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_HIGH] DEFAULT ((0)) FOR [LIMITV_HIGH]
GO
USE [AdventureWorks2008R2]
GO
/****** Object: Table [dbo].[TB_MASTER_CPM02] Script Date: 12/28/2008 01:34:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_MASTER_CPM02](
[ORDER_DATE] [char](8) NOT NULL,
[COMMIT_NO] [char](4) NOT NULL,
[BODY_NO] [varchar](12) NOT NULL,
[CAR_CODE] [varchar](3) NOT NULL,
[LINE_CODE] [char](5) NOT NULL,
[STATION_ID] [char](5) NOT NULL,
[WORK_SEQ] [int] NOT NULL,
[POINT_SEQ] [int] NOT NULL,
[POINT_SER] [int] NOT NULL,
[WORK_TYPE] [varchar](2) NOT NULL,
[ITEM_CODE] [char](5) NULL,
[ALC_CODE] [varchar](4) NULL,
[WORK_CODE] [varchar](6) NOT NULL,
[WORK_POS] [char](1) NOT NULL,
[WORK_QTY] [int] NOT NULL,
[LIMITV_LOW] [decimal](5, 2) NULL,
[LIMITV_HIGH] [decimal](5, 2) NULL,
[OPTION_VALUE] [varchar](20) NULL,
[MASTER_DESC] [varchar](50) NULL,
CONSTRAINT [PK_TB_MASTER_CPM02] PRIMARY KEY CLUSTERED
(
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[BODY_NO] ASC,
[CAR_CODE] ASC,
[LINE_CODE] ASC,
[STATION_ID] ASC,
[WORK_SEQ] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ORDER DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TB_MASTER_CPM02', @level2type=N'COLUMN',@level2name=N'ORDER_DATE'
GO
ALTER TABLE [dbo].[TB_MASTER_CPM02] WITH CHECK ADD CONSTRAINT [check_CPM02] CHECK (([LINE_CODE]='CPM02'))
GO
ALTER TABLE [dbo].[TB_MASTER_CPM02] CHECK CONSTRAINT [check_CPM02]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM02] ADD CONSTRAINT [DF_TB_MASTER_CPM02_POINT_SEQ] DEFAULT ((1)) FOR [POINT_SEQ]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM02] ADD CONSTRAINT [DF_TB_MASTER_CPM02_POINT_SER] DEFAULT ((1)) FOR [POINT_SER]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM02] ADD CONSTRAINT [DF_TB_MASTER_CPM02_WORK_QTY] DEFAULT ((0)) FOR [WORK_QTY]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM02] ADD CONSTRAINT [DF_TB_MASTER_CPM02_LIMITV_LOW] DEFAULT ((0)) FOR [LIMITV_LOW]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM02] ADD CONSTRAINT [DF_TB_MASTER_CPM02_LIMITV_HIGH] DEFAULT ((0)) FOR [LIMITV_HIGH]
GO
USE [AdventureWorks2008R2]
GO
/****** Object: Table [dbo].[TB_MASTER_CPM03] Script Date: 12/28/2008 01:34:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_MASTER_CPM03](
[ORDER_DATE] [char](8) NOT NULL,
[COMMIT_NO] [char](4) NOT NULL,
[BODY_NO] [varchar](12) NOT NULL,
[CAR_CODE] [varchar](3) NOT NULL,
[LINE_CODE] [char](5) NOT NULL,
[STATION_ID] [char](5) NOT NULL,
[WORK_SEQ] [int] NOT NULL,
[POINT_SEQ] [int] NOT NULL,
[POINT_SER] [int] NOT NULL,
[WORK_TYPE] [varchar](2) NOT NULL,
[ITEM_CODE] [char](5) NULL,
[ALC_CODE] [varchar](4) NULL,
[WORK_CODE] [varchar](6) NOT NULL,
[WORK_POS] [char](1) NOT NULL,
[WORK_QTY] [int] NOT NULL,
[LIMITV_LOW] [decimal](5, 2) NULL,
[LIMITV_HIGH] [decimal](5, 2) NULL,
[OPTION_VALUE] [varchar](20) NULL,
[MASTER_DESC] [varchar](50) NULL,
CONSTRAINT [PK_TB_MASTER_CPM03] PRIMARY KEY CLUSTERED
(
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[BODY_NO] ASC,
[CAR_CODE] ASC,
[LINE_CODE] ASC,
[STATION_ID] ASC,
[WORK_SEQ] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ORDER DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TB_MASTER_CPM03', @level2type=N'COLUMN',@level2name=N'ORDER_DATE'
GO
ALTER TABLE [dbo].[TB_MASTER_CPM03] WITH CHECK ADD CONSTRAINT [check_CPM03] CHECK (([LINE_CODE]='CPM03'))
GO
ALTER TABLE [dbo].[TB_MASTER_CPM03] CHECK CONSTRAINT [check_CPM03]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM03] ADD CONSTRAINT [DF_TB_MASTER_CPM03_POINT_SEQ] DEFAULT ((1)) FOR [POINT_SEQ]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM03] ADD CONSTRAINT [DF_TB_MASTER_CPM03_POINT_SER] DEFAULT ((1)) FOR [POINT_SER]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM03] ADD CONSTRAINT [DF_TB_MASTER_CPM03_WORK_QTY] DEFAULT ((0)) FOR [WORK_QTY]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM03] ADD CONSTRAINT [DF_TB_MASTER_CPM03_LIMITV_LOW] DEFAULT ((0)) FOR [LIMITV_LOW]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM03] ADD CONSTRAINT [DF_TB_MASTER_CPM03_LIMITV_HIGH] DEFAULT ((0)) FOR [LIMITV_HIGH]
GO
USE [AdventureWorks2008R2]
GO
/****** Object: Table [dbo].[TB_MASTER_CPM04] Script Date: 12/28/2008 01:35:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_MASTER_CPM04](
[ORDER_DATE] [char](8) NOT NULL,
[COMMIT_NO] [char](4) NOT NULL,
[BODY_NO] [varchar](12) NOT NULL,
[CAR_CODE] [varchar](3) NOT NULL,
[LINE_CODE] [char](5) NOT NULL,
[STATION_ID] [char](5) NOT NULL,
[WORK_SEQ] [int] NOT NULL,
[POINT_SEQ] [int] NOT NULL,
[POINT_SER] [int] NOT NULL,
[WORK_TYPE] [varchar](2) NOT NULL,
[ITEM_CODE] [char](5) NULL,
[ALC_CODE] [varchar](4) NULL,
[WORK_CODE] [varchar](6) NOT NULL,
[WORK_POS] [char](1) NOT NULL,
[WORK_QTY] [int] NOT NULL,
[LIMITV_LOW] [decimal](5, 2) NULL,
[LIMITV_HIGH] [decimal](5, 2) NULL,
[OPTION_VALUE] [varchar](20) NULL,
[MASTER_DESC] [varchar](50) NULL,
CONSTRAINT [PK_TB_MASTER_CPM04] PRIMARY KEY CLUSTERED
(
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[BODY_NO] ASC,
[CAR_CODE] ASC,
[LINE_CODE] ASC,
[STATION_ID] ASC,
[WORK_SEQ] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ORDER DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TB_MASTER_CPM04', @level2type=N'COLUMN',@level2name=N'ORDER_DATE'
GO
ALTER TABLE [dbo].[TB_MASTER_CPM04] WITH CHECK ADD CONSTRAINT [check_CPM04] CHECK (([LINE_CODE]='CPM04'))
GO
ALTER TABLE [dbo].[TB_MASTER_CPM04] CHECK CONSTRAINT [check_CPM04]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM04] ADD CONSTRAINT [DF_TB_MASTER_CPM04_POINT_SEQ] DEFAULT ((1)) FOR [POINT_SEQ]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM04] ADD CONSTRAINT [DF_TB_MASTER_CPM04_POINT_SER] DEFAULT ((1)) FOR [POINT_SER]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM04] ADD CONSTRAINT [DF_TB_MASTER_CPM04_WORK_QTY] DEFAULT ((0)) FOR [WORK_QTY]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM04] ADD CONSTRAINT [DF_TB_MASTER_CPM04_LIMITV_LOW] DEFAULT ((0)) FOR [LIMITV_LOW]
GO
ALTER TABLE [dbo].[TB_MASTER_CPM04] ADD CONSTRAINT [DF_TB_MASTER_CPM04_LIMITV_HIGH] DEFAULT ((0)) FOR [LIMITV_HIGH]
GO
here is view
USE [MCS_MESDB]
GO
/****** Object: View [dbo].[VIEW_MASTER] Script Date: 4/30/2013 10:55:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VIEW_MASTER] WITH SCHEMABINDING AS
SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]
,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]
,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM01
UNION ALL
SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]
,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]
,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM02
UNION ALL
SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]
,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]
,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM03
UNION ALL
SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]
,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]
,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM04
GO
And here I have few testing queries
DECLARE @LINE_CODE VARCHAR(5)='CPM01'
DECLARE @COMMIT_NO varchar(4)='42'
DECLARE @ORDER_DATE varchar(8)='20130301'
DECLARE @STATION_ID varchar(5)='CP-8'
DECLARE @sql varchar(2000)
DECLARE @nsql nvarchar(2000)
DECLARE @StartTime datetime
SET @StartTime = GETDATE()
select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID OPTION(RECOMPILE);
SELECT ExecutionTimeInMSVieWVariableOPt = DATEDIFF(millisecond, @StartTime, getdate())
SET @StartTime = GETDATE()
select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;
SELECT ExecutionTimeInMSVieWVariable = DATEDIFF(millisecond, @StartTime, getdate())
--it cannot be, becuase there isnt variable
SET @StartTime = GETDATE()
select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE='CPM01' AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;
SELECT ExecutionTimeInMSVieWwithoutVar = DATEDIFF(millisecond, @StartTime, getdate())
--we use
SET @StartTime = GETDATE()
set @sql='SELECT TOP 1 * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE='''+@ORDER_DATE+''' AND COMMIT_NO='''+@COMMIT_NO+''' AND STATION_ID='''+@STATION_ID+''''
execute (@sql)
SELECT ExecutionTimeInMSDynamic = DATEDIFF(millisecond, @StartTime, getdate())
SET @StartTime = GETDATE()
select TOP 1 * from TB_MASTER_CPM01 WHERE ORDER_DATE=@ORDER_DATE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID
SELECT ExecutionTimeInMSQuery = DATEDIFF(millisecond, @StartTime, getdate())
FOR TEST you can add to table just one row with parameteres of variable, another columns you can add random..
And next I used this command for better result
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
but the background of this I dont understand very well.
very thank you for response
--edited created table, views and queries

Viewing 6 posts - 1 through 5 (of 5 total)

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

tony28 SSCertifiable Points: 7108 More actions · Answer 1

I would like use for best one of first three queries

but

first is not good, because there is still recreate ex.plan

second is slowly, because without OPTION(RECOMPILE) the select selected from all tables and doesnt use check constraint I think

third is not good, because we would have to use if condition with lot of same commands.

now I use last for one table or second last for all line_code, but the DSQL is sometimes annoying and not good for injection and etc..

do you know about some idea how can I optimazing these queries ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Is column [LINE_CODE] correctly populated in all of the tables?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tony28 SSCertifiable Points: 7108 More actions · Answer 3

yes, for example we have 10 table --- TB_MASTER_CPM01 - 10 and inside each of tables is column LINE_CODE with end of name this table, I dont know why korean guys designed tables like this, but now I cannot redesign immediately, we have some task for future with this, but now I would like try another solution with this design...

tony28 SSCertifiable Points: 7108 More actions · Answer 4

tony28

SSCertifiable

Points: 7108

May 3, 2013 at 10:27 am

#1612281

😎

tony28 SSCertifiable Points: 7108 More actions · Answer 5

Can be problem with some convert? char-varchar? or somethink? or that the primary key in table is string ?

I dont know, why the query selected all tables with union with constraint and without variable is just one table and constraint was use..

here is query for filling tables, just change end of name table and value line_code

declare @i int = 0
declare @i1 int = 0
while ( @i<9999)
BEgin
while (@i1<20)
begin
INSERT INTO TB_MASTER_CPM01
(
[ORDER_DATE]
,[COMMIT_NO]
,[BODY_NO]
,[CAR_CODE]
,[LINE_CODE]
,[STATION_ID]
,[WORK_SEQ]
,[POINT_SEQ]
,[POINT_SER]
,[WORK_TYPE]
,[ITEM_CODE]
,[ALC_CODE]
,[WORK_CODE]
,[WORK_POS]
,[WORK_QTY]
,[LIMITV_LOW]
,[LIMITV_HIGH]
,[OPTION_VALUE]
,[MASTER_DESC]
)
VALUES ( '20130301',@i,'AAA 666666','EL','CPM01','CP-'+CAST(@i1 as varchar),1,1,1,'B','CP010','AAA','WC10','L',3,0.0,0.0,'AA','')
set @i1=@i1+1
end
set @i1=0
set @i=@i+1
END

thx