Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
VenkatHave you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegroups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat