k6__
asked on
Can someone optimize this SQL ?
For MonthCounter := 1 To 12 Do
Begin
UserDate := StrToDate(IntToStr(DaysOfM onth((Mont hCounter),
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.AsStr ing);
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
Begin
UserDate := StrToDate(IntToStr(DaysOfM
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
End;
False : Begin
Sql.Add('Where CustomerCode =' + CustomerDBText.Field.AsStr
Sql.Add('And MyDate >="' + IntToStr(MonthCounter) +
'/' + '1/' + YearSpinEdit.Text + '"');
Sql.Add('And MyDate <="' + FormatDateTime('mm/dd/yyyy
End;
End;
Prepare;
Open;
{----- Do Some Other Stuff -------}
{----- Do Some Other Stuff -------}
{----- Do Some Other Stuff -------}
PQuery.Close;
PQuery.UnPrepare;
End;
End;
End;
Thanx
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/y yyy', UserDate) + '"');
if MyButton.Down=false then
Add('AND CustomerCode =' + CustomerDBText.Field.AsStr ing);
end;
Prepare;
....
.....
close;
with Sql do begin
Clear;
Add('Select * From MyDB');
Add('Where MyDate BETWEEN "' +
IntToStr(MonthCounter) + '/1/' + YearSpinEdit.Text + '"');
Add( 'And "'+FormatDateTime('mm/dd/y
if MyButton.Down=false then
Add('AND CustomerCode =' + CustomerDBText.Field.AsStr
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, [...]);
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, [...]);
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).
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).
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
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.
Add('Select * From MyDB');
to
Add('Select FieldName1,FieldName2,...,
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
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.
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
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
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
ASKER
I knew that setrange is the kind of the
speed but i want an SQL Solution...
Can i use Setrange on SQL Result ?
speed but i want an SQL Solution...
Can i use Setrange on SQL Result ?
ASKER
a typo:
setrange is the kind of the
^^^^
i meant King =)
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok..
thanks
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