Home
Top.Mail.Ru Yandeks.Metrika
Forum: "Bases";
Current archive: 2002.01.08;
Download: [xml.tar.bz2];

Down

An abstract question about optimization. Find similar branches


Котелок   (2001-11-30 02:16) [0]

The question is ...

The code below can be optimized somehow .... ???

And then he wrote and I got hooked. I can’t think of anything else ....: - (((

procedure TFSearch.BitBtn3Click (Sender: TObject);
was
s: string;
katId, strId: integer;
filt: string;
begin
if edit2.text <> "" then
begin
dm1.q1.close;
dm1.q1.sql.clear;
dm1.q1.sql.add ("select * from streets where name =: name");
dm1.q1.prepare;
dm1.q1.ParamByName ("name"). asstring: = edit2.text;
dm1.q1.open;
strId: = dm1.q1.fieldbyname ("idstreet"). asinteger;
end;

if edit3.text <> "" then
begin
dm1.q1.close;
dm1.q1.sql.clear;
dm1.q1.sql.add ("select * from kategor where name =: name");
dm1.q1.prepare;
dm1.q1.ParamByName ("name"). asstring: = edit3.text;
dm1.q1.open;
katId: = dm1.q1.fieldbyname ("idkat"). asinteger;
end;

sqlsearch: = "select" +
"otd.predpr," +
"otd.otdid," +
"pre.idPredpr," +
"otd.name as otdname," +
"otd.phone," +
"pre.katalog," +
"otd.kindid," +
"pre.name as orgname," +
"otd.home," +
"otd.office," +
"str.name as street," +
"(select name from katalog where Idkat = pre.katalog) as katal," +
"kat.name as kateg" +
"from" +
"otdel otd," +
"predpr pre," +
"streets str, kategor kat" +
"where otd.predpr = pre.IdPredpr and otd.kindId = kat.idkat and otd.streetId = str.IdStreet";

if edit1.text <> "" then
begin
sqlsearch: = sqlsearch + "and pre.name like" + "" "*" + edit1.text + "*" "";
end;
if edit4.text <> "" then
sqlsearch: = sqlsearch + "and otd.home =" + "" "" + edit4.text + "" "";
if edit5.text <> "" then
sqlsearch: = sqlsearch + "and otd.office =" + "" "" + edit5.text + "" "";
if edit6.text <> "" then
sqlsearch: = sqlsearch + "and otd.name like" + "" "*" + edit6.text + "*" "";
if edit7.text <> "" then
sqlsearch: = sqlsearch + "and otd.phone like" + "" "*" + edit7.text + "*" "";

dm1.qSearch.close;
dm1.qSearch.sql.clear;
dm1.qSearch.sql.add (sqlsearch);
dm1.qSearch.Prepare;

filt: = "";
if edit2.text <> "" then
filt: = "street =" "" + edit2.text + "" "";
if (edit3.text <> "") and (edit2.text <> "") then
filt: = filt + "and kateg =" "" + edit3.text + "" "";
if (edit3.text <> "") and (edit2.text = "") then
filt: = filt + "kateg =" "" + edit3.text + "" "";
dm1.qSearch.open;
if filt <> "" then
begin
dm1.qSearch.Filter: = filt;
dm1.qSearch.Filtered: = true;
end
else
dm1.qSearch.Filtered: = false;
end;



kaif   (2001-11-30 04:40) [1]

A little comment. I did not delve deeply. But constructs like "kateg =" "" + edit3.text + "" "" seem to me dangerous. You need to use ParamByName, otherwise imagine what will happen if the user enters a quotation mark in the text. You can try, I once pulled myself on this once ... And if it’s absolutely unbearable, then use "kateg =" + QuotedStr (edit3.text) better. The QuotedStr function protects you from crashing when using user quotation marks. It replaces all single quotation marks with double quotes in the text.



Котелок   (2001-11-30 08:21) [2]

Hmmm .....
I didn’t know about such an operator ...
TNX ......

ParamByName does not fit, because this query text is then used in other calls on other forms ...
For example, to sort by different fields.
When I call, I perform approximately the following actions:


dm1.qSearch.close;
dm1.qSearch.sql.clear;
dm1.qSearch.sql.add (sqlsearch);
dm1.qSearch.sql.add ("order by otd.home");
dm1.qSearch.Prepare;
dm1.qSearch.open;



kaif   (2001-12-01 02:37) [3]

ParamByName can always be enabled after you add "order by otd.home". Start using the transfer of parameters to queries (for this it is enough to label them in the text with names with two dots in front of ": myparam"). You will soon see that it is very comfortable and stylish. After you hammer the text into the SQL property and make Prepare, you pass values ​​for all parameters, for example, for the string: ParamByName ("myparam"). AsString: = .. The most important advantage of the parameters is that they pass the correct data types. For example, someone changed the regional installation on Windows from "dd / mm / yy" to "mm / dd / yy". Then all the date parameters transferred by the string will cause an error, and transferred as AsDateTime will work humanly. Then, Prepare makes sense only if you send different parameters to the request with the same text and reopen the request (read about Prepare).



ilysha   (2001-12-01 16:29) [4]

You can also optimize the code like this:

if Edit2.Text <> "" then begin with dm1.q1 do begin Close Sql.Clear; Sql.Add ("select * from streets where name =: name"); Prepare ParamByName ("name"). Asstring: = edit2.text; Open strId: = dm1.q1.FieldByName ("idstreet"). AsInteger; end; end;



Котелок   (2001-12-04 08:15) [5]

> Kaif
Gyk ... At first I did so, but then I had to refuse because
I get the parameters from a dynamically created form. For example, the user clicked the search button. The form popped up, he entered the search terms. I created this very request, and destroyed the form. And then let's say the same user decided to sort the search result by another field. And what should I do in this case? So I climbed through such a garden. The request is stored in a variable in the main form.
PS: I know how to use parambyname .... ;-) ... And honestly, I prefer this way.

> ilysha
I don’t know, at one time in a couple of articles I saw a warning not to use the with construct when working with databases. Although it may not be right ...



kaif   (2001-12-04 16:10) [6]

Well, save the parameters in some variables and resubmit them as many times as you like, after replacing the query text in relation to ORDER BY. Although, in principle, nothing bad in your version, I do not see. For optimal code does not have to chase. But for clarity and visibility is. Suddenly need a month to return to this program? Sometimes it is important to save your future work time. The best option is to generally write a universal procedure that does something similar.



Pages: 1 whole branch

Forum: "Bases";
Current archive: 2002.01.08;
Download: [xml.tar.bz2];

Top





Memory: 0.58 MB
Time: 0.033 c
7-22442
Alexei
2001-09-26 15:20
2002.01.08
How to determine the presence of a modem?


1-22142
Beginner
2001-12-20 10:42
2002.01.08
ReplaceDialog


3-21979
MAXMEM
2001-12-06 13:21
2002.01.08
DBTreeView


4-22460
Dimich
2001-11-01 08:49
2002.01.08
People HELP !!! Disher burns !!!


1-22004
KhLexa
2001-12-17 17:57
2002.01.08
Glitches





afrikaans albanian Arabic armenian azerbaijani basque belarusian bulgarian catalan Chinese (Simplified) Chinese (Traditional) croatian Czech danish Dutch English estonian filipino finnish French
galician georgian German greek haitian Creole hebrew Hindi hungarian icelandic indonesian Irish italian Japanese Korean latvian lithuanian macedonian malay maltese norwegian
persian polish portuguese Romanian russian serbian slovak Slovenian Spanish swahili Swedish thai turkish Ukrainian urdu vietnamese welsh yiddish bengali bosnian
cebuano Esperanto gujarati hausa hmong igbo javanese kannada Khmer lao latin maori marathi mongolian nepali punjabi somali tamil telugu yoruba
zulu
English French German Italian Portuguese Russian Spanish