Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matrixr
Starting Member
26 Posts |
 Posted - 2006-10-19 : 19:03:05
|
| at the moment i use this to page results, this works nicely for a table with less than 20,000 records, anything after that its very slow, anyone can give me some pointers to improve this?SELECT @offset = @totalRecords - (@pageSize * @page)SELECT @mainSql = N'SELECT * FROM ( SELECT TOP ' + convert(varchar, @pageSize) + ' * FROM (SELECT TOP ' + convert(varchar, @offset) + ' * FROM dbo.[Log] ORDER BY TimeLog ASC) a ORDER BY TimeLog DESC) bORDER BY'if (@orderBy is null) begin select @mainSql = @mainSql + ' TimeLog DESC'execute(@mainSql)thanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
matrixr
Starting Member
26 Posts |
Posted - 2006-10-20 : 20:48:48
|
| thanks for the links but i am already using one of those techniques, temp table is not an option because i have too many columns, creating a temp table with that many columns is not an option, i need some advice to speed up the sub queries |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-22 : 01:42:10
|
| How many columns do you have? Using your method there's not much that can be done to make it get any faster.Tara Kizer |
|
|
|
matrixr
Starting Member
26 Posts |
Posted - 2006-10-22 : 16:34:54
|
| for the log table i can use a temp table, but tehre is another stored proc that uses the same technique to page, that proc does a select * on a view which joins 8 tables and returns ~60 columns, i remember in mysql there is a limit x,x and that was really quick, no equivalent in mssql? |
|
|
|
iminore
Posting Yak Master
141 Posts |
Posted - 2006-10-23 : 08:56:38
|
| Does the 'Log' table have an index over the 'TimeLog' column? Creating one should speed it up. |
|
|
|
|
|
|
|
|
paging