[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] wesley wu updated HHH-2655: --------------------------- Attachment: SQLServer2005Dialect.java Sorry, my previous file contains a serious bug, please test the new one. I've create a test to page 200+ records with 20s per page and gain 5% performance, with SQLServer2005Dialect. There should be significant performance gaining when dealing with 20000+ records or more/much more. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Ed Wallen updated HHH-2655: --------------------------- Attachment: BindingSqlServerDialect.java I also found this same problem. I looked at the implementations provided with this ticket but wasn't all that happy with the performance I got out of them, so I wrote a new one (see attached BindingSqlServerDialect.java). This is now deployed in several of my production systems working against database tables with tens of millions of records. It has GREATLY improved the system's performance and efficiency, especially under heavy load. I certainly hope it is included as a part of the Hibernate codebase in the near future. -Ed -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Steve Ebersole commented on HHH-2655: ------------------------------------- Also, does sql server only support variable limits starting with 2005? Or is that supported prior to 2005? Basically, does this require a new dialect? Or can we simply modify the existing dialect? -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] João Nascimento Vasconcellos updated HHH-2655: ---------------------------------------------- Attachment: SQLServer2005Dialect.java Changing "TOP last" to "TOP 100 percent" has a better performance in SQL 2005. I've create a test to page 20+ records with 4s per page and gain 72% performance. I've create a test to page 100.000+ records with 100s per page and gain 100% performance. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Yoryos Valotassios updated HHH-2655: ------------------------------------ Attachment: SqlServer2008Dialect.java Non of the solutions worked for me. The BindinfSqlServerDialect didn't even had to do with the issue. I attach a dialect that seems to work ok (at least for me) till now. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Gail Badner updated HHH-2655: ----------------------------- Affects Version/s: 3.5.3 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Strong Liu reassigned HHH-2655: ------------------------------- Assignee: Strong Liu -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Mazda Fahandezh Saadi commented on HHH-2655: -------------------------------------------- unfortunately there was 2 Bugs in the last implementation. I have just fixed them and add the new File as Attachment. The First Bug was at the Statements this.registerColumnType(Types.VARCHAR, 1073741823, "NVARCHAR(MAX)"); this.registerColumnType(Types.VARCHAR, 2147483647, "VARCHAR(MAX)"); this.registerColumnType(Types.VARBINARY, 2147483647, "VARBINARY(MAX)"); in Constructor of the Class. They caused Hibernate to create Field with type String as NVARCHAR in MSSQL and ignored Length of the Column. It is only a problem if you want to create a index for this column. MSSQL can't create Index for columns with such a type. therefor i added this.registerColumnType(Types.VARCHAR, 8000, "VARCHAR($l)"); to the constructor. The Second Bug was, how the Sqlstring was parsed to find "select" or "select distinct". The sqlstatement was not correct if the property hibernate.use_sql_comments is true. In this case there is a comment at the beginning of the statement and this was not parsed correctly. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Strong Liu commented on HHH-2655: --------------------------------- good job, thank, patch applied -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Strong Liu commented on HHH-2655: --------------------------------- show me the test -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Daniel de Souza Costa Pedroso commented on HHH-2655: ---------------------------------------------------- I got a bug on offset calculation (file SqlServer2008Dialect.java 08/jul/10 3:33 PM). 1 record comes 2 times moving from 1st. page to 2nd page: - Given offset = 0 and limit = 5, query will be written 'select top 5...', returning rows from #1 to #5. - Given offset = 5 and limit = 10, query will return rows from #5 to #10. - row #5 comes twice... Solution: if(offset > 0) offset++; considering that developer will always use zero-based indexing for offset. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2655?page=c... ] Strong Liu commented on HHH-2655: --------------------------------- test case, please -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira