Link to home
Start Free TrialLog in
Avatar of k6__
k6__

asked on

Can someone optimize this SQL ?

For MonthCounter := 1 To 12 Do
 Begin
  UserDate := StrToDate(IntToStr(DaysOfMonth((MonthCounter),
                        StrToInt(YearSpinEdit.Text))) + '/' +
                        IntToStr(MonthCounter) + '/' +
                        YearSpinEdit.Text);

  With DataMod Do
   Begin
    With PQuery Do
     Begin
      Close;
      Sql.Clear;
      Sql.Add('Select * From MyDB');

      Case MyButton.Down Of
       True  : Begin
                Sql.Add('Where MyDate >="' + IntToStr(MonthCounter) +
                                             '/' + '1/' + YearSpinEdit.Text + '"');
                Sql.Add('And   MyDate <="' + FormatDateTime('mm/dd/yyyy', UserDate) + '"');
               End;
       False : Begin
                 Sql.Add('Where CustomerCode ='  + CustomerDBText.Field.AsString);
                 Sql.Add('And       MyDate >="' + IntToStr(MonthCounter) +
                                                  '/' + '1/' + YearSpinEdit.Text + '"');
                 Sql.Add('And       MyDate <="' + FormatDateTime('mm/dd/yyyy', UserDate) + '"');
                End;
      End;

      Prepare;
      Open;

      {----- Do Some Other Stuff -------}
      {----- Do Some Other Stuff -------}
      {----- Do Some Other Stuff -------}

      PQuery.Close;
      PQuery.UnPrepare;
     End;
   End;
 End;


Thanx

Avatar of mhervais
mhervais

what's wrong with it ? you are mono table no join... can't be better ?
the only thing you could do if your
dbengine accepts it is the use of the
*between* operator.

if your perfs are bad, better look how your indexes are made, how your dbengine manages dates conversions, and
eventually, if it does not issue recommendations about using a cast function for your date.

but the sql looks good

regards,

Marc
Optimize????? like this???

.....
close;
with Sql do begin
  Clear;
  Add('Select * From MyDB');
  Add('Where MyDate BETWEEN "' +
    IntToStr(MonthCounter) + '/1/' + YearSpinEdit.Text + '"');
  Add(                         'And "'+FormatDateTime('mm/dd/yyyy', UserDate) + '"');

  if MyButton.Down=false then
     Add('AND CustomerCode ='  + CustomerDBText.Field.AsString);
end;
Prepare;
....
I usually use string constant like

const
sqlstr = 'select * from MyDB' +
         ' where (MyDate >= "%s") and (MyDate <= %s")';

and then in code something like this

sql.text := format(sqlstr, [...]);
I usually use string constant like

const
sqlstr = 'select * from MyDB' +
         ' where (MyDate >= "%s") and (MyDate <= %s")';

and then in code something like this

sql.text := format(sqlstr, [...]);
yk: moving the customer code filtering to the last line isn't really an optimization of the sql. you may have less lines delphi code, but the sql will get executed slower.

try filtering by using the filter that skips the mosts records first, so other filters have to go through less data every time.

k6___: your statement looks quitte efficient. if this statement takes to long to execute, try adjusting the table you select on, by placing indexes on the fields you filter on (MyDate and CustomerCode).
Avatar of k6__

ASKER

So.. there isn't any optimizations left for speed up ?

the only speed up i see, is by altering the database structure (creating segmented tables, creating new indexes, etc...)

topkapi
U may change this
 Add('Select * From MyDB');
to
 Add('Select FieldName1,FieldName2,...,fieldNameN From MyDB');
WHERE
 FieldNameX is the actual FieldName what you need only.

If you perform projection, the resultset will become small.
k6
maybe you can give us som factual information :
1) what sgbd engine
2) how many records in your table
3) what indexes
4) what you intend to do with this selection
5) how you are connected (local, lan wan)
6) what issue you meet ?
7) on what machine the sgbd and the client run
8) how many simultaneous clients?

regards, Marc
Yes, what's the DBEngine? E.g. for Oracle you have to put the most filtering criteria at the end.
Avatar of k6__

ASKER

mhervais:
1) BDE - Paradox
2) from 10000 to million =)
3) All of them in Single Indexes ..
   (eg Index IndexDate contains only
    the field MyDate)
4) To Filter them into Groups using
   a Memory Table
   (this is the {----- Do Some Other Stuff -------})
5) Local
6) ?
7) Don't care.. but let's make it
   a Pentium 100 Mhz =)
8) Just One

If you are using Paradox, I recommend that you would not issue an SQL query,
but rather:
1) use TTable
2) set the appropriate index
3) use setrange for your upper and lower limits, then apply range,
4) explore your dataset from first to eof

this should increase your speed dreadfully.

regards, Marc
Avatar of k6__

ASKER

I knew that setrange is the kind of the
speed but i want an SQL Solution...
Can i use Setrange on SQL Result ?
Avatar of k6__

ASKER

a typo:
setrange is the kind of the
                ^^^^
i meant King =)
it just takes some time for the data to be selected, when you do this on a local machine, even when the query is quite efficient.

you could (if possible) move the table to a database server, wich provides a speedup.

alternativly you could try using more advanced hardware (more memory, faster cpu, faster harddisks, etc...)

topkapi

ASKER CERTIFIED SOLUTION
Avatar of mhervais
mhervais

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of k6__

ASKER

Ok..
thanks