December 7, 2009 at 11:25 am
SQL 2005 - There are 2 databases each with a table. A third database contains a view and works correctly when the value used in the view is static by only selecting 1 of the 2 tables. Recdate is the check constraint where 1 table contains 2009 date and the other table contains 2008 date. For example "select * from aview where recdate = '12/01/2009'" in the query plan only selects the 2009 table and results return quickly.
If I change the query to be parameterized. For example:
declare @rdate smalldatetime
set @rdate = '12/01/2009'
select * from aview where recdate = @rdate
The query plan now shows 2008 and 2009 tables and the performance is slow.
Are there ways to fix this problem with the query not seeing the correct date?
Thanks
December 7, 2009 at 12:08 pm
Please post the view, sample data, and table structures. This will help in coming up with a legitimate answer.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2009 at 2:06 pm
-- Below is a test case for you to confirm the raised issue about the query plan and paramterized value with check constraint.
-- Here is some code to create 3 databases TestA, TestB and TestC and places a table with data into TestA and TestB and a view
-- in TestC
CREATE DATABASE TestB
GO
USE testb
GO
CREATE TABLE [dbo].[Table2008](
[rowid] [int] NOT NULL,
[filedate] [smalldatetime] NOT NULL,
[transdate] [smalldatetime] NULL,
[test] [int] NULL,
CONSTRAINT [PK_Table2008] PRIMARY KEY CLUSTERED
(
[filedate] ASC,
[rowid] 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
ALTER TABLE [dbo].[Table2008] WITH CHECK ADD CONSTRAINT [CK_Table2008] CHECK (([filedate]>='01/01/2008' and [filedate] <='12/31/2008'))
GO
ALTER TABLE [dbo].[Table2008] CHECK CONSTRAINT [CK_Table2008]
-- insert some data
insert into table2008 select 1,'01/01/2008','01/01/2009',130
insert into table2008 select 1,'01/02/2008','01/01/2009',140
insert into table2008 select 1,'01/03/2008','01/01/2009',150
insert into table2008 select 1,'01/04/2008','01/01/2009',160
insert into table2008 select 1,'01/05/2008','01/01/2009',170
GO
------------------------------------------------
CREATE database TestA
GO
USE TestA
GO
CREATE TABLE [dbo].[Table2009](
[rowid] [int] NOT NULL,
[filedate] [smalldatetime] NOT NULL,
[transdate] [smalldatetime] NULL,
[test] [int] NULL,
CONSTRAINT [PK_Table2009] PRIMARY KEY CLUSTERED
(
[filedate] ASC,
[rowid] 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
ALTER TABLE [dbo].[Table2009] WITH CHECK ADD CONSTRAINT [CK_Table2009] CHECK (([filedate]>='01/01/2009' and [filedate] <='12/31/2009'))
GO
ALTER TABLE [dbo].[Table2009] CHECK CONSTRAINT [CK_Table2009]
GO
-- insert some data
insert into table2009 select 1,'01/01/2009','01/01/2009',30
insert into table2009 select 1,'01/02/2009','01/01/2009',40
insert into table2009 select 1,'01/03/2009','01/01/2009',50
insert into table2009 select 1,'01/04/2009','01/01/2009',60
insert into table2009 select 1,'01/05/2009','01/01/2009',70
GO
---------------------------------------------------------
CREATE DATABASE TestC
GO
USE [testC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[v_test]
as
select * from testa.dbo.table2009
union all
select * from testb.dbo.table2008
--------------------------------------------------------------------------------
--IN TESTC DATABASE EXECUTE THE FOLLOWING "INCLUDING ACTUAL QUERY PLAN"
use Testc
go
--THIS WORKS WITH ONLY 2009 TABLE IN QUERY PLAN
select * from v_test where filedate = '01/01/2009'
GO
use Testc
go
-- THIS FAILS WITH 2009 AND 2008 IN QUERY PLAN
declare @fd smalldatetime
set @fd = '01/01/2009'
select * from v_test where filedate = @fd
GO
December 7, 2009 at 5:18 pm
Ah now I see what the issue is.
I was able to get both to use the same execution plan by changing the Parameterization option in TestC db to 'Forced'
In the attachments, you will see that the Original had:
WHERE [filedate]=@1
The query was being parameterized, just not the same as in the Forced parameter query (second query).
The change I made forces both queries into a parameterization and thus they used the same Plan for this scenario.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2009 at 5:20 pm
BTW, thanks for posting that information.
The tsql to change the parameterization is:
ALTER DATABASE [TestC] SET PARAMETERIZATION FORCED
GO
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2009 at 9:31 am
The JPG of the new query plans do not show that the problem was solved. It actually made it worst because the static version now shows both 2008 and 2009. I confirmed this by making the change to the database and running the query showing the actual query plan. The solution should show that for both the static and parameteized solutions should show only the 2009 table.
The only way I found to solve the problem is to make the code parameterized and then execute the dynamic version of the code where the parameterized value has been evaluted to a static value. For example:
declare @gd smalldatetime
set @gd = ‘12/01/2009’
declare @sql varchar(4000)
set @sql = 'Select * from v_test where filedate >= ''' + convert(char(10),@gd,101) + ''''
exec (@sql)
However, the above solution is not acceptable.
Thanks
December 8, 2009 at 12:50 pm
When I test these two queries, I get the same execution statistics, logical reads, scan count etc. Those results are with and without forced parameterization set.
In the plan, the query optimizer does not actually query both of the tables, but only lists them and selects the path that is accurate based on the "startup expression predicate."
At execution time SQL Server will dynamically determine which branches of the query plan to short-circuit. This is great as it allows a single query plan to be re-used for any value of your parameter and still deliver the short-circuits benefits.
http://www.fotia.co.uk/fotia/Blog/AllFiredUp...html Shows some experimentation with this.
To optimize it further you could try the MS recommendation (applies to linked servers)
Setting the lazy schema validation option, by using sp_serveroption, for each linked server definition that is used in distributed partitioned views. This optimizes performance by making sure the query processor does not request metadata for any one of the linked tables until data is actually needed from the remote member table.
http://msdn.microsoft.com/en-us/library/ms188299.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply