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 |
|
Petr
Starting Member
2 Posts |
 Posted - 2008-04-26 : 02:53:39
|
| I have a tmp table created asselect row_number() over ( order by duedate) as row ,duedate as date, ...into #fronta from oitb with(nolock)where ....order by duedateThe table is filled correctly with about 30k records. Now in next step I want to work with this tmp table I created, but I have problem, when I use query like thisselect * from #fronta where row < 500When the operator is = or <>, the query is quick, but when I use < or >, the query takes about 10 minutes.I tried to add to this tmp table index on field named row, but with no succes.Have anyone idea how to improve the speed?thanks a lot |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-26 : 03:09:00
|
you should be able to add indexes to temp tables. did you get an error or what?
elsasoft.org |
 |
|
|
Petr
Starting Member
2 Posts |
Posted - 2008-04-26 : 13:32:24
|
quote:
Originally posted by jezemine
you should be able to add indexes to temp tables. did you get an error or what?
elsasoft.org
No, there was no error, index was created but Im not sure if the index was applied, because the speed was same. When I tried to do dbcc dbreindex of tmp table, there was error that table doesnt exists in db (because is in tempdb). |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-26 : 18:12:23
|
if you want to know if the index is used, have a look at the query plan.
elsasoft.org |
|
|
|
QAZAFI
Yak Posting Veteran
50 Posts |
|
|
|
|
|
|
|
Index on #tmp table?