[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

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

0
k6__
Asked:
k6__
  • 5
  • 5
  • 3
  • +3
1 Solution
 
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
 
k6__Author Commented:
Ok..
0
 
mhervaisCommented:
thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now