[Solved] Slow SQL & DoEvents...

[G-Hawk]

Hi!.. I have a question for you guys... but first the data.. I'm working with SQL Server DB, under Windows 2K. The DB has around 5K of records


I have this SQL statement:

DoEvents
sql = "SELECT BeOrdenTrabajo.Orden, BeOrdenTrabajo.ID, Rack, (Fecha3TFIn - FechaWO) as TotalTime, BeOrdenTrabajo.Carga, Descarga, " _
   & " (SELECT TOP 1 BeModelos.Modelo FROM BeRequisicion,BeModelos WHERE BeOrdenTrabajo.Orden " _
   & " = BeRequisicion.orden AND BeRequisicion.Modelo = BeModelos.Modelo GROUP BY BeModelos.Modelo) as MyModel, " _
   & " (SELECT TOP 1 BeModelos.TiempoBI FROM BeRequisicion,BeModelos WHERE BeOrdenTrabajo.Orden " _
   & " = BeRequisicion.orden AND BeRequisicion.Modelo = BeModelos.Modelo GROUP BY BeModelos.TiempoBI) as MyBI, " _
   & " (DateAdd(Hour,-(SELECT TOP 1 BeModelos.TiempoBI FROM BeRequisicion,BeModelos WHERE BeOrdenTrabajo.Orden " _
   & " = BeRequisicion.orden AND BeRequisicion.Modelo = BeModelos.Modelo GROUP BY BeModelos.TiempoBI)," _
   & "(Fecha3TFIn - FechaWO))) as Dif FROM BeOrdenTrabajo WHERE  Proceso > 3 ORDER BY Dif DESC"
RSBIData.CursorLocation = adUseClient
RSBIData.Open sql, frmDisplay.eabscn, adOpenStatic, adLockOptimistic

While doing this process I'm trying to show a blinking label to indicates that the program is doing something (For that I use a timer) so I use a DoEvents just before the SQL.

What I expected was to have a runnign process, slow process, and a blinking label telling that the program is working even if it looks like is freezed. But what I have is the running process, slow of course, and a freezed label so the program looks like dead... then my questions are:...

a) Is there a way to improve the speed of the SQL statement (Right now takes around 15-20 seconds to complete)
b) What can I do to show the make the label "blink" while processing the SQL

[lintz]

Add your "blinking label" code to your timer.

[G-Hawk]

Hi Lintz... I forgot to tell that my "blinking label" code is already on the timer... the situation is that the timer is not fired while the SQL statement is running...

[lintz]

What interval is your timer set to? Does the Timer code get triggered at all?

[G-Hawk]

Well... the timer is set to a 500ms.. but the strange thing I just realized because of your question is that if I let the timer enabled then the timer gets triggered as soons as I enter the form where the timer is .. but if I set enable to false.. and then I set it to TRUE when I need it then the timer never gets triggered... .. so now I have another question... Why this is happenig?... (That took me completely unguard... )

[lintz]

One option I thought of is to move your query to the timer. (that way you'll know it will get triggered)

Dim DoQuery As Boolean
 
Sub Form_Load()
 
'Do other stuff here before query
 
Timer1.Interval = 500'set interval
Timer1.Enabled = True 'start timer
 
End Sub
 
Sub Timer1_Timer()
 
If DoQuery = False Then
DoQuery = True 'set to true so we don't continue to call it
 
DoEvents
 
sql = "SELECT BeOrdenTrabajo.Orden, BeOrdenTrabajo.ID, Rack, (Fecha3TFIn - FechaWO) as TotalTime, BeOrdenTrabajo.Carga, Descarga, " _
   & " (SELECT TOP 1 BeModelos.Modelo FROM BeRequisicion,BeModelos WHERE BeOrdenTrabajo.Orden " _
   & " = BeRequisicion.orden AND BeRequisicion.Modelo = BeModelos.Modelo GROUP BY BeModelos.Modelo) as MyModel, " _
   & " (SELECT TOP 1 BeModelos.TiempoBI FROM BeRequisicion,BeModelos WHERE BeOrdenTrabajo.Orden " _
   & " = BeRequisicion.orden AND BeRequisicion.Modelo = BeModelos.Modelo GROUP BY BeModelos.TiempoBI) as MyBI, " _
   & " (DateAdd(Hour,-(SELECT TOP 1 BeModelos.TiempoBI FROM BeRequisicion,BeModelos WHERE BeOrdenTrabajo.Orden " _
   & " = BeRequisicion.orden AND BeRequisicion.Modelo = BeModelos.Modelo GROUP BY BeModelos.TiempoBI)," _
   & "(Fecha3TFIn - FechaWO))) as Dif FROM BeOrdenTrabajo WHERE  Proceso > 3 ORDER BY Dif DESC"
RSBIData.CursorLocation = adUseClient
RSBIData.Open sql, frmDisplay.eabscn, adOpenStatic, adLockOptimistic
 
DoQuery = False ' reset
Timer1.Enabled = False ' stop timer
End If
 
DoEvents
'add label flickering code here
 
End Sub

[szlamany]

That query has some problems.

SELECT TOP 1 is never used with GROUP BY - they are mutually exclusive...

Why all those sub-queries - they are very expensive.

5000 rows is a truly small amount for MS SQL to handle.

You should concentrate on re-working that QUERY in QUERY ANALYZER so that it runs as it should - in under a second - and the problem will go away.

Also - since this is MS SQL SERVER start using proper JOIN syntax.

Don't do:

FROM TABLEA, TABLEB

instead do:

FROM TABLEA LEFT JOIN TABLEB ON TABLEB.KEYCOL=TABLEA.KEYCOL

and also make sure that you have proper indexes on the JOIN columns.

[G-Hawk]

@ Lintz:
I just left some code, without the SQL statement but... still once I enable the timer never gets triggered!!! I'm confused... why this could be happening?... I don't know what is the problem with the timer?... there is something I have been missing regarding to that?...

@ Szlamany:
Tnaks for the reply I also believe that the query must have something wrong... so I follow your advice... but now that I'm trying to check the Query the SQL Query Analyser tells me that there is an error "Identifier to long maximum length is 128"... any idea about that (I have never use SQL Query Analyser)

[G-Hawk]

@ Szlamany... I already make it work... I mean I could parse my Query but what's next I couldn't see any "improve query"... or something like that...

[Ishamael]

Were all those sub querys really needed? probley sigh..
theres no way I will try figure that out.

Why have you only used the table name on certain columns?
"SELECT BeOrdenTrabajo.Orden, BeOrdenTrabajo.ID, Rack,"

Don't kill me if this is a stupid question... but why do you select the value from
BeModelos.Modelo
which you already got while joining to
BeRequisicion.Modelo

It is hard for me without knowing your tables/data etc... sigh to new to this
wouldnt this work? (kill me for not using join later.)

(SELECT BeRequisicion.Modelo
FROM BeRequisicion, BeOrdenTrabajo
WHERE BeOrdenTrabajo.Orden  = BeRequisicion.orden 
GROUP BY BeRequisicion.Modelo)

[szlamany]

@ Szlamany... I already make it work... I mean I could parse my Query but what's next I couldn't see any "improve query"... or something like that...

There is no improve query function in QUERY ANALYZER.

It's just a place where it's easy for you to type in a QUERY and run it. Make simple changes to it and get it to fulfill your needs.

What I expected back from you was the start of a better structured query - with proper JOIN/ON syntax - with no sub-queries. Once you got the basic query I was going to ask some questions about the table design and help you get the other data - the stuff you sub-query for now...

It's not going to happen instantly - but you need to take the first step - which is to start with a simpler query in QA that we all can understand and help you expand.

[G-Hawk]

@ Szlamany...

Tnx! for your advices... I already came up with this Query which, as you said, run in less than a second..

"SELECT BeOrdenTrabajo.Orden, ID, Rack, (Fecha3TFIn - FechaWO) as TotalTime, Carga, Descarga, Modelo, 
(datediff(Hour,FechaBin,FechaBout)) as NewBI, 
(DateAdd(Hour,- (datediff(Hour,FechaBin,FechaBout)),(Fecha3TFIn - FechaWO))) as Dif
FROM BeOrdenTrabajo LEFT JOIN BeRequisicion ON BeOrdenTrabajo.Orden = BeRequisicion.orden
WHERE Proceso > 3 ORDER BY Dif DESC"

I appreciate your time and support... Regards!

Hopefully I'll do the same with other Querys that I have under the same circumstances and I'll then be OK... but if I'm unable to solve I know who can help me with that... thanks...

Now the only thing missing is the reason why the timer is not fired once I enable it... (even though I might not need it anymore It would be nice to know)...

[szlamany]

I'm very impressed - that query is so much easier to look at and as you said - runs in a second - that's the way to go!

[G-Hawk]

Tnx!... I would make this thread solved and I will open a different one for the timer issue, which was not suppossed to be a problem when I started this thread... anyway thanks to all...

C-ya!