Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Timers

Posted on 2004-04-02
10
Medium Priority
?
452 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
7 Comments
 
LVL 11

Accepted Solution

by:
calinutz earned 600 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 600 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

810 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