I agree. When you go to the library for information on a subject, do you
fetch the entire shelf of books at once--or (in your case) the entire
library, stuff it into your Volkswagen beetle and try to get home to read
them all before your library sends the cops after you?
Consider that ADO (and all of the data access interfaces) are "query"
interfaces. They are designed to let you ask questions (query) the database.
The database is designed and optimized to answer questions--not act as a
file server. If find yourself fetching a million rows to process them as a
block (and there are applications that must do this (like graphics and
cartography)), then I usually recommend that this processing go on at the
server using an Extended Stored Procedure and rolled-up data be returned to
the client via file IO. If you're simply moving data from one source to
another, ADO is the poorest possible choice short of paper tape. DTS or BCP
are designed and tuned to move bulk data. So, you have to process the data
before it's committed to the database. Never post unvalidated data to a
production table--post it to a work table and use a stored procedure to
revalidate the rules and criteria--then post the valid data to the server.
Does this put a big load on the server? Yup it does. That's why we build
servers that have multiple processors tons of RAM and fast hard drives. It's
almost always faster to process bulk data in this way--orders of magnitude
faster.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Marina" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Like I said, the issue is the command timeout. So you need to set it on
> the SqlCommand object. This is not related to IIS or anything like that, I
> would expect the same behavior in a windows app.
>
> So you should be able to retrieve 1 million records. But you probably want
> to re-examine your requirement to get 1 million records into memory all at
> once.
>
> "sam" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I had set timeout at following :
>> - web.config
>> - ISS 6.0
>> - ASP.Net page
>>
>> But it is seem un-retrievable for millions record....
>>
>> I also notice that MS SQL Server 2000 (Standard Edition) is capable to
>> store over millions but unable to retrieval via stored procedure or
>> ADO.Net...
>>
>> Could it possible that MS SQL Server 2000 (Standard Edition) have
>> constraint of retrieval millions record and only MS SQL Server 2000
>> (Enterprise Edition) could do so...
>>
>> Please advise...
>>
>> "Marina" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> The default timeout for a command is 30 seconds. You need to set it to a
>>> larger timeout, if you need more time to execute your query.
>>>
>>> However, do you really need to retrieve 1 million records all at once?
>>>
>>> "sam" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> When I use ASP.Net via ADO.Net to retrieve data from MS SQL Server
>>>> 2000, it is always prompt error message "Timeout" if the number record
>>>> is larger than 1 million.
>>>>
>>>> May I know what is the best practise for pool large records for my
>>>> case, please?
>>>>
>>>> Or it is limitation on ADO.Net or ASP.Net or .Net framework 1.1?
>>>>
>>>>
>>>> ASP.Net Coding
>>>> -----------------
>>>> Server.ScriptTimeout = 9000
>>>>
>>>> Dim constr_GL_3a_2 As String = "server='SQLSVR'; user id='sa';
>>>> password='sa'; Database='ERP_ARCHIVAL';Connect Timeout=1200"
>>>> Dim sqlcon_GL_3a_2 As System.Data.SqlClient.sqlconnection = New
>>>> System.Data.SqlClient.sqlconnection(constr_GL_3a_2)
>>>> Dim sqlcmd_GL_3a_2 As System.Data.SqlClient.SqlCommand = New
>>>> SqlCommand()
>>>> Dim sqlda_GL_3a_2 As New system.Data.SqlClient.sqldataAdapter()
>>>> Dim sqlprm1_GL_3a_2 As New system.Data.SqlClient.SqlParameter()
>>>> Dim sqlprm2_GL_3a_2 As New system.Data.SqlClient.SqlParameter()
>>>>
>>>> Try
>>>> sqlcmd_GL_3a_2 = sqlcon_GL_3a_2.CreateCommand
>>>>
>>>> sqlcmd_GL_3a_2.CommandText = "[General_Ledger]"
>>>>
>>>> sqlcmd_GL_3a_2.CommandType = commandtype.StoredProcedure
>>>>
>>>> sqlprm1_GL_3a_2 =
>>>> sqlcmd_GL_3a_2.Parameters.Add("@DateFrom",SqlDbType.Int,6)
>>>> sqlprm1_GL_3a_2.value = FlagFrDate
>>>>
>>>> sqlprm2_GL_3a_2 =
>>>> sqlcmd_GL_3a_2.Parameters.Add("@DateTo",SqlDbType.Int,6)
>>>> sqlprm2_GL_3a_2.value = FlagToDate
>>>>
>>>> sqlda_GL_3a_2.SelectCommand = sqlcmd_GL_3a_2
>>>> sqlcon_GL_3a_2.Open
>>>> sqlcmd_GL_3a_2.ExecuteNonQuery
>>>> Finally
>>>> sqlcon_GL_3a_2.Close()
>>>> sqlcon_GL_3a_2.Dispose()
>>>> End Try
>>>>
>>>> constr_GL_3a_2 = Nothing
>>>> sqlcon_GL_3a_2 = Nothing
>>>> sqlcmd_GL_3a_2 = Nothing
>>>> sqlda_GL_3a_2 = Nothing
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Similar Threads
