Query on associations whitout join ?

tropico · **Joined:** Thu Aug 17, 2006 5:10 am **Posts:** 3

Hi all

I'm using SQLServer 2005.

I've two tables with a simple one-to-many relationship (Document 1=> n Files)

I d'like to retrieve every document that have a file named "test.txt" or in sql form

Code:

SELECT * FROM Documents,Files
WHERE Documents.DocumentId = Files.DocumentId
AND Files.Name = "test.txt"

I'm able to retrieve the values using that ICriteria based code:

Code:

ICriteria criteria = session.CreateCriteria(typeof(Document));
criteria.CreateAlias("Files", "files");
criteria.Add(NHibernate.Expression.Expression.Eq("files.Name","test.txt"));
criteria.List();

But that Criteria generate that kind of query :

Code:

SELECT 
this.DocumentId as DocumentId1_, 
this.ModifiedBy as ModifiedBy1_, 
this.LastUpdateDate as LastUpda4_1_, 
this.CreationDate as Creation3_1_, 
this.RetentionDate as Retentio2_1_, 
files.fileId as FileId0_, 
files.DocumentId as DocumentId0_, 
files.Name as Files0_,
files.ModifiedBy as ModifiedBy0_, 
files.CreationDate as Creation3_0_, 
files.LastUpdateDate as LastUpda4_0_ 
FROM Documents this inner join Files files on this.DocumentId=files.DocumentId 
WHERE files.Name = 'test.txt'

That query returns me the good values but it MUCH slower that the first one (about 10 times slower (tests using SQL Manager))!

I'm certainly missing something here, sorry if it seems too obvious.

Thanks for help

tropico · **Joined:** Thu Aug 17, 2006 5:10 am **Posts:** 3

Hum after some more testing, it seems that there is no big speed difference between join or not-join (Haa forgot about cache :)).

I still have another question

How did you write that query using HQL ?

Thanks

gert · **Joined:** Thu Jan 19, 2006 4:29 pm **Posts:** 348

tropico wrote:

Hum after some more testing, it seems that there is no big speed difference between join or not-join (Haa forgot about cache :)).

I still have another question

How did you write that query using HQL ?

Thanks

What is the speed if You inverse the join? I.e.

Code:

SELECT
this.*
FROM Files files
inner join Documents this on this.DocumentId=files.DocumentId
WHERE files.Name = 'test.txt'

In some RDBMS, the cartesian join (Your faster example) is EXTREMLY slow, if the tables do contain much data.

Gert

tropico · **Joined:** Thu Aug 17, 2006 5:10 am **Posts:** 3

It takes the same time as the others.

about 15-16 seconds the first time, less after (cache).

My table contains a lot of entries (~2.500.000)

any idea for the HQL equivalent ?

Tks for answer