5

I am using Criteria to speed up a query, and I am almost there.

Using Query By Example to match up rows in a table, remove duplicate rows with the same id, and then paginate.

Of course I can't paginate until I remove the duplicate rows, and I don't know how to do that. It can be done in SQL, but then that does fit in with the fluent code, the ISQLQuery doesn't return an ICriteria object.

    public IList<EntitySearch> CriteriaSearch(EntitySearch exampleEntitySearch, int startingPage, int pageSize)
    {
        var startRow = startingPage * pageSize;

        // Query By Example.
        var example = Example.Create(exampleEntitySearch)
            .IgnoreCase()
            .EnableLike(MatchMode.Anywhere)
            .ExcludeZeroes();

        var results = this.Session.CreateCriteria(typeof(EntitySearch))
                                .Add(example)
        // select * from (SELECT ROW_NUMBER()OVER (partition by Id order by Id) As rankOrder, * FROM EntitySearch) as original where original.rankOrder = 1
                                .SetFirstResult(startRow)
                                .SetMaxResults(pageSize)
                                .List<DealSearch>();

        return results;
    }

Advice I've read is to write the SQL query in NHibernate, but I can't think how to convert the nifty "ROW_NUMBER() over partition SQL". I would like to get it working end to end first, then make it more elegant.

I'd like to get this spike into production and prove the ~90% speed up.

zeristor
  • 429
  • 6
  • 22
  • Well you can make NHibernate execute hard-coded SQL in your code but then you loose a bunch of the advantages NHibernate gives you like type-safety (strong typing from the map of classes to DB objects), being agnostic to the DB engine (more complicated SQL won't run on all DBs), lazy-loading, caching, etc. I would recommend, unless absolutely necessary and you are certain you will never switch DB engines, don't use hard-coded SQL in code with NHibernate. – Jesse Webb Nov 04 '11 at 18:44
  • Hard coded SQL is better than nothing, I'm afraid. Researching some more, the Criteria don't need to be in the correct order do they, and I should be able to use ExpressionSQL. My query was down to the specific bit of SQL which uses ROW_NUMBER over partition to take the first item in each subgrouping. Which is needed prior to pagination. – zeristor Nov 06 '11 at 23:18
  • There is an experienced team that might help you on this forum: http://sqlserver.ro, try there – radu florescu Dec 24 '11 at 14:28

1 Answers1

0

I'm not sure about performance, but you could use LINQ:

Change:

.List<DealSearch>();

To:

.List<DealSearch>().Distinct().ToList();
BaTTy.Koda
  • 173
  • 6
  • The idea in using Criteria is to do the processing on the server, and so reduce the data to be transferred to the server. LINQ for NHibernate would only work after the data has been delivered. There is .NET on SQL Server though, something I haven't thought about. – zeristor Jan 06 '12 at 14:14
  • Got ya', I missed the part about doing it server-side (which makes perfect sense), I was just looking at getting a distinct result set. I don't have time to look right now, but have you looked at SetResultTransformer using a DistinctEntityRootTransformer? – BaTTy.Koda Jan 06 '12 at 18:15