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

delay with vb.net and sql 2k server?

P: n/a
no matter how i code it, there is always 3-5 second pause on retrieving data
from an sql 2000 server. this pause only happens on the first try, after
that every access is back to being normal. is there a known issue with this
or am i doing something dumb?

im using the following code:
what im attepting to do is when a user exits a combo box, the app retrieves
the highest id, then adds 1 and displays it
the problem is that if the users selects a media that has 5000 plus records
the first time around it takes almost 5 seconds before a number is found.
the first time i saw this i thought it locked up

Private Sub cmbMedia_OnLeave(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmbMedia.Leave
cmd.CommandText = "SELECT MAX(id) FROM inventory WHERE media = '" & media &
"'"

cmd.Connection = conn.GetSqlConnection

dr = cmd.ExecuteReader

dr.Read()

If dr.IsDBNull(0) = False Then

Me.txtId_num.Text = dr(0).GetSqlInt16 + 1

Else

MessageBox.Show("There are no items that match the media type",
"Warning...", MessageBoxButtons.OK, MessageBoxIcon.Warning)

End If

dr.close()

End sub

thanks in advance

tony


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


P: n/a
Hi Perkins,

Max function invariably applies a table level lock and hence is not only
time consuming but doesn't fit well when there are multiple users. Change
the query to

"SELECT id FROM inventory WHERE media = '" & media & "' order by id desc"

this will not place any lock and it is much quicker.
Nov 20 '05 #2

P: n/a
Also, in your case you can use Command.ExecuteScalar instead of
ExecuteReader
Nov 20 '05 #3

P: n/a
"Nice Chap" <Ni******@PlasmaDyne.com> wrote...
Hi Perkins,

Max function invariably applies a table level lock and hence is not only
time consuming but doesn't fit well when there are multiple users. Change
the query to

"SELECT id FROM inventory WHERE media = '" & media & "' order by id desc"

this will not place any lock and it is much quicker.


I guess I'm the one stuck asking... how can that be returning the maximum id
value? Why are you ordering it by "desc"? And I guess the big question is
when did we stop using MAX() when you need to know the maximum value?
Nov 20 '05 #4

P: n/a
i have tried it that way as well. but i still get the delay on the first hit
to the database. after the first hit, every differt way responds normally.
even if i just select one record i have a delay of maybe 3 seconds. but in
any case the first time there is always a delay.

i have not be able to find any information on this but im wondering if the
delay is cause by authentication. once im authentiacted im fine. not sure on
this but its the best theory i have, but i have yet to read it anywhere.

"Nice Chap" <Ni******@PlasmaDyne.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
Hi Perkins,

Max function invariably applies a table level lock and hence is not only
time consuming but doesn't fit well when there are multiple users. Change
the query to

"SELECT id FROM inventory WHERE media = '" & media & "' order by id desc"

this will not place any lock and it is much quicker.

Nov 20 '05 #5

P: n/a
On Mon, 10 Nov 2003 17:19:53 GMT, Tom Leylan wrote:

I guess I'm the one stuck asking... how can that be returning the maximum id
value? Why are you ordering it by "desc"?


He's ordering it in DESCending order, in which case the first item should
contain the max id. Perhaps I misunderstood your question?

As to using MAX(), I cannot answer your question.

--
Chris

To send me an E-mail, remove the underscores and lunchmeat from my E-Mail
address.
Nov 20 '05 #6

P: n/a
"Chris Dunaway" <dunawayc@_lunchmeat_sbcglobal.net> wrote...
He's ordering it in DESCending order, in which case the first item should
contain the max id. Perhaps I misunderstood your question?
Oops, got it :-) (duh) At the very least it could also be limited to
single row right? That might be implementation specific however. I can't
imagine returning (potentially) multiple-thousand rows in order to find the
maximum ID is a good alternative.
As to using MAX(), I cannot answer your question.


I'm pretty certain the OP's delay is due to an initial need to interpret the
SQL and/or caching on subsequent calls. Just my guess however...

Tom
Nov 20 '05 #7

P: n/a
I have my application make a simple connection to the DB
on startup. There is a pause on starting the app, which
is generally accepted better by the customer.

-----Original Message-----
i have tried it that way as well. but i still get the delay on the first hitto the database. after the first hit, every differt way responds normally.even if i just select one record i have a delay of maybe 3 seconds. but inany case the first time there is always a delay.

i have not be able to find any information on this but im wondering if thedelay is cause by authentication. once im authentiacted im fine. not sure onthis but its the best theory i have, but i have yet to read it anywhere.
"Nice Chap" <Ni******@PlasmaDyne.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
Hi Perkins,

Max function invariably applies a table level lock and hence is not only time consuming but doesn't fit well when there are multiple users. Change the query to

"SELECT id FROM inventory WHERE media = '" & media & "' order by id desc"
this will not place any lock and it is much quicker.

.

Nov 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.