Join tables

hi
I got a confusing problem.I have 2 tables (Table_1 , Table_2) whit relation
On table_1.key and table_2.fkey.I need to get a table contains information from
2 table : title , key , fkey for mindate,mindate , describtion of mindate.
so I tried to write a function for returning (select top(1) * from table_2 order by date) so only
I need to connect this function to table_1. Here for running function I need to send Key to function
and I dont know how I can do that because wnehe I try to join them I get Error message.in seccond try I made a procedure
like this : SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1 INNER JOIN
Table_2 ON Table_1.[Key] = Table_2.fkey where Table_2.[key]=(select top(1) Table_2.[key] from table_2 where fkey=Table_1.[key] order by date )

it works perfectly but gets more time to run whene we have 2000 records.Speed goes down ...

table_1:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[title] [nchar](10) COLLATE Arabic_CI_AS NULL,
[Key] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

table_2:
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[date] [datetime] NULL,
[fkey] [int] NULL,
[key] [int] IDENTITY(1,1) NOT NULL,
[describtion] [nchar](10) COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([fkey])
REFERENCES [dbo].[Table_1] ([Key])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

data:
insert into table_1 (title) values('Title1')
insert into table_1 (title) values('Title2')
insert into table_1 (title) values('Title3')

insert into table_2 (date,fkey,describtion) values('2006/10/11',1,'dis 1')
insert into table_2 (date,fkey,describtion) values('2006/10/12',1,'dis 2')
insert into table_2 (date,fkey,describtion) values('2006/10/14',1,'dis 3')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 4')
insert into table_2 (date,fkey,describtion) values('2006/10/10',2,'dis 5')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 6')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 7')
insert into table_2 (date,fkey,describtion) values('2006/10/13',3,'dis 8')
insert into table_2 (date,fkey,describtion) values('2006/10/12',3,'dis 9')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 10')

resault :
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7

hai,

First you can create a view

create view mindatelist as
select min(date) as mindate,fkey from Table_2
group by fkey

then run below query..

SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1
INNER JOIN Table_2 ON Table_1.[Key] = Table_2.fkey
inner join mindatelist on mindatelist.fkey=Table_1.[Key] and Table_2.date=mindatelist.mindate

Next check the performance of this query...

Jefy

|||the resault is rong because we get 2 rows for key # 3
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7
Title3 3 3 2006-10-11 00:00:00.000 dis 10|||

Koosha:

One thing to understand is that your code without the scalar function will run faster than with the scalar function; there is a certain amount of additional overhead that goes with the scalar function pluse the optimizer doesn't optimize scalar functions well. I tried a few things to try to speed up your query. First, I ran your query to get a performance baseline. I then modified the query into what is listed below and tested with the sample data you provided to verify that it qualitatively looked correct.

Next, I generated 2048 entries for table_1 and 32767 entries for table_2 and benchmarked under these circumstances. This is a really small sample set to benchmark with, but I still think the results will at least be indicative. I then compared the modified code to the original code; the time reduction associated with the new code was about 70%; the IO reduction of the new code was about 99%. This does NOT mean that the code is optimized, but it does mean that it is improved -- at least under my particular test circumstances.

Next, I did an experiment to see if a cover index might improve performance. The cover index reduced IO by a very thin 8% or so. Execution time was reduced by about 30%. I would suggest that if this function is not critical that I would probably NOT implement a cover index -- I just don't think it is going to be worth the overhead. Here is the query:

Dave

select t1.title,
t1.[key],
t2.fkey,
t2.date,
t2.describtion
from table_1 t1
inner join
( select [key],
fkey,
date,
describtion,
row_number () over
( partition by fkey
order by date, [key]
) as seq
from table_2
) t2
on t1.[key] = t2.fkey
and t2.seq = 1

|||it works perfectly.thanks for ur helping