Can't edit the title, so we have to a new post to continue to ask:
I asked you how to use the database concurrency query, memory, virtual memory and handle is basically the same, just query speed too slow, TMD, zha added 2 months after the original data, virtual memory is a surge in (program changed a bit, about the architecture, for avoiding the same table at the same time, speaking, reading and writing, polling queries when the original data with a pair of thread lock key areas of the east),
1, what causes the virtual memory surge?
2, the stored procedure can realize dynamic query (i.e., the table name, column name and every query query condition changes), how to operate?
3, because the database is from original database replication, itself does not support SQL cache dependency and SQL cache database level depends on how to set up? Best by Delphi7 set, if not, through setting can also be SQL2005,
CodePudding user response:
Sorry, architecture is changed worse, originally only create a TADOQuery, call me instead of polling a create 1 time, changed back to, then continue to 2 to 3, the problem becomes how to improve the efficiency of dynamic SQL query? "" PS: title zha changed?CodePudding user response:
Create Procedure AAA@ TableName string, @ Column string, @ the where the string of the Column can be combination of multiple fields, where can also be multiple conditions combination, hope can be solved.
CodePudding user response:
That if a stored procedure parameters changed, if we need to recompile? If a recompile, write SQL with Delphi7, again to the SQL Server 2005, a single query data volume is not big, the database can response speed and difference?
The other, to help look at the performance monitor (winxp3) screenshots can show? Can you find the performance optimization entry point? And transactions per second (the transactions) the most great in 15, why can't monitor active transactions?
CodePudding user response:
See the data, if read from the database cache data, faster than reading data from the original table, that if I am going to read before polling query data table (a total of four, are built by the month, the earliest starting in May this year, after the monthly increase in theory, and deposited a set of data every 5 minutes (1-12), I want to say is that a considerable amount of data) through stored procedures or other means to put these tables (partial or full) write cache, and then by Delphi7 dynamic cache table to write SQL read, this method is feasible? How to operate?CodePudding user response:
The thread execution function the Execute () in ms SQL SERVER memory-resident command line statements for.
With qry2 do begin
The close;
SQL. The Clear;
SQL. The Add (' DECLARE @ db_id int, @ tbl_id int ");
SQL. The Add (' USE DRN ');
SQL. The Add (' SET @ db_id=db_id (' + quotedstr (' DRN) + ') ');
SQL. The Add (' SET @ tbl_id=OBJECT_ID (' + quotedstr (' [DRN]. [dbo] [lldata_ '+ tabledate +'] ') + ') ');
SQL. The Add (' DBCC PINTABLE (@ db_id, @ tbl_id) ');
The open;
end;
.
Results the run times wrong: the class EDataBaseerror exception message CommandText does not return a result set,
Before the query to join
.
Qry2. ExecuteOptions:=[eoExecuteNoRecords];//query does not return to record the executed
With qry2 do begin
.
Results and report EOleException class exception message parameter type is not correct, or are not within an acceptable range, or conflict with other parameters',
What reason is caused?
CodePudding user response:
ExecSQL ~ ~ ~CodePudding user response:
Use ExecSQL, return a value of 1, as if parameter is not supported,Another, the boss wanted to use the local server + remote server + collector three-tier architecture, collector of all data uploaded to the local server, want to generalization, intend to do with XML file carrier, weak weak ask, how to write XML content writing database synchronization, written to the database using the TADOQuery Insert Insert, as if not return data sets, with TADOQuery. Savetofile to report errors,