Need query optimization help.

(SQL Server 2000, SP4)
Hello all!
I'm seeing a lot of I/O generated by one of our queries, and I think it's
how the query is constructed. Consider the following:
use [tempdb]
go
if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[
;Test]
create table [dbo].[Test]
(
[RowID] uniqueidentifier not NULL,
[Field1] int not NULL,
[Field2] int not NULL,
[Date] datetime not NULL,
primary key clustered ([RowID])
)
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor d
elay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor d
elay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor d
elay
'0:0:1'
insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor d
elay
'0:0:1'
insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor d
elay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor d
elay
'0:0:1'
insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor d
elay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor d
elay
'0:0:1'
select t.*
from [dbo].[Test] as t with (nolock)
select t.*
from [dbo].[Test] as t with (nolock)
inner join (
select [Field1] = t.[Field1],
[Field2] = t.[Field2],
[Date] = max(t.[Date])
from [dbo].[Test] as t with (nolock)
group by t.[Field1],
t.[Field2]
) as q on q.[Field1] = t.[Field1]
and q.[Field2] = t.[Field2]
and q.[Date] = t.[Date]
In essence, I'm trying to find the rows from Test that have the *maximum*
Date for a unique Field1/Field2 combination. This query is used in a VIEW,
and users of this VIEW typically JOIN on RowID.
I'm sure the "guts" of this VIEW can be refactored to be better, but I don't
know how to do it. Any suggestions would be *much* appreciated!
John PetersonWhat about (didnt try that but it should work)
Select * From Test
Inner Join
(
Select Field1,Field2,Max(Date)
From Test
Group by Field1,Field2
) SUbQuery
on
SUbQuery.Field1 = Test.FIeld1 AND
SUbQuery.Field2 = Test.FIeld2 AND
SUbQuery.Date = Test.Date
Its alsways interesting for performance issues to send the execution plan
with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
command and youll get the query execution plan in text displayes, which you
can copy & paste in the newsgroups.
HTH, Jens Suessmeyer.
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP4)
> Hello all!
> I'm seeing a lot of I/O generated by one of our queries, and I think it's
> how the query is constructed. Consider the following:
>
> use [tempdb]
> go
> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].&#
91;Test]
> create table [dbo].[Test]
> (
> [RowID] uniqueidentifier not NULL,
> [Field1] int not NULL,
> [Field2] int not NULL,
> [Date] datetime not NULL,
> primary key clustered ([RowID])
> )
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
delay
> '0:0:1'
> select t.*
> from [dbo].[Test] as t with (nolock)
> select t.*
> from [dbo].[Test] as t with (nolock)
> inner join (
> select [Field1] = t.[Field1],
> [Field2] = t.[Field2],
> [Date] = max(t.[Date])
> from [dbo].[Test] as t with (nolock)
> group by t.[Field1],
> t.[Field2]
> ) as q on q.[Field1] = t.[Field1]
> and q.[Field2] = t.[Field2]
> and q.[Date] = t.[Date]
>
> In essence, I'm trying to find the rows from Test that have the *maximum*
> Date for a unique Field1/Field2 combination. This query is used in a
> VIEW, and users of this VIEW typically JOIN on RowID.
> I'm sure the "guts" of this VIEW can be refactored to be better, but I
> don't know how to do it. Any suggestions would be *much* appreciated!
> John Peterson
>|||Hello Jens!
I think your suggestion is exactly what I came up with (see original
post) -- I was hoping maybe there was an even better way to express this.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
> What about (didnt try that but it should work)
> Select * From Test
> Inner Join
> (
> Select Field1,Field2,Max(Date)
> From Test
> Group by Field1,Field2
> ) SUbQuery
> on
> SUbQuery.Field1 = Test.FIeld1 AND
> SUbQuery.Field2 = Test.FIeld2 AND
> SUbQuery.Date = Test.Date
> Its alsways interesting for performance issues to send the execution plan
> with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
> command and youll get the query execution plan in text displayes, which
> you can copy & paste in the newsgroups.
> HTH, Jens Suessmeyer.
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
>|||John,
There is another way to do this, but I can not asure that it will give
better performance. you have to compare both execution plan.
select
t1.*
from
[dbo].[Test] as t1 with (nolock)
where
t1.[Date] = (
select
max(t2.[Date])
from
[dbo].[Test] as t2 with (nolock)
where
t2.[Field1] = t1.[Field1]
and t2.[Field2] = t1.[Field2]
)
AMB
"John Peterson" wrote:

> Hello Jens!
> I think your suggestion is exactly what I came up with (see original
> post) -- I was hoping maybe there was an even better way to express this.
>
> "Jens Sü?meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in
> message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
>
>|||Sorry, but thats not exact the same, because you additionaly did a
correlated query
within your subselect and additionaly did and a join outside the query.
Bit the best thing to see the differences is to get the query plan for that
options.
HTH, Jens Suessmeyer.
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:uHWKtFPeFHA.1288@.tk2msftngp13.phx.gbl...
> Hello Jens!
> I think your suggestion is exactly what I came up with (see original
> post) -- I was hoping maybe there was an even better way to express this.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
>|||Thanks Alejandro! I'll compare/contrast this technique. Typically, a
subselect within a field has performed more slowly for me -- but not every
case is the same. I'll do as you suggest and examine the Excecution Plans.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EA463D89-3CD7-474A-8DAD-6638C9B4066D@.microsoft.com...[vbcol=seagreen]
> John,
> There is another way to do this, but I can not asure that it will give
> better performance. you have to compare both execution plan.
> select
> t1.*
> from
> [dbo].[Test] as t1 with (nolock)
> where
> t1.[Date] = (
> select
> max(t2.[Date])
> from
> [dbo].[Test] as t2 with (nolock)
> where
> t2.[Field1] = t1.[Field1]
> and t2.[Field2] = t1.[Field2]
> )
>
> AMB
>
>
> "John Peterson" wrote:
>|||I don't mean to be obtuse, but I must be missing something. Your query and
my original query appear to be identical.
Here's what I had originally crafted:
select t.*
from [dbo].[Test] as t with (nolock)
inner join (
select [Field1] = t.[Field1],
[Field2] = t.[Field2],
[Date] = max(t.[Date])
from [dbo].[Test] as t with (nolock)
group by t.[Field1],
t.[Field2]
) as q on q.[Field1] = t.[Field1]
and q.[Field2] = t.[Field2]
and q.[Date] = t.[Date]
And here's what you suggested:
Select * From Test
Inner Join
(
Select Field1,Field2,Max(Date)
From Test
Group by Field1,Field2
) SUbQuery
on
SUbQuery.Field1 = Test.FIeld1 AND
SUbQuery.Field2 = Test.FIeld2 AND
SUbQuery.Date = Test.Date
Formatting aside, those seem *identical* to me. Or is there some subtle
difference that I don't perceive that might influence the performance?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238a5LuPeFHA.2984@.TK2MSFTNGP15.phx.gbl...
> Sorry, but thats not exact the same, because you additionaly did a
> correlated query
> within your subselect and additionaly did and a join outside the query.
> Bit the best thing to see the differences is to get the query plan for
> that options.
> HTH, Jens Suessmeyer.
>
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:uHWKtFPeFHA.1288@.tk2msftngp13.phx.gbl...
>|||Hi John,
The query you posted should perform just fine, provided you add the
proper indexes. It would definitely be desirable to have an index on
Test(Field1,Field2) or even on Test(Field1,Field2,"Date").
HTH,
Gert-Jan
John Peterson wrote:
> (SQL Server 2000, SP4)
> Hello all!
> I'm seeing a lot of I/O generated by one of our queries, and I think it's
> how the query is constructed. Consider the following:
> use [tempdb]
> go
> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].&#
91;Test]
> create table [dbo].[Test]
> (
> [RowID] uniqueidentifier not NULL,
> [Field1] int not NULL,
> [Field2] int not NULL,
> [Date] datetime not NULL,
> primary key clustered ([RowID])
> )
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
delay
> '0:0:1'
> select t.*
> from [dbo].[Test] as t with (nolock)
> select t.*
> from [dbo].[Test] as t with (nolock)
> inner join (
> select [Field1] = t.[Field1],
> [Field2] = t.[Field2],
> [Date] = max(t.[Date])
> from [dbo].[Test] as t with (nolock)
> group by t.[Field1],
> t.[Field2]
> ) as q on q.[Field1] = t.[Field1]
> and q.[Field2] = t.[Field2]
> and q.[Date] = t.[Date]
> In essence, I'm trying to find the rows from Test that have the *maximum*
> Date for a unique Field1/Field2 combination. This query is used in a VIEW
,
> and users of this VIEW typically JOIN on RowID.
> I'm sure the "guts" of this VIEW can be refactored to be better, but I don
't
> know how to do it. Any suggestions would be *much* appreciated!
> John Peterson

Need query optimization help.

(SQL Server 2000, SP4)
Hello all!
I'm seeing a lot of I/O generated by one of our queries, and I think it's
how the query is constructed. Consider the following:
use [tempdb]
go
if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
create table [dbo].[Test]
(
[RowID] uniqueidentifier not NULL,
[Field1] int not NULL,
[Field2] int not NULL,
[Date] datetime not NULL,
primary key clustered ([RowID])
)
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
'0:0:1'
select t.*
from [dbo].[Test] as t with (nolock)
select t.*
from [dbo].[Test] as t with (nolock)
inner join (
select [Field1] = t.[Field1],
[Field2] = t.[Field2],
[Date] = max(t.[Date])
from [dbo].[Test] as t with (nolock)
group by t.[Field1],
t.[Field2]
) as q on q.[Field1] = t.[Field1]
and q.[Field2] = t.[Field2]
and q.[Date] = t.[Date]
In essence, I'm trying to find the rows from Test that have the *maximum*
Date for a unique Field1/Field2 combination. This query is used in a VIEW,
and users of this VIEW typically JOIN on RowID.
I'm sure the "guts" of this VIEW can be refactored to be better, but I don't
know how to do it. Any suggestions would be *much* appreciated!
John Peterson
What about (didnt try that but it should work)
Select * From Test
Inner Join
(
Select Field1,Field2,Max(Date)
From Test
Group by Field1,Field2
) SUbQuery
on
SUbQuery.Field1 = Test.FIeld1 AND
SUbQuery.Field2 = Test.FIeld2 AND
SUbQuery.Date = Test.Date
Its alsways interesting for performance issues to send the execution plan
with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
command and youll get the query execution plan in text displayes, which you
can copy & paste in the newsgroups.
HTH, Jens Suessmeyer.
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP4)
> Hello all!
> I'm seeing a lot of I/O generated by one of our queries, and I think it's
> how the query is constructed. Consider the following:
>
> use [tempdb]
> go
> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
> create table [dbo].[Test]
> (
> [RowID] uniqueidentifier not NULL,
> [Field1] int not NULL,
> [Field2] int not NULL,
> [Date] datetime not NULL,
> primary key clustered ([RowID])
> )
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> select t.*
> from [dbo].[Test] as t with (nolock)
> select t.*
> from [dbo].[Test] as t with (nolock)
> inner join (
> select [Field1] = t.[Field1],
> [Field2] = t.[Field2],
> [Date] = max(t.[Date])
> from [dbo].[Test] as t with (nolock)
> group by t.[Field1],
> t.[Field2]
> ) as q on q.[Field1] = t.[Field1]
> and q.[Field2] = t.[Field2]
> and q.[Date] = t.[Date]
>
> In essence, I'm trying to find the rows from Test that have the *maximum*
> Date for a unique Field1/Field2 combination. This query is used in a
> VIEW, and users of this VIEW typically JOIN on RowID.
> I'm sure the "guts" of this VIEW can be refactored to be better, but I
> don't know how to do it. Any suggestions would be *much* appreciated!
> John Peterson
>
|||Hello Jens!
I think your suggestion is exactly what I came up with (see original
post) -- I was hoping maybe there was an even better way to express this.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
> What about (didnt try that but it should work)
> Select * From Test
> Inner Join
> (
> Select Field1,Field2,Max(Date)
> From Test
> Group by Field1,Field2
> ) SUbQuery
> on
> SUbQuery.Field1 = Test.FIeld1 AND
> SUbQuery.Field2 = Test.FIeld2 AND
> SUbQuery.Date = Test.Date
> Its alsways interesting for performance issues to send the execution plan
> with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
> command and youll get the query execution plan in text displayes, which
> you can copy & paste in the newsgroups.
> HTH, Jens Suessmeyer.
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
|||John,
There is another way to do this, but I can not asure that it will give
better performance. you have to compare both execution plan.
select
t1.*
from
[dbo].[Test] as t1 with (nolock)
where
t1.[Date] = (
select
max(t2.[Date])
from
[dbo].[Test] as t2 with (nolock)
where
t2.[Field1] = t1.[Field1]
and t2.[Field2] = t1.[Field2]
)
AMB
"John Peterson" wrote:

> Hello Jens!
> I think your suggestion is exactly what I came up with (see original
> post) -- I was hoping maybe there was an even better way to express this.
>
> "Jens Sü?meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
> message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
>
>
|||Sorry, but thats not exact the same, because you additionaly did a
correlated query
within your subselect and additionaly did and a join outside the query.
Bit the best thing to see the differences is to get the query plan for that
options.
HTH, Jens Suessmeyer.
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:uHWKtFPeFHA.1288@.tk2msftngp13.phx.gbl...
> Hello Jens!
> I think your suggestion is exactly what I came up with (see original
> post) -- I was hoping maybe there was an even better way to express this.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
>
|||Thanks Alejandro! I'll compare/contrast this technique. Typically, a
subselect within a field has performed more slowly for me -- but not every
case is the same. I'll do as you suggest and examine the Excecution Plans.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EA463D89-3CD7-474A-8DAD-6638C9B4066D@.microsoft.com...[vbcol=seagreen]
> John,
> There is another way to do this, but I can not asure that it will give
> better performance. you have to compare both execution plan.
> select
> t1.*
> from
> [dbo].[Test] as t1 with (nolock)
> where
> t1.[Date] = (
> select
> max(t2.[Date])
> from
> [dbo].[Test] as t2 with (nolock)
> where
> t2.[Field1] = t1.[Field1]
> and t2.[Field2] = t1.[Field2]
> )
>
> AMB
>
>
> "John Peterson" wrote:
|||I don't mean to be obtuse, but I must be missing something. Your query and
my original query appear to be identical.
Here's what I had originally crafted:
select t.*
from [dbo].[Test] as t with (nolock)
inner join (
select [Field1] = t.[Field1],
[Field2] = t.[Field2],
[Date] = max(t.[Date])
from [dbo].[Test] as t with (nolock)
group by t.[Field1],
t.[Field2]
) as q on q.[Field1] = t.[Field1]
and q.[Field2] = t.[Field2]
and q.[Date] = t.[Date]
And here's what you suggested:
Select * From Test
Inner Join
(
Select Field1,Field2,Max(Date)
From Test
Group by Field1,Field2
) SUbQuery
on
SUbQuery.Field1 = Test.FIeld1 AND
SUbQuery.Field2 = Test.FIeld2 AND
SUbQuery.Date = Test.Date
Formatting aside, those seem *identical* to me. Or is there some subtle
difference that I don't perceive that might influence the performance?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238a5LuPeFHA.2984@.TK2MSFTNGP15.phx.gbl...
> Sorry, but thats not exact the same, because you additionaly did a
> correlated query
> within your subselect and additionaly did and a join outside the query.
> Bit the best thing to see the differences is to get the query plan for
> that options.
> HTH, Jens Suessmeyer.
>
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:uHWKtFPeFHA.1288@.tk2msftngp13.phx.gbl...
>
|||Hi John,
The query you posted should perform just fine, provided you add the
proper indexes. It would definitely be desirable to have an index on
Test(Field1,Field2) or even on Test(Field1,Field2,"Date").
HTH,
Gert-Jan
John Peterson wrote:
> (SQL Server 2000, SP4)
> Hello all!
> I'm seeing a lot of I/O generated by one of our queries, and I think it's
> how the query is constructed. Consider the following:
> use [tempdb]
> go
> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
> create table [dbo].[Test]
> (
> [RowID] uniqueidentifier not NULL,
> [Field1] int not NULL,
> [Field2] int not NULL,
> [Date] datetime not NULL,
> primary key clustered ([RowID])
> )
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> select t.*
> from [dbo].[Test] as t with (nolock)
> select t.*
> from [dbo].[Test] as t with (nolock)
> inner join (
> select [Field1] = t.[Field1],
> [Field2] = t.[Field2],
> [Date] = max(t.[Date])
> from [dbo].[Test] as t with (nolock)
> group by t.[Field1],
> t.[Field2]
> ) as q on q.[Field1] = t.[Field1]
> and q.[Field2] = t.[Field2]
> and q.[Date] = t.[Date]
> In essence, I'm trying to find the rows from Test that have the *maximum*
> Date for a unique Field1/Field2 combination. This query is used in a VIEW,
> and users of this VIEW typically JOIN on RowID.
> I'm sure the "guts" of this VIEW can be refactored to be better, but I don't
> know how to do it. Any suggestions would be *much* appreciated!
> John Peterson

Need query optimization help.

(SQL Server 2000, SP4)
Hello all!
I'm seeing a lot of I/O generated by one of our queries, and I think it's
how the query is constructed. Consider the following:
use [tempdb]
go
if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
create table [dbo].[Test]
(
[RowID] uniqueidentifier not NULL,
[Field1] int not NULL,
[Field2] int not NULL,
[Date] datetime not NULL,
primary key clustered ([RowID])
)
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
'0:0:1'
insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
'0:0:1'
select t.*
from [dbo].[Test] as t with (nolock)
select t.*
from [dbo].[Test] as t with (nolock)
inner join (
select [Field1] = t.[Field1],
[Field2] = t.[Field2],
[Date] = max(t.[Date])
from [dbo].[Test] as t with (nolock)
group by t.[Field1],
t.[Field2]
) as q on q.[Field1] = t.[Field1]
and q.[Field2] = t.[Field2]
and q.[Date] = t.[Date]
In essence, I'm trying to find the rows from Test that have the *maximum*
Date for a unique Field1/Field2 combination. This query is used in a VIEW,
and users of this VIEW typically JOIN on RowID.
I'm sure the "guts" of this VIEW can be refactored to be better, but I don't
know how to do it. Any suggestions would be *much* appreciated!
John PetersonWhat about (didn´t try that but it should work)
Select * From Test
Inner Join
(
Select Field1,Field2,Max(Date)
From Test
Group by Field1,Field2
) SUbQuery
on
SUbQuery.Field1 = Test.FIeld1 AND
SUbQuery.Field2 = Test.FIeld2 AND
SUbQuery.Date = Test.Date
Its alsways interesting for performance issues to send the execution plan
with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
command and you´ll get the query execution plan in text displayes, which you
can copy & paste in the newsgroups.
HTH, Jens Suessmeyer.
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP4)
> Hello all!
> I'm seeing a lot of I/O generated by one of our queries, and I think it's
> how the query is constructed. Consider the following:
>
> use [tempdb]
> go
> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
> create table [dbo].[Test]
> (
> [RowID] uniqueidentifier not NULL,
> [Field1] int not NULL,
> [Field2] int not NULL,
> [Date] datetime not NULL,
> primary key clustered ([RowID])
> )
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> select t.*
> from [dbo].[Test] as t with (nolock)
> select t.*
> from [dbo].[Test] as t with (nolock)
> inner join (
> select [Field1] = t.[Field1],
> [Field2] = t.[Field2],
> [Date] = max(t.[Date])
> from [dbo].[Test] as t with (nolock)
> group by t.[Field1],
> t.[Field2]
> ) as q on q.[Field1] = t.[Field1]
> and q.[Field2] = t.[Field2]
> and q.[Date] = t.[Date]
>
> In essence, I'm trying to find the rows from Test that have the *maximum*
> Date for a unique Field1/Field2 combination. This query is used in a
> VIEW, and users of this VIEW typically JOIN on RowID.
> I'm sure the "guts" of this VIEW can be refactored to be better, but I
> don't know how to do it. Any suggestions would be *much* appreciated!
> John Peterson
>|||Hello Jens!
I think your suggestion is exactly what I came up with (see original
post) -- I was hoping maybe there was an even better way to express this.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
> What about (didn´t try that but it should work)
> Select * From Test
> Inner Join
> (
> Select Field1,Field2,Max(Date)
> From Test
> Group by Field1,Field2
> ) SUbQuery
> on
> SUbQuery.Field1 = Test.FIeld1 AND
> SUbQuery.Field2 = Test.FIeld2 AND
> SUbQuery.Date = Test.Date
> Its alsways interesting for performance issues to send the execution plan
> with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
> command and you´ll get the query execution plan in text displayes, which
> you can copy & paste in the newsgroups.
> HTH, Jens Suessmeyer.
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> (SQL Server 2000, SP4)
>> Hello all!
>> I'm seeing a lot of I/O generated by one of our queries, and I think it's
>> how the query is constructed. Consider the following:
>>
>> use [tempdb]
>> go
>> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
>> create table [dbo].[Test]
>> (
>> [RowID] uniqueidentifier not NULL,
>> [Field1] int not NULL,
>> [Field2] int not NULL,
>> [Date] datetime not NULL,
>> primary key clustered ([RowID])
>> )
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
>> '0:0:1'
>> select t.*
>> from [dbo].[Test] as t with (nolock)
>> select t.*
>> from [dbo].[Test] as t with (nolock)
>> inner join (
>> select [Field1] = t.[Field1],
>> [Field2] = t.[Field2],
>> [Date] = max(t.[Date])
>> from [dbo].[Test] as t with (nolock)
>> group by t.[Field1],
>> t.[Field2]
>> ) as q on q.[Field1] = t.[Field1]
>> and q.[Field2] = t.[Field2]
>> and q.[Date] = t.[Date]
>>
>> In essence, I'm trying to find the rows from Test that have the *maximum*
>> Date for a unique Field1/Field2 combination. This query is used in a
>> VIEW, and users of this VIEW typically JOIN on RowID.
>> I'm sure the "guts" of this VIEW can be refactored to be better, but I
>> don't know how to do it. Any suggestions would be *much* appreciated!
>> John Peterson
>>
>|||John,
There is another way to do this, but I can not asure that it will give
better performance. you have to compare both execution plan.
select
t1.*
from
[dbo].[Test] as t1 with (nolock)
where
t1.[Date] = (
select
max(t2.[Date])
from
[dbo].[Test] as t2 with (nolock)
where
t2.[Field1] = t1.[Field1]
and t2.[Field2] = t1.[Field2]
)
AMB
"John Peterson" wrote:
> Hello Jens!
> I think your suggestion is exactly what I came up with (see original
> post) -- I was hoping maybe there was an even better way to express this.
>
> "Jens Sü�meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
> message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
> > What about (didn´t try that but it should work)
> >
> > Select * From Test
> > Inner Join
> > (
> > Select Field1,Field2,Max(Date)
> > From Test
> > Group by Field1,Field2
> > ) SUbQuery
> > on
> > SUbQuery.Field1 = Test.FIeld1 AND
> > SUbQuery.Field2 = Test.FIeld2 AND
> > SUbQuery.Date = Test.Date
> >
> > Its alsways interesting for performance issues to send the execution plan
> > with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
> > command and you´ll get the query execution plan in text displayes, which
> > you can copy & paste in the newsgroups.
> >
> > HTH, Jens Suessmeyer.
> >
> > "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> > news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> >> (SQL Server 2000, SP4)
> >>
> >> Hello all!
> >>
> >> I'm seeing a lot of I/O generated by one of our queries, and I think it's
> >> how the query is constructed. Consider the following:
> >>
> >>
> >> use [tempdb]
> >> go
> >>
> >> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
> >> create table [dbo].[Test]
> >> (
> >> [RowID] uniqueidentifier not NULL,
> >> [Field1] int not NULL,
> >> [Field2] int not NULL,
> >> [Date] datetime not NULL,
> >> primary key clustered ([RowID])
> >> )
> >>
> >> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> >> '0:0:1'
> >> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
> >> '0:0:1'
> >> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
> >> '0:0:1'
> >> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> >> '0:0:1'
> >> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
> >> '0:0:1'
> >> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> >> '0:0:1'
> >> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> >> '0:0:1'
> >> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> >> '0:0:1'
> >>
> >> select t.*
> >> from [dbo].[Test] as t with (nolock)
> >>
> >> select t.*
> >> from [dbo].[Test] as t with (nolock)
> >> inner join (
> >> select [Field1] = t.[Field1],
> >> [Field2] = t.[Field2],
> >> [Date] = max(t.[Date])
> >> from [dbo].[Test] as t with (nolock)
> >> group by t.[Field1],
> >> t.[Field2]
> >> ) as q on q.[Field1] = t.[Field1]
> >> and q.[Field2] = t.[Field2]
> >> and q.[Date] = t.[Date]
> >>
> >>
> >> In essence, I'm trying to find the rows from Test that have the *maximum*
> >> Date for a unique Field1/Field2 combination. This query is used in a
> >> VIEW, and users of this VIEW typically JOIN on RowID.
> >>
> >> I'm sure the "guts" of this VIEW can be refactored to be better, but I
> >> don't know how to do it. Any suggestions would be *much* appreciated!
> >>
> >> John Peterson
> >>
> >>
> >
> >
>
>|||Sorry, but thats not exact the same, because you additionaly did a
correlated query
within your subselect and additionaly did and a join outside the query.
Bit the best thing to see the differences is to get the query plan for that
options.
HTH, Jens Suessmeyer.
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:uHWKtFPeFHA.1288@.tk2msftngp13.phx.gbl...
> Hello Jens!
> I think your suggestion is exactly what I came up with (see original
> post) -- I was hoping maybe there was an even better way to express this.
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
>> What about (didn´t try that but it should work)
>> Select * From Test
>> Inner Join
>> (
>> Select Field1,Field2,Max(Date)
>> From Test
>> Group by Field1,Field2
>> ) SUbQuery
>> on
>> SUbQuery.Field1 = Test.FIeld1 AND
>> SUbQuery.Field2 = Test.FIeld2 AND
>> SUbQuery.Date = Test.Date
>> Its alsways interesting for performance issues to send the execution plan
>> with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing the
>> command and you´ll get the query execution plan in text displayes, which
>> you can copy & paste in the newsgroups.
>> HTH, Jens Suessmeyer.
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> (SQL Server 2000, SP4)
>> Hello all!
>> I'm seeing a lot of I/O generated by one of our queries, and I think
>> it's how the query is constructed. Consider the following:
>>
>> use [tempdb]
>> go
>> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
>> create table [dbo].[Test]
>> (
>> [RowID] uniqueidentifier not NULL,
>> [Field1] int not NULL,
>> [Field2] int not NULL,
>> [Date] datetime not NULL,
>> primary key clustered ([RowID])
>> )
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
>> '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
>> '0:0:1'
>> select t.*
>> from [dbo].[Test] as t with (nolock)
>> select t.*
>> from [dbo].[Test] as t with (nolock)
>> inner join (
>> select [Field1] = t.[Field1],
>> [Field2] = t.[Field2],
>> [Date] = max(t.[Date])
>> from [dbo].[Test] as t with (nolock)
>> group by t.[Field1],
>> t.[Field2]
>> ) as q on q.[Field1] = t.[Field1]
>> and q.[Field2] = t.[Field2]
>> and q.[Date] = t.[Date]
>>
>> In essence, I'm trying to find the rows from Test that have the
>> *maximum* Date for a unique Field1/Field2 combination. This query is
>> used in a VIEW, and users of this VIEW typically JOIN on RowID.
>> I'm sure the "guts" of this VIEW can be refactored to be better, but I
>> don't know how to do it. Any suggestions would be *much* appreciated!
>> John Peterson
>>
>>
>|||Thanks Alejandro! I'll compare/contrast this technique. Typically, a
subselect within a field has performed more slowly for me -- but not every
case is the same. I'll do as you suggest and examine the Excecution Plans.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EA463D89-3CD7-474A-8DAD-6638C9B4066D@.microsoft.com...
> John,
> There is another way to do this, but I can not asure that it will give
> better performance. you have to compare both execution plan.
> select
> t1.*
> from
> [dbo].[Test] as t1 with (nolock)
> where
> t1.[Date] = (
> select
> max(t2.[Date])
> from
> [dbo].[Test] as t2 with (nolock)
> where
> t2.[Field1] = t1.[Field1]
> and t2.[Field2] = t1.[Field2]
> )
>
> AMB
>
>
> "John Peterson" wrote:
>> Hello Jens!
>> I think your suggestion is exactly what I came up with (see original
>> post) -- I was hoping maybe there was an even better way to express this.
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
>> in
>> message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
>> > What about (didn´t try that but it should work)
>> >
>> > Select * From Test
>> > Inner Join
>> > (
>> > Select Field1,Field2,Max(Date)
>> > From Test
>> > Group by Field1,Field2
>> > ) SUbQuery
>> > on
>> > SUbQuery.Field1 = Test.FIeld1 AND
>> > SUbQuery.Field2 = Test.FIeld2 AND
>> > SUbQuery.Date = Test.Date
>> >
>> > Its alsways interesting for performance issues to send the execution
>> > plan
>> > with the post, use the SET SHOWPLAN_TEXT ON Statement before issuing
>> > the
>> > command and you´ll get the query execution plan in text displayes,
>> > which
>> > you can copy & paste in the newsgroups.
>> >
>> > HTH, Jens Suessmeyer.
>> >
>> > "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> > news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> >> (SQL Server 2000, SP4)
>> >>
>> >> Hello all!
>> >>
>> >> I'm seeing a lot of I/O generated by one of our queries, and I think
>> >> it's
>> >> how the query is constructed. Consider the following:
>> >>
>> >>
>> >> use [tempdb]
>> >> go
>> >>
>> >> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
>> >> create table [dbo].[Test]
>> >> (
>> >> [RowID] uniqueidentifier not NULL,
>> >> [Field1] int not NULL,
>> >> [Field2] int not NULL,
>> >> [Date] datetime not NULL,
>> >> primary key clustered ([RowID])
>> >> )
>> >>
>> >> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
>> >> delay
>> >> '0:0:1'
>> >>
>> >> select t.*
>> >> from [dbo].[Test] as t with (nolock)
>> >>
>> >> select t.*
>> >> from [dbo].[Test] as t with (nolock)
>> >> inner join (
>> >> select [Field1] = t.[Field1],
>> >> [Field2] = t.[Field2],
>> >> [Date] = max(t.[Date])
>> >> from [dbo].[Test] as t with (nolock)
>> >> group by t.[Field1],
>> >> t.[Field2]
>> >> ) as q on q.[Field1] = t.[Field1]
>> >> and q.[Field2] = t.[Field2]
>> >> and q.[Date] = t.[Date]
>> >>
>> >>
>> >> In essence, I'm trying to find the rows from Test that have the
>> >> *maximum*
>> >> Date for a unique Field1/Field2 combination. This query is used in a
>> >> VIEW, and users of this VIEW typically JOIN on RowID.
>> >>
>> >> I'm sure the "guts" of this VIEW can be refactored to be better, but I
>> >> don't know how to do it. Any suggestions would be *much* appreciated!
>> >>
>> >> John Peterson
>> >>
>> >>
>> >
>> >
>>|||I don't mean to be obtuse, but I must be missing something. Your query and
my original query appear to be identical.
Here's what I had originally crafted:
select t.*
from [dbo].[Test] as t with (nolock)
inner join (
select [Field1] = t.[Field1],
[Field2] = t.[Field2],
[Date] = max(t.[Date])
from [dbo].[Test] as t with (nolock)
group by t.[Field1],
t.[Field2]
) as q on q.[Field1] = t.[Field1]
and q.[Field2] = t.[Field2]
and q.[Date] = t.[Date]
And here's what you suggested:
Select * From Test
Inner Join
(
Select Field1,Field2,Max(Date)
From Test
Group by Field1,Field2
) SUbQuery
on
SUbQuery.Field1 = Test.FIeld1 AND
SUbQuery.Field2 = Test.FIeld2 AND
SUbQuery.Date = Test.Date
Formatting aside, those seem *identical* to me. Or is there some subtle
difference that I don't perceive that might influence the performance?
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238a5LuPeFHA.2984@.TK2MSFTNGP15.phx.gbl...
> Sorry, but thats not exact the same, because you additionaly did a
> correlated query
> within your subselect and additionaly did and a join outside the query.
> Bit the best thing to see the differences is to get the query plan for
> that options.
> HTH, Jens Suessmeyer.
>
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:uHWKtFPeFHA.1288@.tk2msftngp13.phx.gbl...
>> Hello Jens!
>> I think your suggestion is exactly what I came up with (see original
>> post) -- I was hoping maybe there was an even better way to express this.
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
>> in message news:uCi7hBPeFHA.3880@.tk2msftngp13.phx.gbl...
>> What about (didn´t try that but it should work)
>> Select * From Test
>> Inner Join
>> (
>> Select Field1,Field2,Max(Date)
>> From Test
>> Group by Field1,Field2
>> ) SUbQuery
>> on
>> SUbQuery.Field1 = Test.FIeld1 AND
>> SUbQuery.Field2 = Test.FIeld2 AND
>> SUbQuery.Date = Test.Date
>> Its alsways interesting for performance issues to send the execution
>> plan with the post, use the SET SHOWPLAN_TEXT ON Statement before
>> issuing the command and you´ll get the query execution plan in text
>> displayes, which you can copy & paste in the newsgroups.
>> HTH, Jens Suessmeyer.
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eM0id1OeFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> (SQL Server 2000, SP4)
>> Hello all!
>> I'm seeing a lot of I/O generated by one of our queries, and I think
>> it's how the query is constructed. Consider the following:
>>
>> use [tempdb]
>> go
>> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
>> create table [dbo].[Test]
>> (
>> [RowID] uniqueidentifier not NULL,
>> [Field1] int not NULL,
>> [Field2] int not NULL,
>> [Date] datetime not NULL,
>> primary key clustered ([RowID])
>> )
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
>> delay '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor
>> delay '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor
>> delay '0:0:1'
>> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
>> delay '0:0:1'
>> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor
>> delay '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
>> delay '0:0:1'
>> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor
>> delay '0:0:1'
>> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor
>> delay '0:0:1'
>> select t.*
>> from [dbo].[Test] as t with (nolock)
>> select t.*
>> from [dbo].[Test] as t with (nolock)
>> inner join (
>> select [Field1] = t.[Field1],
>> [Field2] = t.[Field2],
>> [Date] = max(t.[Date])
>> from [dbo].[Test] as t with (nolock)
>> group by t.[Field1],
>> t.[Field2]
>> ) as q on q.[Field1] = t.[Field1]
>> and q.[Field2] = t.[Field2]
>> and q.[Date] = t.[Date]
>>
>> In essence, I'm trying to find the rows from Test that have the
>> *maximum* Date for a unique Field1/Field2 combination. This query is
>> used in a VIEW, and users of this VIEW typically JOIN on RowID.
>> I'm sure the "guts" of this VIEW can be refactored to be better, but I
>> don't know how to do it. Any suggestions would be *much* appreciated!
>> John Peterson
>>
>>
>>
>|||Hi John,
The query you posted should perform just fine, provided you add the
proper indexes. It would definitely be desirable to have an index on
Test(Field1,Field2) or even on Test(Field1,Field2,"Date").
HTH,
Gert-Jan
John Peterson wrote:
> (SQL Server 2000, SP4)
> Hello all!
> I'm seeing a lot of I/O generated by one of our queries, and I think it's
> how the query is constructed. Consider the following:
> use [tempdb]
> go
> if (object_id('[dbo].[Test]') is not NULL) drop table [dbo].[Test]
> create table [dbo].[Test]
> (
> [RowID] uniqueidentifier not NULL,
> [Field1] int not NULL,
> [Field2] int not NULL,
> [Date] datetime not NULL,
> primary key clustered ([RowID])
> )
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 2, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 3, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 3, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 2, 1, getdate()) waitfor delay
> '0:0:1'
> insert into [dbo].[Test] values (newid(), 1, 1, getdate()) waitfor delay
> '0:0:1'
> select t.*
> from [dbo].[Test] as t with (nolock)
> select t.*
> from [dbo].[Test] as t with (nolock)
> inner join (
> select [Field1] = t.[Field1],
> [Field2] = t.[Field2],
> [Date] = max(t.[Date])
> from [dbo].[Test] as t with (nolock)
> group by t.[Field1],
> t.[Field2]
> ) as q on q.[Field1] = t.[Field1]
> and q.[Field2] = t.[Field2]
> and q.[Date] = t.[Date]
> In essence, I'm trying to find the rows from Test that have the *maximum*
> Date for a unique Field1/Field2 combination. This query is used in a VIEW,
> and users of this VIEW typically JOIN on RowID.
> I'm sure the "guts" of this VIEW can be refactored to be better, but I don't
> know how to do it. Any suggestions would be *much* appreciated!
> John Peterson

Need line feed in ToolTip text - how?

Hello,

I am displaying a complex formula in a column header tool tip. The formula is generated in a stored procedure. (I do not enter the tool tip text directly in the column header expression.)

Within the stored procedure, how do I generate a string that contains a carriage return/line feed?

Thanks,

BCB

In you sproc insert

select 'here is the break' + char(10) + 'here is the next line' as text

In your tool tip insert

=First(Fields!text.Value)

|||

Thanks for the response, but I don't think your solution will work in my case. The expression that I use for the tooltip text is:

=First(Fields!COL_4.Value, "ToolTips")

You can see that the tooltip is coming from a "ToolTips" dataset. The dataset is produced by a stored procedure. I need to format the tooltip string within the stored procedure that will contain the line feed. I don't believe the Chr(10) would work within a sproc.

Can you think of another approach?

Thanks,

BCB

|||Using CHAR(10) works fine, at least in SQL Server. Your database may be different, but I'd guess it still has some way to embed special characters into string data.|||

You will have to use CHAR(10)+CHAR(13) for line feed and carriage return in your stored proc in SQL Server.

Shyam

|||

I misunderstood Harley Rider's correct response. Thanks to everyone for setting me straight.

BCB

Need Help! Connection Count to Linked DB2 Server Reaches limit (SQL1040N) when execute dyn

Need Help! Connection Count to Linked DB2 Server Reaches limit
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.
Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.
At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.
So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.
The pseud code something like this:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC @.Dynamic_SQL
END
.=2E..
END
But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message: SQL1040N
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7= =94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95= =B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].
I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?
Thanks.
Regards,
Ling, Xiao-liSorry, the pseud code should lik this, just pseud code, in case someone
will question the pseud code validity:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC (@.Dynamic_SQL)
FETCH NEXT FROM condition_cursor INTO
local_variables
END
.=2E..
END
alingsjtu@.gmail.com =E5=86=99=E9=81=93=EF=BC=9A
> CREATE PROCEDURE p_FetchRawData variable_list
> AS
> BEGIN
> ...
> DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
> SELECT * FROM local_condition_table
> OPEN condition_cursor
> FETCH NEXT FROM condition_cursor INTO
> local_variables
> WHILE @.@.FETCH_STATUS =3D 0
> BEGIN
> SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
> SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
> ')'
> EXEC @.Dynamic_SQL
> END
> ...
> END
> But when execute this stored procedure p_FetchRawData, when the loop
> count is too big, then I got the error:
> [OLE/DB provider returned message: SQL1040N
> =E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94==E7=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6==95=B0=E7=9B=AE=E3=80=82
> SQLSTATE=3D57030]
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80040e69].
> I understood this error meaning which said too many OPENQUERY
> connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
> their connections to linked server? How to fail these connections when
> every OPENQUERY execution finished?
> > Thanks.
> > Regards,
> Ling, Xiao-li