Query too Slow

[guillermo88]

QUERY TAKES TOO LONG

I have a form with a grid
I have an invoice table.
I want to display records that match certain criteria:
First_name, last_name
I also have indices for each field first_name and last_name.
The problem that I am having is it takes too long that query and sometimes I have a message from w2k (program not responding).

By the way table is fully optimize

Here is the piece of code on the click event of the button search

=SYS(3054,12,'xx')

thisform.grid2.RecordSource = ""

SELECT COL_AGT, INV_NO, DATE, REM_TEL1, BEN_FIRST, BEN_LAST, BEN_TEL1, TOT_INV, REM_FIRST, REM_LAST;
FROM INVOICE;
WHERE INVOICE->rem_first = thisform.txtRem_first.value ;
AND INVOICE->rem_last = thisform.txtRem_last.value ;
ORDER BY invoice->rem_tel1;
INTO CURSOR C_RES

thisform.Grid2.RecordSource = 'C_RES'

thisform.Grid2.Column1.width = 50
thisform.Grid2.Column2.width = 70
thisform.Grid2.Column3.width = 70
thisform.Grid2.Column4.width = 150
thisform.Grid2.Column5.width = 150
thisform.Grid2.Column6.width = 150
thisform.Grid2.Column7.width = 150

thisform.lblRecords.Caption = ALLTRIM(STR(_TALLY)) + ' Records'
thisform.lblRemitter.Caption = ALLTRIM(C_INV->rem_first) + ' ' + C_INV->rem_last

MESSAGEBOX(XX)

This line of code in the DE is
Thisform.grid1.recordsource = “”


Thanks

 

[Mike Gagnon]

guillermo88

I'm just wonder if the SYS(3054) is slowing your query down, also if you want to avoid grid reconstruction problem the line is ok
thisform.grid2.RecordSource = "" && OK
thisform.Grid2.RecordSource = 'C_RES' && OK

But do you need to then resize the grid columns on top of that?

This line of code in the DE is
Thisform.grid1.recordsource = “”

Why is this in the DE and in what event?



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[guillermo88]

Hey Mike,

But do you need to then resize the grid columns on top of that?

The reason why I resize it's because when I re-query the columns change the width.

This line of code in the DE is in the init event
Thisform.grid2.recordsource = “”

If I don't, grid will try to load the table.

SYS(3054)
The reason I use that was just to know if the idexes were being used.

I tried this before but recordsourcetype of the grid was 4(SQL Statement)

Thanks

 

[Mike Gagnon]

guillermo88

Take a look at faq814-1813 "How to avoid grid reconstruction". If you follow those instructions, you should have to reset your grid widths.

[/i]If I don't, grid will try to load the table[/i]

What table is this?

You may want to look at Using Rushmore in queries in the help file to help you optimize your SQL statement.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[guillermo88]

I added a table in my DE (Invoices)

 

[Mike Gagnon]

guillermo88

I'm not sure how to solve this, its a design issue. Typically what you could do (in the init of form ) is run your same SQL statement but with a condition that will never be met. You will end up with a cursor that is empty, and that way you won't have the grid reconstruction problem.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[guillermo88]

I going to create another method with dummy select statement in it so it will run only once. Thanks.

Something else Is it any difference in using RecordSourceType=Alias and SQL Statement?

I have another with very similar criteria and it works fine

 

[Mike Gagnon]

Something else Is it any difference in using RecordSourceType=Alias and SQL Statement?

Not that I'm aware of, except maybe that writing it in the recordsource can leave to problems if its a long one and you cannot see the whole statement, I would write it out in the command window on a single line and test it first then paste it in.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[guillermo88]

The problem seems to loading the the grid with the cursor.
I just tried the same select statement from the command line an it works very fast.

 

[Mike Gagnon]

guillermo88

Now try it from the init of the form.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[guillermo88]

This is my dummy query in the init event of the form.
I added a msgbox to check how long it takes the query. And the msgbox comes very fast the I click the msgbox button and that's where it takes time. In this case now it takes time to load the form.

SELECT COL_AGT, INV_NO, DATE, REM_TEL1, BEN_FIRST, BEN_LAST, BEN_TEL1, TOT_INV, REM_FIRST, REM_LAST;
FROM INVOICE;
WHERE INVOICE->inv_no = -99999;
INTO CURSOR C_RES

MESSAGEBOX("Sql finished",0,"&quot

thisform.Grid2.RecordSource = 'C_RES'

 

[Mike Gagnon]

guillermo88

Is this a network setup?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[guillermo88]

Yes, all the tables are sitting on a network, and the exe is in every local computer

 

[Mike Gagnon]

guillermo88

You can try two things.
1. Do you have a lot of tables in your dataenviroment of the form? If not try removing them from it and run to form (with the SQL running in the init only) see if that makes a difference.
2. Or leave your tables in the dataenviroment and set the NoDatOnLoad to .T. on each table to see if that will make a difference.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[craigsboyd]

guillermo88,

I would change your where clause on dummy cursor to:

SELECT COL_AGT, INV_NO, DATE, REM_TEL1, BEN_FIRST, BEN_LAST, BEN_TEL1, TOT_INV, REM_FIRST, REM_LAST;
FROM INVOICE;
WHERE .F. ;
INTO CURSOR C_RES

...how many records are in this cursor, I mean when you say it is taking the grid forever to load about how many records?

...I think you are right that it is not the query that is taking so long it is something else since you say the exact same query from command window on network tables is really quick, my question is what lead you to believe that it was the Query that was taking so long in the first place? Was everything decent speed before you added query and grid?

Try something like:

nSecondsStart = SECONDS()

SELECT COL_AGT, INV_NO, DATE, REM_TEL1, BEN_FIRST, BEN_LAST, BEN_TEL1, TOT_INV, REM_FIRST, REM_LAST;
FROM INVOICE;
WHERE INVOICE->rem_first = thisform.txtRem_first.value ;
AND INVOICE->rem_last = thisform.txtRem_last.value ;
ORDER BY invoice->rem_tel1;
INTO CURSOR C_RES

nSecondsMiddle = SECONDS()

thisform.Grid2.RecordSource = 'C_RES'

nSecondsEnd = SECONDS()

MESSAGEBOX("QUERY TOOK: "+ TRANSFORM(nSecondsMiddle - SecondsStart) + " sec" + chr(13) + ;
"GRID TOOK: "+ TRANSFORM(nSecondsEnd - nSecondsMiddle) + " sec&quot

...to see how long each part is taking or wrap other statements with similar to see where bottleneck is.

Ok, last thing I can think of to try is run the Coverage Profiler...before you even call your form:

SET COVERAGE TO c:\WhatIsGoingOn.log &&Turns on logging

...and in the activate event of your form (I'm assuming the bottleneck is before this, if not then put this next line of code behind a button or something that you can click) put:

SET COVERAGE TO &&turns off logging

then run your application from inside VFP and when you are finished go in the command window and put:

DO (_COVERAGE) WITH "c:\WhatIsGoingOn.log" &&analyze log

...sometimes the coverage profiler takes a while to bring up the log, so you can alternately just open the WhatIsGoingOn.log with notepad or Excel or something and look at it. It is comma delimited and the first column is the execution time. You should be able to see which lines of code are taking the longest.

Slighthaze = NULL

 

[guillermo88]

Hey Mike,
thanks a lot for your tips.

I went back to my original queries and that seems to be the problem.

First of all I have to grids, the first grid will show custmomers with transactions date = date() and amount > 2000

thisform.grid1.RecordSource = ''

nSecsSta = SECONDS()

* 1st Query
SELECT rem_first, rem_last;
FROM INVOICE;
WHERE date = DATE();
ORDER BY rem_last, rem_first;
GROUP BY rem_last, rem_first;
INTO CURSOR C_REM

* 2nd Query
SELECT I->rem_first, I->rem_last, sum(I->tot_inv) as Tot_Snd, count(*) as ord;
FROM INVOICE AS I, C_REM AS T;
WHERE I->rem_first = T->rem_first;
AND I->rem_last = T->rem_last;
ORDER BY Tot_Snd desc;
GROUP BY I->rem_last, I->rem_first;
HAVING tot_snd > 2000;
INTO CURSOR C_INV

thisform.grid1.RecordSource = 'C_INV'

Query took 7.531
grid took 0


And this the code for the second grid

thisform.grid2.RecordSource = ''

SELECT COL_AGT, INV_NO, DATE, REM_TEL1, BEN_FIRST, BEN_LAST, BEN_TEL1, TOT_INV, REM_FIRST, REM_LAST;
FROM INVOICE;
WHERE INVOICE->rem_first = C_INV->rem_first ;
AND INVOICE->rem_last = C_INV->rem_last ;
AND INVOICE->date >= thisform.date;
ORDER BY invoice->rem_tel1;
INTO CURSOR C_RES

thisform.Grid2.RecordSource = 'C_RES'

All 3 queries are based on the invoice table and they have idexes on rem_last and rem_first

Thanks again