By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,168 Members | 2,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,168 IT Pros & Developers. It's quick & easy.

SPEED QUESTION

P: n/a
In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader ;select
* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).

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


Nov 20 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
"Agnes" <ag***@dynamictech.com.hk> wrote in news:OxcbxEedEHA.3476
@tk2msftngp13.phx.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********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/
Nov 20 '05 #2

P: n/a
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***@dynamictech.com.hk> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...
In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader ;select * from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).

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

Nov 20 '05 #3

P: n/a
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;select * from invoicedetail where ???, conSea)
thanks

"Tom Dacon" <td****@community.nospam> 在郵件
news:ui*************@TK2MSFTNGP11.phx.gbl 中撰寫...
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***@dynamictech.com.hk> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...
In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader

;select
* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).

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


Nov 20 '05 #4

P: n/a
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.HeaderKey in (select
InvoiceHeader.PrimaryKey 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***@dynamictech.com.hk> wrote in message
news:%2****************@TK2MSFTNGP12.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;select * from invoicedetail where ???, conSea)
thanks

"Tom Dacon" <td****@community.nospam> 在郵件
news:ui*************@TK2MSFTNGP11.phx.gbl 中撰寫...
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***@dynamictech.com.hk> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...
In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader

;select
* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).

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



Nov 20 '05 #5

P: n/a
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
Nov 20 '05 #6

P: n/a
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**********@planet.nl> 在郵件
news:%2****************@TK2MSFTNGP10.phx.gbl 中撰寫...
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

Nov 20 '05 #7

P: n/a
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
Nov 20 '05 #8

P: n/a
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.InvoiceLineNumber

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***@dynamictech.com.hk> wrote in message
news:%2****************@TK2MSFTNGP12.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;select * from invoicedetail where ???, conSea)
thanks

"Tom Dacon" <td****@community.nospam> 在郵件
news:ui*************@TK2MSFTNGP11.phx.gbl 中撰寫...
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***@dynamictech.com.hk> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...
In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader

;select
* from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).

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




Nov 20 '05 #9

P: n/a
I have had a similar problem before and used the overloaded dataAdapter.fill 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.

OleDbDataAdapter1.Fill(DsDataSet1, 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.fill(mydataset).

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


Nov 20 '05 #10

P: n/a
Depending on your needs you may not need a dataset and may just need a
datareader....
Disconnected datasets are nice if you need them but for small scale apps
with no potential of becoming giants, just connecting to SQL via datareader
and using SQL command objects is preferable in my opinion and even
quicker....and doesn't have those concurrency issues.

Be sure you need the features before just using what is being pushed....

It's like XML....yeah it's great for something but it really bytes for
others.... if your a big company or sending through a firewall or
comunicating cross platform it is great... but for saving files---many files
formats are a WHOLE LOT SMALLER AND EFFICIENT. So although certain
technologies are the craze they may not always be the best solution to your
particular app and espcially on smaller scale.

Even though you have 50,000 records, you may or may not want to do
datasets.... lot it over well and decide--only you can.

With datasets your connects to SQL server aren't tied up as much and you do
cut down on usage of the server by keeping local data for the user to look
at... but if you have plenty of connections and no worries there...simple
client server with data reader and SQL Command work fine for me.

My 2 cents worth,

Shane
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...
In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader ;select * from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).

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

Nov 20 '05 #11

P: n/a
Depending on your needs you may not need a dataset and may just need a
datareader....
Disconnected datasets are nice if you need them but for small scale apps
with no potential of becoming giants, just connecting to SQL via datareader
and using SQL command objects is preferable in my opinion and even
quicker....and doesn't have those concurrency issues.

Be sure you need the features before just using what is being pushed....

It's like XML....yeah it's great for something but it really bytes for
others.... if your a big company or sending through a firewall or
comunicating cross platform it is great... but for saving files---many files
formats are a WHOLE LOT SMALLER AND EFFICIENT. So although certain
technologies are the craze they may not always be the best solution to your
particular app and espcially on smaller scale.

Even though you have 50,000 records, you may or may not want to do
datasets.... lot it over well and decide--only you can.

With datasets your connects to SQL server aren't tied up as much and you do
cut down on usage of the server by keeping local data for the user to look
at... but if you have plenty of connections and no worries there...simple
client server with data reader and SQL Command work fine for me.

My 2 cents worth,

Shane
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...
In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader ;select * from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).

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

Nov 20 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.