TZQuery - query problem

[czuga]

Hello,
I have problem, I use TZQuery to get 11000 records and it takes about 3 minutes. Query is very simple, like:

Code: Select all

SELECT * from table WHERE something;

I think, it is too long. I have RAID0 (2xWD800JB) and AthlonXP2700++ I have no idea what is wrong.

[fduenas]

plz post wha database are you using, zeoslib version, delphi version.
and if possible a sample project

[Terence]

It mainly also depends if you database server is hosted locally or on another machine, then you have to include the network traffic to transmit the 11.000 records.
I am very impressed that you nee such huge amount of results, who would like to seev such many rows in a grid?. What do you do on client side with 11.000 records? Somwhere it would be best to reduce that amount with more selection on server side, whatever there is possible.

[czuga]

...so, I use zeosdbo-6.1.5-stable version, delphi 7 enterprise environment, database server is hosted locally (MySQL 4.0.18).

Code: Select all

function TE_Courses.getCourseListFromDateToDateSort(from, to: TDateTime; idDispatcher, idDriver: Integer): TCourseList;
var
  query  : string;
  z :  TZQuery;

begin
   query := 'SELECT idCourse, idDriver,idDispatcher,...
    'FROM courses cs , streets s WHERE cs.idStreet=s.idStreet'+
    ' AND cDate>='''+
    IntToStr(YearOf(odDaty))+'-'+
      IntToStr(MonthOfTheYear(odDaty))+'-'+
        IntToStr(DayOfTheMonth(odDaty)) + ''' AND cDdate <='''+
              IntToStr(YearOf(doDaty))+'-'+
                IntToStr(MonthOfTheYear(doDaty))+'-'+
                  IntToStr(DayOfTheMonth(doDaty)) + '''';

   if (idDriver>0) then query := query + ' AND idDriver= '+
        IntToStr(idDriver);

   if (idDispatcher>0) then query := query + ' AND idWorker= '+
        IntToStr(idDispatcher) + '  ORDER BY cDate DESC, cHour DESC';

    E_BazaDanych.executeQuery(query );

   z := E_BazaDanych.BDZapytanie;


   Result := self.getListOfCourse(z);

end;

...and

Code: Select all

getListOfCourse

implementation:

Code: Select all

function TE_Courses.getListOfCourse(z: TZQuery): TCourseList;
var
  fields declaration
  lz : TCourseList;
begin
   max:= z.RecordCount;
   if (max = 0) then
        Result := nil    { nil, jesli lista pusta }
   else
    begin   { jesli nie, to stworzenie obiektu... }
        lz := TListaZlecen.Create();
        for i:=1 to max do
          begin      { ... i dodanie elementow }

              lz.addCourse(
			......
			......

                );
              z.Next;
          end;
        Result := lz;
    end;
end;

....and I have no idea why this query takes so many minutes (about 3 minutes)...has anybody any idea?

[Terence]

No idea, but some general things to ckeck.
If this query('s - i mean one with idriver and one without) is executed many times i would define a memeber variable for each and prepare it.
I would propose using params instead of 'sql text' field then to update the values for the actual run.
And of course check your index's for the field in the 'where ' part. The perfoemance mainly depends on the db design if having huge data amount.
Futhermore you can cross check the zeos db time results with another query engine like 'phpmyadmin'
Just some thoughts.

[guyvdb]

don't use recordcount (z.recordcount)!
It is slow.

In place create a function e.g. 'getRecCount(q: TZQuery): integer'

select statement: 'select count(*) from courses'