C#, SQL SERVER 2005
first. Sorry for the bad english :>
can anyone give me ideas, techniques, theories, or any posible solution.
Here is a scenario
i have a form called SearchItem and a database table doc_Item
doc_Item has a 1 million. . . data.
when i click a button getITem the form SearchItem shows up
and get all the items in doc_Item table. when my data is 100000 the my program is running correctly but not that fast it took some 3 seconds to retrieve,
but when i make it 1 million it becomes slower and cannot continue
The problem is the way i retrive data i think, i get all the doc_Item and store it in a temptable using the command
Select ROW_NUMBER() OVER(ORDER BY Id DESC) AS Row,*
INTO Temptable
From (doc_Item)a";
I create a temptable with RowNumber so that i can apply paging in my application. 100,000 is fine but 1 million cannot proceed. my error returns an invalid object temptable. i think the sql server cannot proceed in creating a temptable with 1 million data.
i used this technique to speed up my application performance. please help me. any recommendation, techniques or ideas are much appreciated.
How can i retrieve 1 million data.
Like how do the google return million or billions of data in just a millisecond or the speed of insert like SQLBULKCOPY.