How to improve the performance,

Hi *.*
I've a problem with my query, perhaps you professionals can kindly
Help me to overcome this, let me explain the problem, We have an
Inventory software with about 8500 of Goods (we stored it in table
Called tblPart), every input/output of these Goods saves in a Table
Called tblInvRate, we have a field called Type, if it's input then
Type=1, if that's output the Type=-1, Qty is the number of input or
Output.
Now with the following query we get the remain of Goods in the
Inventory:
SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
Rate.Type) AS PriorRemain
FROM dbo.tblInvRate Rate INNER JOIN
dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
WHERE Rate.[Date] < '850515'
GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING SUM(Rate.Qty * Rate.Type)>0
This query takes about 1 sec to execute,
In tblInvRate we have a field called IsRated and Rate, Rated and
IsRated sets in a sp named prRatingInv which calculate the rate of the
Goods and...
We need to have the last rate of Goods in the remain query so we use
This:
SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
SUM(Rate.Qty * Rate.Type) AS PriorRemain,
(SELECT TOP 1 Rate
FROM dbo.tblInvRate
WHERE (fkPart = dbo.tblPart.pkCode
AND [Date]<='850515' And IsRated = 1)
ORDER BY [Date] DESC, [Time] DESC) AS
Rate
FROM dbo.tblInvRate Rate INNER JOIN
dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
WHERE Rate.[Date] < '850515'
GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING SUM(Rate.Qty * Rate.Type)>0
Above query may take about 40 to 80 secs to execute!!! (Total number of
rows in the tblInvRate is about 1.000.000 records)
H've run the Index Wizard but it can't find any more index on the
Tables, now would you please help me in this situation,
here is the schema of these tables:
CREATE TABLE [tblPart] (
[pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
NULL ,
[fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
(1),
[Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
[TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
NULL ,
[OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
,
[fkPartAccGroup] [int] NULL ,
CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
(
[pkCode]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblInvRate] (
[pkID] [int] NOT NULL ,
[Source] [int] NOT NULL ,
[Dest] [int] NOT NULL ,
[fkInvHdr] [int] NULL ,
[fkInvItm] [int] NULL ,
[fkChangePart] [int] NULL ,
[fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
NULL ,
[Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
[Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
[Qty] [int] NOT NULL ,
[Rate] [decimal](24, 4) NULL ,
[Amount] [decimal](24, 0) NULL ,
[OldRate] [decimal](24, 4) NULL ,
[MainRate] [decimal](24, 4) NULL ,
[Type] [int] NOT NULL ,
[Code] [int] NOT NULL ,
[IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
DEFAULT (0),
[fkCurrency] [int] NULL ,
[CurrencyRate] [int] NULL ,
[CurrencyAmount] [decimal](24, 0) NULL ,
[fkShop] [int] NULL ,
[fkPerson] [int] NULL ,
[RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
(0),
[IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
(0),
[fkAccVchHdr] [int] NULL ,
[AccTempNum] [int] NULL ,
CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
(
[pkID],
[Source]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
(
[fkChangePart],
[Source]
) REFERENCES [tblChangePart] (
[pkID],
[Source]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
(
[fkInvHdr],
[Source]
) REFERENCES [tblInvHdr] (
[pkID],
[Source]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
Thanks in advance,
Waiting for your tips and advices,
HamedHamed,
Try using the correlated query after the remain of Goods in the Inventory
has been calculated.
select
a.*,
(
SELECT TOP 1
b.Rate
FROM
dbo.tblInvRate as b
WHERE
b.fkPart = a.pkCode
AND b.[Date] <= '850515'
And b.IsRated = 1
) as Rate
from
(
SELECT
dbo.tblPart.pkCode,
dbo.tblPart.Title,
SUM(Rate.Qty * Rate.Type) AS PriorRemain
FROM
dbo.tblInvRate as Rate
INNER JOIN
dbo.tblPart
ON Rate.fkPart = dbo.tblPart.pkCode
WHERE
Rate.[Date] < '850515'
GROUP BY
dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING
SUM(Rate.Qty * Rate.Type)>0
) as a
go
AMB
"Hamed" wrote:
> Hi *.*
> I've a problem with my query, perhaps you professionals can kindly
> Help me to overcome this, let me explain the problem, We have an
> Inventory software with about 8500 of Goods (we stored it in table
> Called tblPart), every input/output of these Goods saves in a Table
> Called tblInvRate, we have a field called Type, if it's input then
> Type=1, if that's output the Type=-1, Qty is the number of input or
> Output.
> Now with the following query we get the remain of Goods in the
> Inventory:
> SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> Rate.Type) AS PriorRemain
> FROM dbo.tblInvRate Rate INNER JOIN
> dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> WHERE Rate.[Date] < '850515'
> GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> HAVING SUM(Rate.Qty * Rate.Type)>0
> This query takes about 1 sec to execute,
> In tblInvRate we have a field called IsRated and Rate, Rated and
> IsRated sets in a sp named prRatingInv which calculate the rate of the
> Goods and...
> We need to have the last rate of Goods in the remain query so we use
> This:
> SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> (SELECT TOP 1 Rate
> FROM dbo.tblInvRate
> WHERE (fkPart = dbo.tblPart.pkCode
> AND [Date]<='850515' And IsRated = 1)
> ORDER BY [Date] DESC, [Time] DESC) AS
> Rate
> FROM dbo.tblInvRate Rate INNER JOIN
> dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> WHERE Rate.[Date] < '850515'
> GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> HAVING SUM(Rate.Qty * Rate.Type)>0
> Above query may take about 40 to 80 secs to execute!!! (Total number of
> rows in the tblInvRate is about 1.000.000 records)
> H've run the Index Wizard but it can't find any more index on the
> Tables, now would you please help me in this situation,
> here is the schema of these tables:
> CREATE TABLE [tblPart] (
> [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> NULL ,
> [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> NULL ,
> [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> (1),
> [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> NULL ,
> [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> ,
> [fkPartAccGroup] [int] NULL ,
> CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> (
> [pkCode]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblInvRate] (
> [pkID] [int] NOT NULL ,
> [Source] [int] NOT NULL ,
> [Dest] [int] NOT NULL ,
> [fkInvHdr] [int] NULL ,
> [fkInvItm] [int] NULL ,
> [fkChangePart] [int] NULL ,
> [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> NULL ,
> [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> [Qty] [int] NOT NULL ,
> [Rate] [decimal](24, 4) NULL ,
> [Amount] [decimal](24, 0) NULL ,
> [OldRate] [decimal](24, 4) NULL ,
> [MainRate] [decimal](24, 4) NULL ,
> [Type] [int] NOT NULL ,
> [Code] [int] NOT NULL ,
> [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> DEFAULT (0),
> [fkCurrency] [int] NULL ,
> [CurrencyRate] [int] NULL ,
> [CurrencyAmount] [decimal](24, 0) NULL ,
> [fkShop] [int] NULL ,
> [fkPerson] [int] NULL ,
> [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> (0),
> [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> (0),
> [fkAccVchHdr] [int] NULL ,
> [AccTempNum] [int] NULL ,
> CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> (
> [pkID],
> [Source]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> (
> [fkChangePart],
> [Source]
> ) REFERENCES [tblChangePart] (
> [pkID],
> [Source]
> ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> (
> [fkInvHdr],
> [Source]
> ) REFERENCES [tblInvHdr] (
> [pkID],
> [Source]
> ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
>
> Thanks in advance,
> Waiting for your tips and advices,
> Hamed
>|||Thanks for your reply, it take 135 secs to execute,
Alejandro Mesa wrote:
> Hamed,
> Try using the correlated query after the remain of Goods in the Inventory
> has been calculated.
> select
> a.*,
> (
> SELECT TOP 1
> b.Rate
> FROM
> dbo.tblInvRate as b
> WHERE
> b.fkPart = a.pkCode
> AND b.[Date] <= '850515'
> And b.IsRated = 1
> ) as Rate
> from
> (
> SELECT
> dbo.tblPart.pkCode,
> dbo.tblPart.Title,
> SUM(Rate.Qty * Rate.Type) AS PriorRemain
> FROM
> dbo.tblInvRate as Rate
> INNER JOIN
> dbo.tblPart
> ON Rate.fkPart = dbo.tblPart.pkCode
> WHERE
> Rate.[Date] < '850515'
> GROUP BY
> dbo.tblPart.pkCode, dbo.tblPart.Title
> HAVING
> SUM(Rate.Qty * Rate.Type)>0
> ) as a
> go
>
> AMB
> "Hamed" wrote:
> > Hi *.*
> >
> > I've a problem with my query, perhaps you professionals can kindly
> > Help me to overcome this, let me explain the problem, We have an
> > Inventory software with about 8500 of Goods (we stored it in table
> > Called tblPart), every input/output of these Goods saves in a Table
> > Called tblInvRate, we have a field called Type, if it's input then
> > Type=1, if that's output the Type=-1, Qty is the number of input or
> > Output.
> >
> > Now with the following query we get the remain of Goods in the
> > Inventory:
> >
> > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > Rate.Type) AS PriorRemain
> > FROM dbo.tblInvRate Rate INNER JOIN
> > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > WHERE Rate.[Date] < '850515'
> > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > HAVING SUM(Rate.Qty * Rate.Type)>0
> >
> > This query takes about 1 sec to execute,
> >
> > In tblInvRate we have a field called IsRated and Rate, Rated and
> > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > Goods and...
> >
> > We need to have the last rate of Goods in the remain query so we use
> > This:
> >
> > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > (SELECT TOP 1 Rate
> > FROM dbo.tblInvRate
> > WHERE (fkPart = dbo.tblPart.pkCode
> > AND [Date]<='850515' And IsRated = 1)
> > ORDER BY [Date] DESC, [Time] DESC) AS
> > Rate
> > FROM dbo.tblInvRate Rate INNER JOIN
> > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > WHERE Rate.[Date] < '850515'
> > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > HAVING SUM(Rate.Qty * Rate.Type)>0
> >
> > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > rows in the tblInvRate is about 1.000.000 records)
> >
> > H've run the Index Wizard but it can't find any more index on the
> > Tables, now would you please help me in this situation,
> >
> > here is the schema of these tables:
> >
> > CREATE TABLE [tblPart] (
> > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > NULL ,
> > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > NULL ,
> > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > (1),
> > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > NULL ,
> > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > ,
> > [fkPartAccGroup] [int] NULL ,
> > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > (
> > [pkCode]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> >
> > CREATE TABLE [tblInvRate] (
> > [pkID] [int] NOT NULL ,
> > [Source] [int] NOT NULL ,
> > [Dest] [int] NOT NULL ,
> > [fkInvHdr] [int] NULL ,
> > [fkInvItm] [int] NULL ,
> > [fkChangePart] [int] NULL ,
> > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > NULL ,
> > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > [Qty] [int] NOT NULL ,
> > [Rate] [decimal](24, 4) NULL ,
> > [Amount] [decimal](24, 0) NULL ,
> > [OldRate] [decimal](24, 4) NULL ,
> > [MainRate] [decimal](24, 4) NULL ,
> > [Type] [int] NOT NULL ,
> > [Code] [int] NOT NULL ,
> > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > DEFAULT (0),
> > [fkCurrency] [int] NULL ,
> > [CurrencyRate] [int] NULL ,
> > [CurrencyAmount] [decimal](24, 0) NULL ,
> > [fkShop] [int] NULL ,
> > [fkPerson] [int] NULL ,
> > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > (0),
> > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > (0),
> > [fkAccVchHdr] [int] NULL ,
> > [AccTempNum] [int] NULL ,
> > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > (
> > [pkID],
> > [Source]
> > ) ON [PRIMARY] ,
> > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > (
> > [fkChangePart],
> > [Source]
> > ) REFERENCES [tblChangePart] (
> > [pkID],
> > [Source]
> > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > (
> > [fkInvHdr],
> > [Source]
> > ) REFERENCES [tblInvHdr] (
> > [pkID],
> > [Source]
> > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > ) ON [PRIMARY]
> > GO
> >
> >
> > Thanks in advance,
> > Waiting for your tips and advices,
> > Hamed
> >
> >|||Hamed,
can you post the execution plan?
AMB
"Hamed" wrote:
> Thanks for your reply, it take 135 secs to execute,
> Alejandro Mesa wrote:
> > Hamed,
> >
> > Try using the correlated query after the remain of Goods in the Inventory
> > has been calculated.
> >
> > select
> > a.*,
> > (
> > SELECT TOP 1
> > b.Rate
> > FROM
> > dbo.tblInvRate as b
> > WHERE
> > b.fkPart = a.pkCode
> > AND b.[Date] <= '850515'
> > And b.IsRated = 1
> > ) as Rate
> > from
> > (
> > SELECT
> > dbo.tblPart.pkCode,
> > dbo.tblPart.Title,
> > SUM(Rate.Qty * Rate.Type) AS PriorRemain
> > FROM
> > dbo.tblInvRate as Rate
> > INNER JOIN
> > dbo.tblPart
> > ON Rate.fkPart = dbo.tblPart.pkCode
> > WHERE
> > Rate.[Date] < '850515'
> > GROUP BY
> > dbo.tblPart.pkCode, dbo.tblPart.Title
> > HAVING
> > SUM(Rate.Qty * Rate.Type)>0
> > ) as a
> > go
> >
> >
> > AMB
> >
> > "Hamed" wrote:
> >
> > > Hi *.*
> > >
> > > I've a problem with my query, perhaps you professionals can kindly
> > > Help me to overcome this, let me explain the problem, We have an
> > > Inventory software with about 8500 of Goods (we stored it in table
> > > Called tblPart), every input/output of these Goods saves in a Table
> > > Called tblInvRate, we have a field called Type, if it's input then
> > > Type=1, if that's output the Type=-1, Qty is the number of input or
> > > Output.
> > >
> > > Now with the following query we get the remain of Goods in the
> > > Inventory:
> > >
> > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > > Rate.Type) AS PriorRemain
> > > FROM dbo.tblInvRate Rate INNER JOIN
> > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > WHERE Rate.[Date] < '850515'
> > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > >
> > > This query takes about 1 sec to execute,
> > >
> > > In tblInvRate we have a field called IsRated and Rate, Rated and
> > > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > > Goods and...
> > >
> > > We need to have the last rate of Goods in the remain query so we use
> > > This:
> > >
> > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > > (SELECT TOP 1 Rate
> > > FROM dbo.tblInvRate
> > > WHERE (fkPart = dbo.tblPart.pkCode
> > > AND [Date]<='850515' And IsRated = 1)
> > > ORDER BY [Date] DESC, [Time] DESC) AS
> > > Rate
> > > FROM dbo.tblInvRate Rate INNER JOIN
> > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > WHERE Rate.[Date] < '850515'
> > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > >
> > > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > > rows in the tblInvRate is about 1.000.000 records)
> > >
> > > H've run the Index Wizard but it can't find any more index on the
> > > Tables, now would you please help me in this situation,
> > >
> > > here is the schema of these tables:
> > >
> > > CREATE TABLE [tblPart] (
> > > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > NULL ,
> > > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > NULL ,
> > > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > > (1),
> > > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > > NULL ,
> > > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > > ,
> > > [fkPartAccGroup] [int] NULL ,
> > > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > > (
> > > [pkCode]
> > > ) ON [PRIMARY]
> > > ) ON [PRIMARY]
> > > GO
> > >
> > >
> > > CREATE TABLE [tblInvRate] (
> > > [pkID] [int] NOT NULL ,
> > > [Source] [int] NOT NULL ,
> > > [Dest] [int] NOT NULL ,
> > > [fkInvHdr] [int] NULL ,
> > > [fkInvItm] [int] NULL ,
> > > [fkChangePart] [int] NULL ,
> > > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > NULL ,
> > > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > [Qty] [int] NOT NULL ,
> > > [Rate] [decimal](24, 4) NULL ,
> > > [Amount] [decimal](24, 0) NULL ,
> > > [OldRate] [decimal](24, 4) NULL ,
> > > [MainRate] [decimal](24, 4) NULL ,
> > > [Type] [int] NOT NULL ,
> > > [Code] [int] NOT NULL ,
> > > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > > DEFAULT (0),
> > > [fkCurrency] [int] NULL ,
> > > [CurrencyRate] [int] NULL ,
> > > [CurrencyAmount] [decimal](24, 0) NULL ,
> > > [fkShop] [int] NULL ,
> > > [fkPerson] [int] NULL ,
> > > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > > (0),
> > > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > > (0),
> > > [fkAccVchHdr] [int] NULL ,
> > > [AccTempNum] [int] NULL ,
> > > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > > (
> > > [pkID],
> > > [Source]
> > > ) ON [PRIMARY] ,
> > > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > > (
> > > [fkChangePart],
> > > [Source]
> > > ) REFERENCES [tblChangePart] (
> > > [pkID],
> > > [Source]
> > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > > (
> > > [fkInvHdr],
> > > [Source]
> > > ) REFERENCES [tblInvHdr] (
> > > [pkID],
> > > [Source]
> > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > > ) ON [PRIMARY]
> > > GO
> > >
> > >
> > > Thanks in advance,
> > > Waiting for your tips and advices,
> > > Hamed
> > >
> > >
>|||I don't know how to post the execution plan to this group,
Alejandro Mesa wrote:
> Hamed,
> can you post the execution plan?
>
> AMB
> "Hamed" wrote:
> > Thanks for your reply, it take 135 secs to execute,
> >
> > Alejandro Mesa wrote:
> > > Hamed,
> > >
> > > Try using the correlated query after the remain of Goods in the Inventory
> > > has been calculated.
> > >
> > > select
> > > a.*,
> > > (
> > > SELECT TOP 1
> > > b.Rate
> > > FROM
> > > dbo.tblInvRate as b
> > > WHERE
> > > b.fkPart = a.pkCode
> > > AND b.[Date] <= '850515'
> > > And b.IsRated = 1
> > > ) as Rate
> > > from
> > > (
> > > SELECT
> > > dbo.tblPart.pkCode,
> > > dbo.tblPart.Title,
> > > SUM(Rate.Qty * Rate.Type) AS PriorRemain
> > > FROM
> > > dbo.tblInvRate as Rate
> > > INNER JOIN
> > > dbo.tblPart
> > > ON Rate.fkPart = dbo.tblPart.pkCode
> > > WHERE
> > > Rate.[Date] < '850515'
> > > GROUP BY
> > > dbo.tblPart.pkCode, dbo.tblPart.Title
> > > HAVING
> > > SUM(Rate.Qty * Rate.Type)>0
> > > ) as a
> > > go
> > >
> > >
> > > AMB
> > >
> > > "Hamed" wrote:
> > >
> > > > Hi *.*
> > > >
> > > > I've a problem with my query, perhaps you professionals can kindly
> > > > Help me to overcome this, let me explain the problem, We have an
> > > > Inventory software with about 8500 of Goods (we stored it in table
> > > > Called tblPart), every input/output of these Goods saves in a Table
> > > > Called tblInvRate, we have a field called Type, if it's input then
> > > > Type=1, if that's output the Type=-1, Qty is the number of input or
> > > > Output.
> > > >
> > > > Now with the following query we get the remain of Goods in the
> > > > Inventory:
> > > >
> > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > > > Rate.Type) AS PriorRemain
> > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > WHERE Rate.[Date] < '850515'
> > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > >
> > > > This query takes about 1 sec to execute,
> > > >
> > > > In tblInvRate we have a field called IsRated and Rate, Rated and
> > > > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > > > Goods and...
> > > >
> > > > We need to have the last rate of Goods in the remain query so we use
> > > > This:
> > > >
> > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > > > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > > > (SELECT TOP 1 Rate
> > > > FROM dbo.tblInvRate
> > > > WHERE (fkPart = dbo.tblPart.pkCode
> > > > AND [Date]<='850515' And IsRated = 1)
> > > > ORDER BY [Date] DESC, [Time] DESC) AS
> > > > Rate
> > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > WHERE Rate.[Date] < '850515'
> > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > >
> > > > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > > > rows in the tblInvRate is about 1.000.000 records)
> > > >
> > > > H've run the Index Wizard but it can't find any more index on the
> > > > Tables, now would you please help me in this situation,
> > > >
> > > > here is the schema of these tables:
> > > >
> > > > CREATE TABLE [tblPart] (
> > > > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > NULL ,
> > > > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > NULL ,
> > > > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > > > (1),
> > > > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > > > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > > > NULL ,
> > > > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > > > ,
> > > > [fkPartAccGroup] [int] NULL ,
> > > > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > > > (
> > > > [pkCode]
> > > > ) ON [PRIMARY]
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > >
> > > > CREATE TABLE [tblInvRate] (
> > > > [pkID] [int] NOT NULL ,
> > > > [Source] [int] NOT NULL ,
> > > > [Dest] [int] NOT NULL ,
> > > > [fkInvHdr] [int] NULL ,
> > > > [fkInvItm] [int] NULL ,
> > > > [fkChangePart] [int] NULL ,
> > > > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > NULL ,
> > > > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > [Qty] [int] NOT NULL ,
> > > > [Rate] [decimal](24, 4) NULL ,
> > > > [Amount] [decimal](24, 0) NULL ,
> > > > [OldRate] [decimal](24, 4) NULL ,
> > > > [MainRate] [decimal](24, 4) NULL ,
> > > > [Type] [int] NOT NULL ,
> > > > [Code] [int] NOT NULL ,
> > > > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > > > DEFAULT (0),
> > > > [fkCurrency] [int] NULL ,
> > > > [CurrencyRate] [int] NULL ,
> > > > [CurrencyAmount] [decimal](24, 0) NULL ,
> > > > [fkShop] [int] NULL ,
> > > > [fkPerson] [int] NULL ,
> > > > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > > > (0),
> > > > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > > > (0),
> > > > [fkAccVchHdr] [int] NULL ,
> > > > [AccTempNum] [int] NULL ,
> > > > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > > > (
> > > > [pkID],
> > > > [Source]
> > > > ) ON [PRIMARY] ,
> > > > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > > > (
> > > > [fkChangePart],
> > > > [Source]
> > > > ) REFERENCES [tblChangePart] (
> > > > [pkID],
> > > > [Source]
> > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > > > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > > > (
> > > > [fkInvHdr],
> > > > [Source]
> > > > ) REFERENCES [tblInvHdr] (
> > > > [pkID],
> > > > [Source]
> > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > >
> > > > Thanks in advance,
> > > > Waiting for your tips and advices,
> > > > Hamed
> > > >
> > > >
> >
> >|||Hamed,
Use "set showplan_text on" to get the execution plan as text.
set showplan_text on
go
select ...
go
set showplan_text off
go
AMB
"Hamed" wrote:
> I don't know how to post the execution plan to this group,
> Alejandro Mesa wrote:
> > Hamed,
> >
> > can you post the execution plan?
> >
> >
> > AMB
> >
> > "Hamed" wrote:
> >
> > > Thanks for your reply, it take 135 secs to execute,
> > >
> > > Alejandro Mesa wrote:
> > > > Hamed,
> > > >
> > > > Try using the correlated query after the remain of Goods in the Inventory
> > > > has been calculated.
> > > >
> > > > select
> > > > a.*,
> > > > (
> > > > SELECT TOP 1
> > > > b.Rate
> > > > FROM
> > > > dbo.tblInvRate as b
> > > > WHERE
> > > > b.fkPart = a.pkCode
> > > > AND b.[Date] <= '850515'
> > > > And b.IsRated = 1
> > > > ) as Rate
> > > > from
> > > > (
> > > > SELECT
> > > > dbo.tblPart.pkCode,
> > > > dbo.tblPart.Title,
> > > > SUM(Rate.Qty * Rate.Type) AS PriorRemain
> > > > FROM
> > > > dbo.tblInvRate as Rate
> > > > INNER JOIN
> > > > dbo.tblPart
> > > > ON Rate.fkPart = dbo.tblPart.pkCode
> > > > WHERE
> > > > Rate.[Date] < '850515'
> > > > GROUP BY
> > > > dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > HAVING
> > > > SUM(Rate.Qty * Rate.Type)>0
> > > > ) as a
> > > > go
> > > >
> > > >
> > > > AMB
> > > >
> > > > "Hamed" wrote:
> > > >
> > > > > Hi *.*
> > > > >
> > > > > I've a problem with my query, perhaps you professionals can kindly
> > > > > Help me to overcome this, let me explain the problem, We have an
> > > > > Inventory software with about 8500 of Goods (we stored it in table
> > > > > Called tblPart), every input/output of these Goods saves in a Table
> > > > > Called tblInvRate, we have a field called Type, if it's input then
> > > > > Type=1, if that's output the Type=-1, Qty is the number of input or
> > > > > Output.
> > > > >
> > > > > Now with the following query we get the remain of Goods in the
> > > > > Inventory:
> > > > >
> > > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,SUM(Rate.Qty *
> > > > > Rate.Type) AS PriorRemain
> > > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > > WHERE Rate.[Date] < '850515'
> > > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > > >
> > > > > This query takes about 1 sec to execute,
> > > > >
> > > > > In tblInvRate we have a field called IsRated and Rate, Rated and
> > > > > IsRated sets in a sp named prRatingInv which calculate the rate of the
> > > > > Goods and...
> > > > >
> > > > > We need to have the last rate of Goods in the remain query so we use
> > > > > This:
> > > > >
> > > > > SELECT dbo.tblPart.pkCode, dbo.tblPart.Title,
> > > > > SUM(Rate.Qty * Rate.Type) AS PriorRemain,
> > > > > (SELECT TOP 1 Rate
> > > > > FROM dbo.tblInvRate
> > > > > WHERE (fkPart = dbo.tblPart.pkCode
> > > > > AND [Date]<='850515' And IsRated = 1)
> > > > > ORDER BY [Date] DESC, [Time] DESC) AS
> > > > > Rate
> > > > > FROM dbo.tblInvRate Rate INNER JOIN
> > > > > dbo.tblPart ON Rate.fkPart = dbo.tblPart.pkCode
> > > > > WHERE Rate.[Date] < '850515'
> > > > > GROUP BY dbo.tblPart.pkCode, dbo.tblPart.Title
> > > > > HAVING SUM(Rate.Qty * Rate.Type)>0
> > > > >
> > > > > Above query may take about 40 to 80 secs to execute!!! (Total number of
> > > > > rows in the tblInvRate is about 1.000.000 records)
> > > > >
> > > > > H've run the Index Wizard but it can't find any more index on the
> > > > > Tables, now would you please help me in this situation,
> > > > >
> > > > > here is the schema of these tables:
> > > > >
> > > > > CREATE TABLE [tblPart] (
> > > > > [pkCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > > NULL ,
> > > > > [Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > > NULL ,
> > > > > [fkPartUnit] [int] NOT NULL CONSTRAINT [DF_tblPart_fkPartUnit] DEFAULT
> > > > > (1),
> > > > > [Descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
> > > > > [TitleLatin] [varchar] (255) COLLATE SQL_Latin1_General_CP1256_CI_AS
> > > > > NULL ,
> > > > > [OldCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL
> > > > > ,
> > > > > [fkPartAccGroup] [int] NULL ,
> > > > > CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
> > > > > (
> > > > > [pkCode]
> > > > > ) ON [PRIMARY]
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > >
> > > > > CREATE TABLE [tblInvRate] (
> > > > > [pkID] [int] NOT NULL ,
> > > > > [Source] [int] NOT NULL ,
> > > > > [Dest] [int] NOT NULL ,
> > > > > [fkInvHdr] [int] NULL ,
> > > > > [fkInvItm] [int] NULL ,
> > > > > [fkChangePart] [int] NULL ,
> > > > > [fkPart] [varchar] (20) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT
> > > > > NULL ,
> > > > > [Date] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > > [Time] [char] (6) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL ,
> > > > > [Qty] [int] NOT NULL ,
> > > > > [Rate] [decimal](24, 4) NULL ,
> > > > > [Amount] [decimal](24, 0) NULL ,
> > > > > [OldRate] [decimal](24, 4) NULL ,
> > > > > [MainRate] [decimal](24, 4) NULL ,
> > > > > [Type] [int] NOT NULL ,
> > > > > [Code] [int] NOT NULL ,
> > > > > [IsAutomatic] [bit] NULL CONSTRAINT [DF_tblInvRate_IsAutomatic]
> > > > > DEFAULT (0),
> > > > > [fkCurrency] [int] NULL ,
> > > > > [CurrencyRate] [int] NULL ,
> > > > > [CurrencyAmount] [decimal](24, 0) NULL ,
> > > > > [fkShop] [int] NULL ,
> > > > > [fkPerson] [int] NULL ,
> > > > > [RateKind] [int] NOT NULL CONSTRAINT [DF_tblInvRate_RateKind] DEFAULT
> > > > > (0),
> > > > > [IsRated] [bit] NOT NULL CONSTRAINT [DF_tblInvRate_IsRated] DEFAULT
> > > > > (0),
> > > > > [fkAccVchHdr] [int] NULL ,
> > > > > [AccTempNum] [int] NULL ,
> > > > > CONSTRAINT [PK_tblInvRate] PRIMARY KEY CLUSTERED
> > > > > (
> > > > > [pkID],
> > > > > [Source]
> > > > > ) ON [PRIMARY] ,
> > > > > CONSTRAINT [FK_tblInvRate_tblChangePart] FOREIGN KEY
> > > > > (
> > > > > [fkChangePart],
> > > > > [Source]
> > > > > ) REFERENCES [tblChangePart] (
> > > > > [pkID],
> > > > > [Source]
> > > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
> > > > > CONSTRAINT [FK_tblInvRate_tblInvHdr] FOREIGN KEY
> > > > > (
> > > > > [fkInvHdr],
> > > > > [Source]
> > > > > ) REFERENCES [tblInvHdr] (
> > > > > [pkID],
> > > > > [Source]
> > > > > ) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > >
> > > > > Thanks in advance,
> > > > > Waiting for your tips and advices,
> > > > > Hamed
> > > > >
> > > > >
> > >
> > >
>|||This is yours:
|--Compute Scalar(DEFINE:([b].[Rate]=[b].[Rate]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([tblPart].[pkCode]))
|--Filter(WHERE:([Expr1003]>0))
| |--Merge Join(Inner Join,
MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
| |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
| | |--Index
Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
| |--Index
Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
|--Top(1)
|--Filter(WHERE:(Convert([b].[IsRated])=1))
|--Bookmark Lookup(BOOKMARK:([Bmk1004]),
OBJECT:([PosKish].[dbo].[tblInvRate] AS [b]) WITH PREFETCH)
|--Index
Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [b]),
SEEK:([b].[fkPart]=[tblPart].[pkCode] AND [b].[Date] <= '850515')
ORDERED FORWARD)
and this is mine:
|--Compute Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([tblPart].[pkCode]))
|--Filter(WHERE:([Expr1003]>0))
| |--Merge Join(Inner Join,
MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
| |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
| | |--Index
Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
| |--Index
Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
|--Compute
Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
|--Sort(TOP 1, ORDER BY:([tblInvRate].[Date] DESC,
[tblInvRate].[Time] DESC))
|--Filter(WHERE:(Convert([tblInvRate].[IsRated])=1))
|--Bookmark Lookup(BOOKMARK:([Bmk1004]),
OBJECT:([PosKish].[dbo].[tblInvRate]) WITH PREFETCH)
|--Index
Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11]),
SEEK:([tblInvRate].[fkPart]=[tblPart].[pkCode] AND [tblInvRate].[Date]
<= '850515') ORDERED FORWARD)|||Hamed,
Please, can you post indexes for each table also?
Seems that column [dbo].[tblInvRate].[IsRated] is not integer, correct?.
Ttry casting the value you compare with. Let us assume it is bit data type,
then:
|--Filter(WHERE:(Convert([b].[IsRated])=1))
(
SELECT TOP 1
Rate
FROM
dbo.tblInvRate
WHERE
fkPart = dbo.tblPart.pkCode
AND [Date]<='850515'
And IsRated = cast(1 as bit) <-- change this
)
AMB
"Hamed" wrote:
> This is yours:
> |--Compute Scalar(DEFINE:([b].[Rate]=[b].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Top(1)
> |--Filter(WHERE:(Convert([b].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate] AS [b]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [b]),
> SEEK:([b].[fkPart]=[tblPart].[pkCode] AND [b].[Date] <= '850515')
> ORDERED FORWARD)
> and this is mine:
> |--Compute Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Compute
> Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Sort(TOP 1, ORDER BY:([tblInvRate].[Date] DESC,
> [tblInvRate].[Time] DESC))
> |--Filter(WHERE:(Convert([tblInvRate].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11]),
> SEEK:([tblInvRate].[fkPart]=[tblPart].[pkCode] AND [tblInvRate].[Date]
> <= '850515') ORDERED FORWARD)
>|||Hamed,
In my first post I forgot the "order by" clause in the correlated subquery.
I think that is the reason why we are getting diffrent execution plans.
select
a.*,
(
SELECT TOP 1
b.Rate
FROM
dbo.tblInvRate as b
WHERE
b.fkPart = a.pkCode
AND b.[Date] <= '850515'
And b.IsRated = 1
ORDER BY [Date] DESC, [Time] DESC <-- forgot this
) as Rate
from
(
SELECT
dbo.tblPart.pkCode,
dbo.tblPart.Title,
SUM(Rate.Qty * Rate.Type) AS PriorRemain
FROM
dbo.tblInvRate as Rate
INNER JOIN
dbo.tblPart
ON Rate.fkPart = dbo.tblPart.pkCode
WHERE
Rate.[Date] < '850515'
GROUP BY
dbo.tblPart.pkCode, dbo.tblPart.Title
HAVING
SUM(Rate.Qty * Rate.Type)>0
) as a
go
AMB
"Hamed" wrote:
> This is yours:
> |--Compute Scalar(DEFINE:([b].[Rate]=[b].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Top(1)
> |--Filter(WHERE:(Convert([b].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate] AS [b]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [b]),
> SEEK:([b].[fkPart]=[tblPart].[pkCode] AND [b].[Date] <= '850515')
> ORDERED FORWARD)
> and this is mine:
> |--Compute Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Nested Loops(Left Outer Join, OUTER
> REFERENCES:([tblPart].[pkCode]))
> |--Filter(WHERE:([Expr1003]>0))
> | |--Merge Join(Inner Join,
> MERGE:([Rate].[fkPart])=([tblPart].[pkCode]),
> RESIDUAL:([tblPart].[pkCode]=[Rate].[fkPart]))
> | |--Stream Aggregate(GROUP BY:([Rate].[fkPart])
> DEFINE:([Expr1003]=SUM([Rate].[Qty]*[Rate].[Type])))
> | | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11] AS [Rate]),
> WHERE:([Rate].[Date]<'850515') ORDERED FORWARD)
> | |--Index
> Scan(OBJECT:([PosKish].[dbo].[tblPart].[tblPart16]), ORDERED FORWARD)
> |--Compute
> Scalar(DEFINE:([tblInvRate].[Rate]=[tblInvRate].[Rate]))
> |--Sort(TOP 1, ORDER BY:([tblInvRate].[Date] DESC,
> [tblInvRate].[Time] DESC))
> |--Filter(WHERE:(Convert([tblInvRate].[IsRated])=1))
> |--Bookmark Lookup(BOOKMARK:([Bmk1004]),
> OBJECT:([PosKish].[dbo].[tblInvRate]) WITH PREFETCH)
> |--Index
> Seek(OBJECT:([PosKish].[dbo].[tblInvRate].[tblInvRate11]),
> SEEK:([tblInvRate].[fkPart]=[tblPart].[pkCode] AND [tblInvRate].[Date]
> <= '850515') ORDERED FORWARD)
>