Solved

Timers

Posted on 2004-04-02
10
444 Views
Last Modified: 2010-04-05
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
0
Comment
Question by:SCOTT78
10 Comments
 
LVL 11

Accepted Solution

by:
calinutz earned 150 total points
ID: 10739381
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
 
LVL 17

Expert Comment

by:mokule
ID: 10739458
Using DisableControls of TDataSet before operations and  EnableControls may also be helpfull.
But as calinutz says it is difficult to guess.
0
 

Author Comment

by:SCOTT78
ID: 10739795
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:wframsay
ID: 10740546
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
 

Author Comment

by:SCOTT78
ID: 10773038
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
 
LVL 1

Expert Comment

by:wframsay
ID: 10774009
Probably not.  However, doing your "processing" as part of the query might enhance the performance.
0
 
LVL 1

Assisted Solution

by:Ridey
Ridey earned 150 total points
ID: 11196055
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now