delay with vb.net and sql 2k server?

  • Thread starter Thread starter T Perkins
  • Start date Start date
T

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
 
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 said:
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.
Click to expand...

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?
 
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.
 
I guess I'm the one stuck asking... how can that be returning the maximum id
value? Why are you ordering it by "desc"?
Click to expand...

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 Dunaway said:
He's ordering it in DESCending order, in which case the first item should
contain the max id. Perhaps I misunderstood your question?
Click to expand...

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.
Click to expand...

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
 
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.
 
Back
Top