delay with vb.net and sql 2k server?

[T Perkins]

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

 

[Nice Chap]

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.

 

[Nice Chap]

Also, in your case you can use Command.ExecuteScalar instead of
ExecuteReader

 

[Tom Leylan]

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?

 

[T Perkins]

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.

 

[Chris Dunaway]

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.

 

[Tom Leylan]

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

 

[Shane]

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.