Result of dynamic sql with parameteres into a variable
-
That's one way to try and abstract the poor table-design. When used in a query, the Optimizer will evaluate the contents of the VIEW so indexing on the referenced tables is what will be relevant. Try it out and see what the performance is like. It may stink perfromance-wise when compared to dynamic SQL depending on whether SQL Server can eliminate looking in some of the tables for data based on the query. You could also consider an indexed view which would solve a lot of your problems, until you added a new TB_MASTER_??? table :Whistling:
Any effort to get out of the dynamic SQL mess you're in is effort well spent in my opinion, but it may turn out that without a full refactoring of the system, i.e. getting away from separate tables, it may be the best you can do.
Another approach to refactoring is to move all the data into a central table with a column that says the type of data, drop the old tables, and then add VIEWS with the old table's names that point to your central table and have the proper WHERE-clause to pickup the correct type of data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
So you know, we have separate tables, but there is column for line_code, so its good for view or one table ... new table according this view and insert all datas there, it can be possible, but just with trigger, because we have lot of program without procedure ( query inside source ) and lot of procedure which uses the separate tables ...
so best idea will be the view for this time, because work with dynamic sql is annoying sometimes --
but I am not sure about this performance... little test here with selected values
HERE ARE QUERIES
DECLARE @LINE_CODE VARCHAR(5)='CPM01'
DECLARE @COMMIT_NO varchar(4)='0001'
DECLARE @ORDER_DATE varchar(8)='20130425'
DECLARE @STATION_ID varchar(5)='CP-10'
DECLARE @sql varchar(2000)
DECLARE @StartTime datetime
SET @StartTime = GETDATE()
select * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID
SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
SET @StartTime = GETDATE()
set @sql='SELECT * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE='''+@ORDER_DATE+''' AND COMMIT_NO='''+@COMMIT_NO+''' AND STATION_ID='''+@STATION_ID+''''
execute (@sql)
SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
SET @StartTime = GETDATE()
select * from TB_MASTER_CPM01 WHERE ORDER_DATE=@ORDER_DATE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID
SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
and here is result in milisecond and %in execution plan
1.VIEW - between 50-90 -- 91%
2.DSQL - between 0-6 -- 4%
3.QUER - between 0-6 -- 5%
So maybe is not good idea, how I thought, if you execute lot of procedures with select every second...
what do you think about it ?
Maybe I will stay at dynamic sql ...
OR one next way >>>>
create mainly procedure, where will be condition with if = LINE_CODE , lets execute procedure_LINE_CODE
but it is not good for maintance, because you know, if you will do change, you have to edit all these procedures.
So i dont know now, if I want to change for do easy my work, I will find that old solution is almost best due to database desing here:doze:
-
Can you please post the DDL for one of the TB tables, including all constraints?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
rebuild for index is every saturday. Before was never 😀
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT distinct OBJECT_NAME(OBJECT_ID)
FROM sys.dm_db_index_physical_stats (DB_ID(N'MCS_MESDB'), NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent>'15'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD '
exec (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
ALTER TABLE [dbo].[TB_REINPUT]
REBUILD
GO
And here is DDL for table
USE [MCS_MESDB]
GO
/****** Object: Table [dbo].[TB_MASTER_CPM01] Script Date: 4/26/2013 3:38:29 AM ******/
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 OFF
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
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
-
If you add a CHECK CONSTRAINT to LINE_CODE on all your tables to let the optimizer know what values can exist in that table then the optimizer will be able to eliminate many of the tables involved in the VIEW without even looking at them, based on the WHERE clause you use with the VIEW.
You can see this behavior even with empty tables.
1. Create two empty tables with the same schema:
SET ANSI_NULLS ONGO
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
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 OFF
GO
2. This query:
WITH cteAS (
SELECT *
FROM dbo.TB_MASTER_CPM01
UNION ALL
SELECT *
FROM dbo.TB_MASTER_CPM02
)
SELECT *
FROM cte
WHERE LINE_CODE = 'a';
Results in this plan:
3. Now add CHECK CONSTRAINTs to both tables such that only rows with LINE_CODE = 'a' are allowed to exist in the 01 table, and rows with LINE_CODE = 'b' can exist in the 02 table:
ALTER TABLE dbo.TB_MASTER_CPM01 ADD CONSTRAINT [ck01] CHECK (LINE_CODE = 'a')GO
ALTER TABLE dbo.TB_MASTER_CPM02 ADD CONSTRAINT [ck02] CHECK (LINE_CODE = 'b')
GO
4. Now the same query in step 2 results in the plan below. Notice how it no only references the 01 table. That is because the CHECK CONSTRAINTs allow the Optimizer to eliminate table 02 from consideration based on the WHERE clause. If you add supporting CHECK CONSTRAINTs to all your table to inform the Optimizer about which LINE_CODES exist in each table the VIEW will likely perform much better:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Hello,
nice , it is good know it, i will try
-
You forgot to supply a WHERE clause in the query that uses the VIEW. You need a predicate that folters on the column where your check constraint is:
SELECT * FROM dbo.TB_MASTER_CPM_TEST WHERE LINE_CODE = 'a';edit: I see you may have backtracked...let me know if you run into any roadblocks with the technique
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
yes, i know, this is reason why i edited now 😀
but i found another bug, if i use variable is not function ... why ?
without
with
-
I used OPTION(RECOMPILE) and it is ok, but without is wrong,,,---- and in milisecond is little worst, but better than before. ,
-
tony28 (4/26/2013)
I used OPTION(RECOMPILE) and it is ok, but without is wrong,,,---- and in milisecond is little worst, but better than before.It has to do with how a plan is generated and cached. If the plan is cached it must work for all values sent to the plan for the initial execution and any future executions so the plan cannot exclude certain tables based on the value of the variable when the plan was initially generated.
The additional milliseconds of execution time using the VIEW is probably due to the fact that the engine, even though it can exclude scanning all the tables where the CHECK CONSTRAINT means no data can be found, still has to go through the motions of "computing" the UNION ALL. Maybe someone with more Optimizer experience will drop by and can explain that piece. At any rate though, the overhead introduced by the compute-operator is negligible and I would probably accept the overhead if it meant being able to trade in the dynamic SQL for declared SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (4/27/2013)
tony28 (4/26/2013)
I used OPTION(RECOMPILE) and it is ok, but without is wrong,,,---- and in milisecond is little worst, but better than before.It has to do with how a plan is generated and cached. If the plan is cached it must work for all values sent to the plan for the initial execution and any future executions so the plan cannot exclude certain tables based on the value of the variable when the plan was initially generated.
The additional milliseconds of execution time using the VIEW is probably due to the fact that the engine, even though it can exclude scanning all the tables where the CHECK CONSTRAINT means no data can be found, still has to go through the motions of "computing" the UNION ALL. Maybe someone with more Optimizer experience will drop by and can explain that piece. At any rate though, the overhead introduced by the compute-operator is negligible and I would probably accept the overhead if it meant being able to trade in the dynamic SQL for declared SQL.
yes I understand, I thought that it will be, because the ex.plan is still recreated
but I dont know, if I really good understand last sentence,,, you wrote me few post ago, why I use DSQL.
if it meant being able to trade in the dynamic SQL for declared SQL.
thank you for help
-
tony28 (4/28/2013)
opc.three (4/27/2013)
tony28 (4/26/2013)
I used OPTION(RECOMPILE) and it is ok, but without is wrong,,,---- and in milisecond is little worst, but better than before.It has to do with how a plan is generated and cached. If the plan is cached it must work for all values sent to the plan for the initial execution and any future executions so the plan cannot exclude certain tables based on the value of the variable when the plan was initially generated.
The additional milliseconds of execution time using the VIEW is probably due to the fact that the engine, even though it can exclude scanning all the tables where the CHECK CONSTRAINT means no data can be found, still has to go through the motions of "computing" the UNION ALL. Maybe someone with more Optimizer experience will drop by and can explain that piece. At any rate though, the overhead introduced by the compute-operator is negligible and I would probably accept the overhead if it meant being able to trade in the dynamic SQL for declared SQL.
yes I understand, I thought that it will be, because the ex.plan is still recreated
but I dont know, if I really good understand last sentence,,, you wrote me few post ago, why I use DSQL.
if it meant being able to trade in the dynamic SQL for declared SQL.
thank you for help
What I meant by that was, maintaining dynamic SQL is difficult and it opens lots of opportunities for injection attacks and coding mistakes since the code cannot be checked for accuracy until runtime.
With some basic refactoring, like putting a VIEW on top of the numbered tables and implementing some CHECK CONSTRAINTs, you can start moving towards a codebase where all the code is declared. Eliminating dynamic SQL from your codebase would lift many of the concerns I mentioned. To be fair, implementing the VIEW and CONSTRAINTs has some potential drawbacks too, namely:
1. the VIEW will need to be updated if new tables are added
2. the CHECK CONSTRAINTs could slow down data loads
So weigh all factors.
Ideally, long term, I would be looking for ways to completely get rid of the numbered tables and instead start loading everything into one table with a column like line_code that told me the source of the data...provided all incoming sources have and will continue to have the same schema, which is an assumption I have been making all along based on things that were said.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply