Immediate processing of large recordset with VB

I have a table with roughly 165,000,000 records. Within
this table is a subset of roughly 6,000,000 records I
need to retrieve and process sequentially with some VB
code. These records are identified by a numeric code in
1 field. There is an index built on this field. When I
run a simple query, it takes roughly 20 minutes to return
the subset of records.
My question: Is there a way to have SQL return record 1
immediately to the VB program so it can begin
processing? As it stands when I issue the ADO command to
build the recordset I am continuosly hitting timeouts. I
assume that's because the SQL server is building the
entire recordset to return to the client. While I can
conceiveably increase my timeout(s), I'd like the client
to start processing whe the SQL server has pulled the
first record, or block of 1000 or something, rather than
wait for the entire recordset to be assembled.
Thanks for your help.You can use:
SELECT ...
FROM ...
WHERE...
OPTION (FAST n)
Where n will be number of rows in the first batch that will returned
quicker. Note that using this option can slow down overal execution time of
your query, and by a not inconsiderable amount.
Why exactly do you need to process 6 million rows sequentially in VB? There
are many things that can be done quicker in T-SQL than in VB with a set that
large.
Jacco Schalkwijk
SQL Server MVP
"DB" <dave_nospam_blair@.adelphia.net> wrote in message
news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
>I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.|||See clause OPTION of the select statement. There is an option FAST (xxx) tha
t
can help. If you are using ADO, also recommend to increase recordset propert
y
CacheSize.
Can you explain what are you trying to do?, may be somebody can come with a
set-based solution.
AMB
"DB" wrote:

> I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.
>|||You can use TOP to retrieve a batch of a predefined number of rows:
SELECT TOP 10000 WITH TIES col1, col2, ...
FROM YourTable
WHERE key_col > @.key_col
/* @.key_col = The primary key of the last value processed */
AND code = 1
ORDER BY key_col
Also consider whether your VB process would be better done in SQL or
located in the middle tier if it isn't already.
David Portas
SQL Server MVP
--|||> I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client.
Yeah, some of the time that you have to wait might involve query processing
time but the rest of the time involves all that data flowing across the
network onto your PC. I assume that the memory usage on the PC that the VB
program is running on goes up as the 6 million rows are returned.
When you "process" a row of data is there any way to determine that the
specific row is processed? If so you could do something like:
SELECT TOP 1000
FROM YourTable
/*pull the rows (based on the numeric code) that you are interested in*/
WHERE SomeColumn = @.TheNumericCode
/*this pulls the rows that have not been processed*/
AND HasThisRowBeenProcessed = 0
/*important to make sure that the rows are processed in the correct order*/
ORDER BY ...
Keith
"DB" <dave_nospam_blair@.adelphia.net> wrote in message
news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
> I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.|||I wonder is possible to indetify that subset of data? I mean, using an
indentity field.
In any case, carrying up that subset into a temp table and working with that
?
See you,
"Keith Kratochvil" wrote:

> Yeah, some of the time that you have to wait might involve query processin
g
> time but the rest of the time involves all that data flowing across the
> network onto your PC. I assume that the memory usage on the PC that the V
B
> program is running on goes up as the 6 million rows are returned.
> When you "process" a row of data is there any way to determine that the
> specific row is processed? If so you could do something like:
> SELECT TOP 1000
> FROM YourTable
> /*pull the rows (based on the numeric code) that you are interested in*/
> WHERE SomeColumn = @.TheNumericCode
> /*this pulls the rows that have not been processed*/
> AND HasThisRowBeenProcessed = 0
> /*important to make sure that the rows are processed in the correct order*
/
> ORDER BY ...
> --
> Keith
>
> "DB" <dave_nospam_blair@.adelphia.net> wrote in message
> news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
>

Image data type to character string

I have a table with an image datatype field.

When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?

Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..

If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.

Image data type to character string

I have a table with an image datatype field.

When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?

Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..

If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.

Image Data and PATINDEX function

Hi,
I need to retrieve the offset of some text stored as image. PATINDEX does not work for image data type. What other choice do I have?
Thank you,
Amar DasAre you searching for the text or the binary pattern of the text?

Use the image datatype for storing binary data and (n)text datatype for storing text.|||Thanks Paul.

I am searching for the text. Unfortunately the table is created by my client and I can not alter it.|||Hum... Books Online seems to have some errors...

try this...
create table #Tmp(f1 text, f2 image)
insert into #Tmp values('Test number 1','Test number 1')
insert into #Tmp values('Test number 2','Test number 2')
insert into #Tmp values('Test number 3','Test number 3')
insert into #Tmp values('Test number 4','Test number 4')
select charindex(cast('r 2' as varbinary),f2),* from #Tmpsql

Image control and DB

I wanna know how to retrieve & insert an image from/to a Sql Server database.

usingimage control or any other control

I've done that with windows formpicture box.

As that was explained in MS tutorials

Code….

--------------

Dim msAsNew MemoryStreamPictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)Dim arrImage()AsByte = ms.GetBuffer

ms.Close()

Dim strSQLAsString = _ "INSERT INTO Emp (EmpName,EmpSalary,Picture)" & _ "VALUES (@.EmpName,@.EmpSalary,@.Picture)" Dim cmdAsNew SqlCommand(strSQL, ConnEmp) With cmd .Parameters.Add(New SqlParameter("@.Picture", _ SqlDbType.Image)).Value = arrImage .Parameters.Add(New SqlParameter("@.EmpName", _ SqlDbType.NVarChar)).Value = txtEmpName.Text .Parameters.Add(New SqlParameter("@.EmpSalary", _ SqlDbType.Decimal)).Value = txtEmpSalary.Text EndWith

cmd.ExecuteNonQuery()

--------------

But with a web form'simage control IDO NOTknow how to do it

I real appreciate your help

Thank you

Here's an article on it:http://aspalliance.com/articleViewer.aspx?aId=140

Hope this helps...

-Damien