Need some help!

Hi All
I want to blow up the database. Say I have 10 records in a particular table
and I want to increase it to 100 records for performance testing purposes. I
s
there a way to do it in SQL Server?
Thank you
Viccu.Viccu
create table #test (id int not null identity(1,1))
declare @.i int
set @.i=1
while @.i<=100
begin
insert #test default values
set @.i=@.i+1
end
select * from #test
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||How about that. In answering your question I grabbed a function that was
posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
microsoft.public.sqlserver.programming there are 12 posts between yours and
his).
Anyway...
--Lets pretend this is your table:
create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime)
go
--and here is your 10 rows of data
insert into #foo (col2, col3) values ('test 1', '1/1/2006')
insert into #foo (col2, col3) values ('test 2', '2/1/2006')
insert into #foo (col2, col3) values ('test 3', '1/3/2006')
insert into #foo (col2, col3) values ('test 4', '1/4/2006')
insert into #foo (col2, col3) values ('test 5', '5/1/2006')
insert into #foo (col2, col3) values ('test 6', '1/6/2006')
insert into #foo (col2, col3) values ('test 7', '1/7/2006')
insert into #foo (col2, col3) values ('test 8', '8/1/2006')
insert into #foo (col2, col3) values ('test 9', '1/9/2006')
insert into #foo (col2, col3) values ('test 10', '10/1/2006')
--Peter Larson's function:
CREATE FUNCTION dbo.fnSeqNumbers
(
@.LowLimit INT,
@.HighLimit INT
)
RETURNS @.Values TABLE
(
Value INT
)
AS
BEGIN
DECLARE @.Temp INT
IF @.LowLimit > @.HighLimit
SELECT @.Temp = @.LowLimit,
@.LowLimit = @.HighLimit,
@.HighLimit = @.Temp
INSERT @.Values VALUES (@.LowLimit)
WHILE @.@.ROWCOUNT > 0
INSERT @.Values
SELECT n.Value + t.Items
FROM @.Values n
CROSS JOIN (
SELECT COUNT(*) Items
FROM @.Values
) t
WHERE n.Value + t.Items <= @.HighLimit
RETURN
END
go
-- showing you the results from the function:
--select * from dbo.fnSeqNumbers(1,10)
--this returns 100 rows
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,10) b
order by a.col2
--...but you already have 10 in your table so lets only insert 90 rows
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
select @.@.rowcount
--look at your "new" data:
select * From #foo
This solution assumes that you have an identity value as your primary key.
If that is not the case in your table but your primary key is a number you
should be able to convert the number to a string, concatinate the value from
the function, and convert it back to an int that could be used as your
primary key. Hopefully this example gives you a few ideas and points you in
a direction where you can figure out a solution.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||Thanks much.
"Uri Dimant" wrote:

> Viccu
> create table #test (id int not null identity(1,1))
> declare @.i int
> set @.i=1
> while @.i<=100
> begin
> insert #test default values
> set @.i=@.i+1
> end
> select * from #test
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||Thank you so much.
"Keith Kratochvil" wrote:

> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||google up "generate test data"|||Thank you.Because of the foreign key constraints,say one of my tables has a
value that should
correspond to the value in the other table. How would I include that
particular value
in the query given by you?
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
"Keith Kratochvil" wrote:

> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||You could hardcode a single value within the select, modify the function to
return an additional column that has "random" FK values passed out of it or
you could just use (re-use) the foreign key data that you already have in
your table (#foo in my example). You probably want to load all the columns
when you insert data...just make sure that you query your FK column from
your base table when joining to the function.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:D11F6AF9-11D2-43FB-AF0D-40726220E012@.microsoft.com...
> Thank you.Because of the foreign key constraints,say one of my tables has
> a
> value that should
> correspond to the value in the other table. How would I include that
> particular value
> in the query given by you?
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2sql

Need some help with picture in Crystal Reports, please.

Hello all!

I use VB.NET and Crystal Reports and I need to create a win report which will display pictures.
The issue is that I don't know what pictures should be printed while I design the report, but my application check this in runtime.
What I want to do is create a report that will get pictures by parameters or by any other way that will allow me to add pictures dynamically during runtime.
I heard about OLE Picture but I didn't find how to use it and I don't even sure that I should use it...

May someone explain me how to create such report?

Thanks in advanced,
Tom.Hi Tom,

Problem with Crystal and pictures is that there's no way (up until 8.5) to dynamically add a picture from a file or folder location.

You can, however, create a field in a table structure within your database to store BLOBs (BinaryLargeObjects) such as pictures or binary streams etc, and then embed that field within your report. That's provided your database supports the storage of binary objects.

So if you have a table for Personnel, and the Fields are Id, LastName, FirstName, Address etc, and you wanted to show a picture of the person, you would add a BLOB field named Pic to this table and then store the picture in it. Then when you show the person's details on the report, you will have the picture field available to select from the field list in Crystal, and pop it on the report in the appropriate location.

Some databases support storage of BLOBs 2 ways - directly storing the picture itself, and storing a file reference to a location within the file system.
You'll have to work out which way is gonna work for you.

Good luck

Dave|||I am having this same problem.

When you say "field list" are you refering to the dataset *.xsd file that is created in VB.NET 2002 that is populated from a SQL query and used and the record source for the crystal report?|||Raven (nevermore!)
I don't use Crystal and .NET, so I can't say absolutely yes to your question, but my theoretical understanding is that the schema is exposed via XML, so that anything in the underlying tables on the server side would be visible to the user as a "field".

The real issue here is that your connection can expoise a BLOB. If it can, you store the pictures in a table on the server and then retrieve thme to client side as what is essentially a binary stream. On the client side you have a container that can display binary stream data (like a picture box), and you load the binary data into that object.

Dave|||Thank You.

I have read the pictures into a SQL table via a binary stream and I have now read that data into Crystal Reports via a binary stream.

I have decided that I will bypass storing the pics in the database and simply store the path to the pics in the database and still perform the binary stream read into Crystal Reports.

thank for the help!

(" 'Tis some visitor," I muttered, "tapping at my chamber door;
Only this, and nothing more.") :p|||Raven,
One less midnight dreary, pondering weak and weary over one of those damned Crystal Reports!
My 11yo daughter's fave poem.

Glad to help - streaming is an often misunderstood feature, and once you master the mechanics of it, it's so useful. I first came across it coding with Intersystems Cache OO database, and it was very useful overcoming Crystal's problems with dynamic pictures.
Don't forget to rate the thread......

Dave
"Nameless here for evermore"

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