Code from the table biao, according to ziduan lookup, and returns the x1, x2 two values, assign jieguo1, jieguo2

A:='emample';
For I=1 to 400 do//test run 400
The begin
ADOQuery1. Close;
ADOQuery1. SQL. Text:='select x1, x2 from biao where ziduan=' "+ a +" '"';
ADOQuery1. Open;

Jieguo1:=ADOQuery1 FieldByName (' x1). AsString;//the removed, the running time no optimization
Jieguo2:=ADOQuery1 FieldByName (' x2). AsString;//the removed, the running time no optimization

end;

Biao about 20000 data, ziduan, x1, x2 length is not more than 5 characters,
Test the above code, you want to run three and a half seconds,
The speed is too slow, Daniel, help have a look at how to solve

CodePudding user response:

ADOQuery1. Close;
ADOQuery1. SQL. Text:='select x1, x2 from biao where ziduan=' "+ a +" '"';
ADOQuery1. Open;

Jieguo1:=ADOQuery1 FieldByName (' x1). AsString;//the removed, the running time no optimization
Jieguo2:=ADOQuery1 FieldByName (' x2). AsString;//the removed, the running time no optimization
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Perform a 3 seconds?

CodePudding user response:

For ziduan field to establish a clustering index index, see speed of ascension

CodePudding user response:

A:='emample';
For I=1 to 400 do//test run 400
The begin
//the following three to write inside the loop behavior? Once found to need the assignment respectively?
ADOQuery1. Close;
ADOQuery1. SQL. Text:='select x1, x2 from biao where ziduan=' "+ a +" '"';
ADOQuery1. Open;
Jieguo1:=ADOQuery1 FieldByName (' x1). AsString;//the removed, the running time no optimization
Jieguo2:=ADOQuery1 FieldByName (' x2). AsString;//the removed, the running time no optimization

end;

CodePudding user response:

Some look not to understand the code,
This is just the table opened the 400 times!

Every time to check the results are the same, why not check only once?

CodePudding user response:

Well, yes, this is to test the code above, 400 times is equivalent to access the database, took three seconds,
(in fact, in to the client program, he enter a value, to check the database 1200 times, what I use is the code above, about 9 seconds, the time is too long),

Just in the access to the "ziduan" to build an index, speed is quick, run 400 times is about 1.5 seconds,

CodePudding user response:

In fact each cycle to find "a" is different

CodePudding user response:

reference 5 floor niejinhui reply:

well, yes, this is to test the code above, 400 times is equivalent to access the database, took three seconds,
(in fact, in to the client program, he enter a value, to check the database 1200 times, what I use is the code above, about 9 seconds, the time is too long),

Just built in access to "ziduan" index, speed is quick, run about 1.5 seconds, 400 times

This time is normal.
I suggest you adjust the direction of the

Practical applications, the query is 1200 times at one time?
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 user operation time, execute a query, speed delay won't big.
2 user operation time, perform multiple queries, the query together - first & gt; A query execution - & gt; The query result, so fast

CodePudding user response:

Write a stored procedure can a bit faster, the query in the SQL execution, only return results, so there is no need to exchange data with database, the stored procedure how to write, don't ask me, also won't, did not get,

CodePudding user response:

Slow with Delphi doesn't matter too much, you'll have to put emphasis on the SQL, indexed faster choice is indispensable, and one you use cycle to keep the open/close it spend a lot of time on the IO, the maximum extent can be best screening extracted in the range of memory in the table, and then query memory table,

CodePudding user response:

Generally speaking, the odbc database drive slower than dedicated drive a data level,

CodePudding user response:

This query 400 times 3 s not very long... The speed and DELPHI key in the background

CodePudding user response:

If the access database, optimization of the place is not how much,
You where slow place, is a frequent open, close, the second, is always need to compile and run the SQL statements,
If you want to fast speed, the use of stored procedures, stored procedures only need the incoming parameters, do not need to open, close, also do not need to compile statements every time,
But the access does not support stored procedures, want to high performance, using better database,

CodePudding user response:

references 9 f sololie response:

slow with Delphi doesn't matter too much, you'll have to put emphasis on the SQL, indexed faster choice is indispensable, and one you use cycle to keep the open/close it spend a lot of time on the IO, the maximum extent can be best screening extracted in the range of memory table, memory table, and then query

Agree with this Daniel!

CodePudding user response:

Just built in access to "ziduan" index, speed is quick, run about 1.5 seconds, 400 times [access] or 20000 records, there are 3 seconds has been good,

CodePudding user response:

A database of 20000 article, speed can be the optimization not much anymore,

CodePudding user response:

Bottleneck is the database or database engine network communication, Delphi database query millions of records and the faster than you

CodePudding user response:

Recommended three layer architecture, the use of ClientDataSet.

CodePudding user response:

Data set every time the cost of the Close and Open than query overhead, without optimization under the condition of data table, suggested that the query as select * from biao where ziduan1 in (' vlu1 ', 'vlu2), one-time query all of the data you want, and then circulating in the query, the efficiency will be much higher than your writing now

Further optimization of the efficiency, it is inside the biao to create ziduan1 non clustered index, the index to include you in several fields, but also can improve the efficiency of query

Basically a tens of thousands of data table query, query at a time, memory loop 300 times, also the efficiency of less than 1 second

CodePudding user response:

reference 15 floor lyhoo163 response:

database of 20000 article, speeds, optimize not much anymore,

Our products always in tens of millions of data table query, a return to 100 records, and requires 2 to 3 seconds out of the results, you say 20000 data, can this speed? We want to live?

CodePudding user response:

Did you understand a mistake? I think this is not a problem to optimize the database, but the problem of the concept of logic?
You can do it:

 


ADOQuery1. Close; 
ADOQuery1. SQL. Text:='select x1, x2 from biao';//I want to open a 20000 data table should slow cannot go to where? 
ADOQuery1. Open; 

A:='emample';//a value needs to change 400 times, here is not simulated, the default is this value 
For I=1 to 400 do//test run 400 
The begin 
//locating records 
ADOQuery1. Locate (' ziduan ', a, []);//this statement is found in memory, none of the database, so the speed quickly, 

//return the result of the need to 
Jieguo1:=ADOQuery1 FieldByName (' x1). AsString; 
Jieguo2:=ADOQuery1 FieldByName (' x2). AsString; 

end; 

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull