SPEED QUESTION

[Agnes]

In my form load()
Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader ;select
* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fi ll(mydataset).

As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...

[Lucas Tam]

"Agnes" <ag***@dynamict ech.com.hk> wrote in news:OxcbxEedEH A.3476
@tk2msftngp13.p hx.gbl:

As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...

Becuase you're loading 50,000 records into a dataset... it's sucking up all
your memory.

I wouldn't use a dataset if there are >20,000 rows.

Use a datareader instead.

--
Lucas Tam (RE********@rog ers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Tom Dacon]

I'll just bet that it does seem quite slow. You're bringing over a ton of
data, little of which the user is ever actually going to be looking at.
So...how about not loading the whole works all at once? Maybe you might load
just the most recent data, perhaps enough to fill your grid or whatever
you're using, and then wait until your user wants to 'page back' or do a
filtered search on records, using some user-specified criteria. Then go back
and load the data of interest. I know it's a little more work, but nothing
comes for free.

No offense intended, but this is kind of like those people who add 100,000
items to a drop-down list box and wonder why it doesn't work too pretty
good.

Trying hard not to be irritable and not quite succeeding,
Tom Dacon
Dacon Software Consulting

"Agnes" <ag***@dynamict ech.com.hk> wrote in message
news:Ox******** ******@tk2msftn gp13.phx.gbl...

In my form load()
Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader ;select * from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fi ll(mydataset).

As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...

[Agnes]

this time I understand . But I got another question. in my Invoiceheader,I
can load the current month's data
but .. in my invoicedetail, there is no field to store the"date"
How can I select from invoicedetail with the "date condition" ....

Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader from
month = currentmonth;se lect * from invoicedetail where ???, conSea)
thanks

"Tom Dacon" <td****@communi ty.nospam> ¦b¶l¥ó
news:ui******** *****@TK2MSFTNG P11.phx.gbl ¤¤¼¶¼g...

I'll just bet that it does seem quite slow. You're bringing over a ton of
data, little of which the user is ever actually going to be looking at.
So...how about not loading the whole works all at once? Maybe you might load just the most recent data, perhaps enough to fill your grid or whatever
you're using, and then wait until your user wants to 'page back' or do a
filtered search on records, using some user-specified criteria. Then go back and load the data of interest. I know it's a little more work, but nothing
comes for free.

No offense intended, but this is kind of like those people who add 100,000
items to a drop-down list box and wonder why it doesn't work too pretty
good.

Trying hard not to be irritable and not quite succeeding,
Tom Dacon
Dacon Software Consulting

"Agnes" <ag***@dynamict ech.com.hk> wrote in message
news:Ox******** ******@tk2msftn gp13.phx.gbl...

In my form load()
Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader

;select

* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fi ll(mydataset).

As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...

[Tom Dacon]

Making a guess here, since I don't know your schema, and no guarantees
without compiling and running it, but how about something like:

Dim daSeaInvoice As New SqlDataAdapter(
"select * from InvoiceHeader where month = currentmonth;
select * from invoicedetail where Invoicedetail.H eaderKey in (select
InvoiceHeader.P rimaryKey where month = currentmonth),
conSea)

thanks
The double select on InvoiceHeader kind of hurts if you do it as an ad hoc
query, but it might not be so bad as a stored procedure with month as the
parameter. The stored procedure could probably re-use the first result set's
primary keys instead of running another query.

Tom Dacon
Dacon Software Consulting

"Agnes" <ag***@dynamict ech.com.hk> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. .. this time I understand . But I got another question. in my Invoiceheader,I
can load the current month's data
but .. in my invoicedetail, there is no field to store the"date"
How can I select from invoicedetail with the "date condition" ....

Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader from
month = currentmonth;se lect * from invoicedetail where ???, conSea)
thanks

"Tom Dacon" <td****@communi ty.nospam> ¦b¶l¥ó
news:ui******** *****@TK2MSFTNG P11.phx.gbl ¤¤¼¶¼g...

I'll just bet that it does seem quite slow. You're bringing over a ton of data, little of which the user is ever actually going to be looking at.
So...how about not loading the whole works all at once? Maybe you might

load

just the most recent data, perhaps enough to fill your grid or whatever
you're using, and then wait until your user wants to 'page back' or do a
filtered search on records, using some user-specified criteria. Then go

back

and load the data of interest. I know it's a little more work, but nothing comes for free.

No offense intended, but this is kind of like those people who add 100,000 items to a drop-down list box and wonder why it doesn't work too pretty
good.

Trying hard not to be irritable and not quite succeeding,
Tom Dacon
Dacon Software Consulting

"Agnes" <ag***@dynamict ech.com.hk> wrote in message
news:Ox******** ******@tk2msftn gp13.phx.gbl...

In my form load()
Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader

;select

* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fi ll(mydataset).

As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...

[Cor Ligthert]

Hi Agnes,

Can you tell us what is the reason you load that bunch of data.

Keep in mind that a dataset is disconnected so when you use this as well for
updating you will get probably more and more overhead and especially
concurrency problems (when it is a multiuser environment).

Cor

[Agnes]

I don't want to load so much data, I am starting my 3rd .net form
Every textbook teach us 'select * from myTable" and the
daInvocie.fill( dsInvoice)
It seems "load all the data first" ,so i am questioned about it.

"Cor Ligthert" <no**********@p lanet.nl> ¦b¶l¥ó
news:%2******** ********@TK2MSF TNGP10.phx.gbl ¤¤¼¶¼g...

Hi Agnes,

Can you tell us what is the reason you load that bunch of data.

Keep in mind that a dataset is disconnected so when you use this as well for updating you will get probably more and more overhead and especially
concurrency problems (when it is a multiuser environment).

Cor

[Cor Ligthert]

Hi Agnes,

Do it than directly in the right way.

"Select myclientadres, myclientname from invoice
where myclientId = @ myclientId"

And than use the SQLparameters

http://msdn.microsoft.com/library/de...classtopic.asp

I hope this helps?

Cor

[Bob]

Almost always when presenting data you will have to supply two essential
elements to your user interface: header search and detail presentation.
Supply criteria to the user to search through all those invoice headers,
like date, open/closed status, vendor, etc. (I typically supply a default
condition of showing only the top 300 rows if no search condition is
provided, sorting by create date descending when applicable). When the user
clicks on a row, have another section of your form load the invoice details
for that particular header.

If you still want to show invoice details based on a search (and not an
individual header), the query should look something like this:

SELECT TOP 300 d.*
FROM InvoiceHeader h
INNER JOIN InvoiceDetail d
ON d.InvoiceID = h.InvoiceID
WHERE
h.InvoiceDate = <your date variable>
AND <any other search conditions>
ORDER BY h.InvoiceID, d.InvoiceLineNu mber

I put in the TOP 300 because users don't want to page through a large result
set - that's what a search feature is for. You can tell your users in a note
on the form that the result set has been truncated when necessary ("only the
top 300 search hits shown"). If a user really does want to see all 200,000
rows, this is a report, something to be printed; not a form. As such I never
user a datareader, because my queries never return large result sets.

As a side note, I don't recommend having any SQL at all in compiled code,
use stored procedures if you can; they supply a useful layer of abstraction,
boost performance, and allow you to make DB changes without having to
recompile your deployment. Also, take some time to buy a book or two on SQL
and really learn it. It will really make a big difference.

HTH,
Bob

"Agnes" <ag***@dynamict ech.com.hk> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

this time I understand . But I got another question. in my Invoiceheader,I
can load the current month's data
but .. in my invoicedetail, there is no field to store the"date"
How can I select from invoicedetail with the "date condition" ....

Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader from
month = currentmonth;se lect * from invoicedetail where ???, conSea)
thanks

"Tom Dacon" <td****@communi ty.nospam> ¦b¶l¥ó
news:ui******** *****@TK2MSFTNG P11.phx.gbl ¤¤¼¶¼g...

I'll just bet that it does seem quite slow. You're bringing over a ton of data, little of which the user is ever actually going to be looking at.
So...how about not loading the whole works all at once? Maybe you might

load

just the most recent data, perhaps enough to fill your grid or whatever
you're using, and then wait until your user wants to 'page back' or do a
filtered search on records, using some user-specified criteria. Then go

back

and load the data of interest. I know it's a little more work, but nothing comes for free.

No offense intended, but this is kind of like those people who add 100,000 items to a drop-down list box and wonder why it doesn't work too pretty
good.

Trying hard not to be irritable and not quite succeeding,
Tom Dacon
Dacon Software Consulting

"Agnes" <ag***@dynamict ech.com.hk> wrote in message
news:Ox******** ******@tk2msftn gp13.phx.gbl...

In my form load()
Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader

;select

* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fi ll(mydataset).

As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...

[Justin.Fisher]

I have had a similar problem before and used the overloaded dataAdapter.fil l method. You can tell the fill method the starting record and how many records you would like to return.

I use this all the time to show **LOTS** of data to the user, when the user gets close to the end of the data I have loaded I go and pull more data from the DB.

OleDbDataAdapte r1.Fill(DsDataS et1, intStartRec, intNumRecs, "MainDB")

Hardest part of this is figuring out where the user is and loading the appropriate records but still not that hard and it allows you to configure the number of records you are dealing with if performance becomes a problem. I.E. intNumRecs = 1000 or intNumRecs = 10

Regards,
Justin

"Agnes" wrote:

In my form load()
Dim daSeaInvoice As New SqlDataAdapter( "select * from InvoiceHeader ;select
* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fi ll(mydataset).

As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...