Menu
▾
▴
[Nhibernate-commit] SF.net SVN: nhibernate:[5007] trunk/nhibernate/src/NHibernate
|
From: <jul...@us...> - 2010-07-15 16:20:58
|
Revision: 5007
http://nhibernate.svn.sourceforge.net/nhibernate/?rev=5007&view=rev
Author: julian-maughan
Date: 2010-07-15 16:20:51 +0000 (Thu, 15 Jul 2010)
Log Message:
-----------
Performance enhancement, reported as "MsSql2005Dialect does not use parameters for paging parameters" (ref. NH-2215).
Modified Paths:
--------------
trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs
trunk/nhibernate/src/NHibernate.Test/Pagination/CustomMsSqlDriver.cs
Modified: trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
===================================================================
--- trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2010-07-15 15:16:31 UTC (rev 5006)
+++ trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2010-07-15 16:20:51 UTC (rev 5007)
@@ -45,7 +45,7 @@
public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
{
//dont do this paging code if there is no offset, use the
- //sql 2000 dialect since it wont just uses a top statement
+ //sql 2000 dialect since it just uses a top statement
if (offset == 0)
{
return base.GetLimitString(querySqlString, offset, last);
@@ -76,11 +76,17 @@
sortExpressions = new[] {new SqlString("CURRENT_TIMESTAMP"),};
}
+ Parameter limitParameter = Parameter.Placeholder;
+ limitParameter.ParameterPosition = 0;
+
+ Parameter offsetParameter = Parameter.Placeholder;
+ offsetParameter.ParameterPosition = 1;
+
SqlStringBuilder result =
new SqlStringBuilder()
- .Add("SELECT TOP ")
- .Add(last.ToString())
- .Add(" ")
+ .Add("SELECT TOP (")
+ .Add(limitParameter)
+ .Add(") ")
.Add(StringHelper.Join(", ", columnsOrAliases))
.Add(" FROM (")
.Add(select)
@@ -88,10 +94,12 @@
AppendSortExpressions(aliasToColumn, sortExpressions, result);
- result.Add(") as __hibernate_sort_row ")
- .Add(from)
- .Add(") as query WHERE query.__hibernate_sort_row > ")
- .Add(offset.ToString()).Add(" ORDER BY query.__hibernate_sort_row");
+ result
+ .Add(") as __hibernate_sort_row ")
+ .Add(from)
+ .Add(") as query WHERE query.__hibernate_sort_row > ")
+ .Add(offsetParameter)
+ .Add(" ORDER BY query.__hibernate_sort_row");
return result.ToSqlString();
}
@@ -106,8 +114,7 @@
return trimmedExpression.Trim();
}
- private static void AppendSortExpressions(Dictionary<SqlString, SqlString> aliasToColumn, SqlString[] sortExpressions,
- SqlStringBuilder result)
+ private static void AppendSortExpressions(Dictionary<SqlString, SqlString> aliasToColumn, SqlString[] sortExpressions, SqlStringBuilder result)
{
for (int i = 0; i < sortExpressions.Length; i++)
{
@@ -256,6 +263,21 @@
{
get { return true; }
}
+
+ public override bool BindLimitParametersInReverseOrder
+ {
+ get { return true; }
+ }
+
+ public override bool SupportsVariableLimit
+ {
+ get { return true; }
+ }
+
+ public override bool BindLimitParametersFirst
+ {
+ get { return true; }
+ }
protected override string GetSelectExistingObject(string name, Table table)
{
@@ -436,4 +458,3 @@
}
}
}
-
Modified: trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs
===================================================================
--- trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2010-07-15 15:16:31 UTC (rev 5006)
+++ trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2010-07-15 16:20:51 UTC (rev 5007)
@@ -19,37 +19,37 @@
SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 Contact1_19_0_, Rating2_19_0_ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, ROW_NUMBER() OVER(ORDER BY c.Rating DESC, c.Last_Name, c.First_Name) as __hibernate_sort_row from dbo.Contact c where COALESCE(c.Rating, 0) > 0) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) Contact1_19_0_, Rating2_19_0_ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, ROW_NUMBER() OVER(ORDER BY c.Rating DESC, c.Last_Name, c.First_Name) as __hibernate_sort_row from dbo.Contact c where COALESCE(c.Rating, 0) > 0) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id FROM (SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) id FROM (SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 ixx9_ FROM (SELECT DISTINCT fish_.id as ixx9_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) ixx9_ FROM (SELECT DISTINCT fish_.id as ixx9_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), 5, 15);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 15 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 5 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT fish.id, fish.name FROM fish ORDER BY name DESC"), 7, 28);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 28 id, name FROM (SELECT fish.id, fish.name, ROW_NUMBER() OVER(ORDER BY fish.name DESC) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 7 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) id, name FROM (SELECT fish.id, fish.name, ROW_NUMBER() OVER(ORDER BY fish.name DESC) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str =
@@ -57,25 +57,25 @@
new SqlString("SELECT * FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t ORDER BY name DESC"), 10, 20);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 20 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name DESC) as __hibernate_sort_row FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) as query WHERE query.__hibernate_sort_row > 10 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name DESC) as __hibernate_sort_row FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT f.Type, COUNT(DISTINCT f.Name) AS Name FROM Fish f GROUP BY f.Type ORDER BY COUNT(DISTINCT f.Name)"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
}
@@ -97,14 +97,14 @@
SqlString result =
d.GetLimitString(new SqlString("select concat(a.Description,', ', a.Description) as desc from Animal a"), 1, 10);
System.Console.WriteLine(result);
- Assert.AreEqual("SELECT TOP 10 desc FROM (select concat(a.Description,', ', a.Description) as desc, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from Animal a) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row", result.ToString());
+ Assert.AreEqual("SELECT TOP (?) desc FROM (select concat(a.Description,', ', a.Description) as desc, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from Animal a) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", result.ToString());
// The test use the function "cast" because cast need the keyWork "as" too
SqlString str =
d.GetLimitString(new SqlString("SELECT fish.id, cast('astring, with,comma' as string) as bar FROM fish"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id, bar FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP (?) id, bar FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
}
[Test]
Modified: trunk/nhibernate/src/NHibernate.Test/Pagination/CustomMsSqlDriver.cs
===================================================================
--- trunk/nhibernate/src/NHibernate.Test/Pagination/CustomMsSqlDriver.cs 2010-07-15 15:16:31 UTC (rev 5006)
+++ trunk/nhibernate/src/NHibernate.Test/Pagination/CustomMsSqlDriver.cs 2010-07-15 16:20:51 UTC (rev 5007)
@@ -22,11 +22,11 @@
int offset = (int)((IDataParameter)command.Parameters[0]).Value;
int limit = (int)((IDataParameter)command.Parameters[1]).Value;
- Assert.That(command.CommandText.ToLower().Contains("top " + limit),
- "Expected string containing 'top " + limit + "', but got " + command.CommandText);
+ Assert.That(command.CommandText.ToLower().Contains("top (@p0)"),
+ "Expected string containing 'top (@p0)', but got " + command.CommandText);
- Assert.That(command.CommandText.ToLower().Contains("hibernate_sort_row > " + offset),
- "Expected string containing 'hibernate_sort_row > " + offset + "', but got " + command.CommandText);
+ Assert.That(command.CommandText.ToLower().Contains("hibernate_sort_row > @p1"),
+ "Expected string containing 'hibernate_sort_row > @p1', but got " + command.CommandText);
}
base.OnBeforePrepare(command);
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
