Timers

Hi,

I have written a timer application that performs the following:

Connects to an accounts package and executes a SQL query that returns all of the Sales Orders in the accounts package, the program then loops on the query and inserts all the data into a SQL server 2000 database. Also the program breaks down the data and also performs sorting functions on some of the information.

My problem is that this procedure takes around 1-2 hours to complete one loop of the data. The timer is set to fire 5 seconds after the processing the data has finished. The loop though takes along time to copy and manipulate the data. The amount of records being retrieved is around 2000.
Should the program take this long to process and is there another way of carrying out this procedure.  

I am using a TTimer component and a form. The timer is housed on the form and when the timer fires the collecting and processing of the data is performed in the OnTimer Event.

Any ideas on how to improve the performance or the program itself would be greatly appreciated as I am not a genius programmer but am fairly capable.

I am using Delphi 5 Professional, TTimer and TQuery components and a SQL Server 2000 database and also ODBC for connecting to my accounts package.

Thanks in advance
Sct
SCOTT78Technical AdvisorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
calinutzConnect With a Mentor Commented:
Do you have Indexes on the tables that you use? Having not indexes would deffinitely increase the time of any calculations. This is a way of reducing drasticly the response time.

You could also use threads to execute your task. Everytime your timer fires you could start a new thread in which you do your calculations.(Be carefull of the calculations you do though... because if the timer fires before the previous calculation finishes you might have problems whit your data). Anyways I do not know exactly what your program does but this are the possible solutions for you.
Cheers
0
 
mokuleCommented:
Using DisableControls of TDataSet before operations and  EnableControls may also be helpfull.
But as calinutz says it is difficult to guess.
0
 
SCOTT78Technical AdvisorAuthor Commented:
My program gets all Sales Orders from an accounts package using a SQL query, then loops on the querys and copies all the data returned into a SQL Server 2000 database.
This is so that I can manipulate the data without changing the original data in the accounts package.

Hope this helps
Sct
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
wframsayCommented:
What kind of Database are you querying?  You may be able to optimize performance by either doing your "breaks down the data" and "performs sorting functions" within your initial query.  You might want to select your query into a temporary table, then process the results of that into your SQL Server 2000 database.
0
 
SCOTT78Technical AdvisorAuthor Commented:
The database is part of an accounting system, I think it is a 4GL database. It is accessed through the alias in ODBC. Will sending the data to a temp table and then processing the table be quicker than looping on the recordset from the SQL statement?

Thanks
0
 
wframsayCommented:
Probably not.  However, doing your "processing" as part of the query might enhance the performance.
0
 
RideyConnect With a Mentor Commented:
The more of your process you allow your SQL Server to do the better.

May I suggest that you do a  simple transfer of data from your account package to your SQL Server before you start to do any sorting etc. The build a store procedure that you execute from your delphi app as soon as the data has completed it's transfer, in fact all of these can be done in sql itself by creating a dts package and executing it from the stored procedure, this allows all of your code to be run on your SQL serve which at the end of the day will drastically improve your time spent, as there is no overheads in the transfer update and insert of data via the ODBC drivers and no local calculations that might slow the process down as well.

In a nutshell i suggest you have a look at what SQL can do for you, it's a lot more powerful than what you are currently using it for.  SQL will also allow you to schedule your action, which will allow you to get away from the tTimer component on your delphi.

As a side not, always make sure that the first thing you do when starting a timer is to disable the timer so that it will not fire again unstill the end of the process that needs to take place, remember to re-enable the timer again at the end of your processes.

Kind Regards
0
All Courses

From novice to tech pro — start learning today.