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

LVL 2
k6__Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mhervaisCommented:
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
0
yk030299Commented:
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;
....
0
XANCommented:
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, [...]);
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

XANCommented:
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, [...]);
0
topkapiCommented:
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).
0
k6__Author Commented:
So.. there isn't any optimizations left for speed up ?

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

topkapi
0
yk030299Commented:
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.
0
mhervaisCommented:
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
0
ptmcompCommented:
Yes, what's the DBEngine? E.g. for Oracle you have to put the most filtering criteria at the end.
0
k6__Author Commented:
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

0
mhervaisCommented:
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
0
k6__Author Commented:
I knew that setrange is the kind of the
speed but i want an SQL Solution...
Can i use Setrange on SQL Result ?
0
k6__Author Commented:
a typo:
setrange is the kind of the
                ^^^^
i meant King =)
0
topkapiCommented:
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

0
mhervaisCommented:
1) you can use setrange on a native sql engine, the data on which you issue the setrange must be specified in the indexfieldname. I have no idea of the perf for this function, but I think that with native sql engines, it translates the setrange into a where clause comparable with what you write in a query

2) Although BDE handles SQL, it is not optimized for it.

if you want to whange engine, I advise you to use non bde replacements.

you can find many of them here :
http://www.kylecordes.com/bag/index.html   
3) If you stay an paradox and you want speed use setrange for easy requests (like you showed us in your question), and keep the usage of sql for more complex ones wher you can accept greater delays.
4) I am sorry but rjecting my answer will bring you no miracle :-))

regards

Marc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
k6__Author Commented:
Ok..
0
mhervaisCommented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.