Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Slow SQL Server query in Delphi

Posted on 2009-04-04
6
615 Views
Last Modified: 2013-11-23
I am creating a simple Delphi 7 app to compare some records in an Access table with those in a SQL Server 2008 table. The Access table has 2600 records and I loop through each of them looking for a record in the SQL table with the same value in 2 fields. For some reason it is running very slowly and when I put some timing code in I found that after the first 50 or so records, the time to excecute the query keeps increasing. Eventually it was taking more than 1.5 seconds for each instance. The code is below.

tblContacts is the Access table and Qry1 runs on the SQL Server. The query is cursor location clUseClient, cursor type ctOpenForwardOnly, lock type ltReadOnly. The Attorneys table is indexed on a combination of the 2 search fields (FirstName + LastName).

I found that if I used a Locate on the SQL Server table it was about 5 times faster. But why would the query be so slow and keep getting slower?

tblContacts.First;
  while not tblContacts.EOF do begin
    Qry1.Close;
    Qry1.SQL.Add('SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName));
    Qry1.SQL.Add('AND Lastname=' + QuotedStr(tblContactsLastName.AsString));
    Qry1.Open;
    // Code to add matching names to a list will be here
    tblContacts.Next;
  end;

Open in new window

0
Comment
Question by:galcott1
  • 4
6 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24069737
You would get much better performance if you setup a linked server against your MS-Access database in SQL Server 2008..

No looping code is gonna ourperform a set based query as such..


0
 
LVL 4

Accepted Solution

by:
jamerslong earned 125 total points
ID: 24069751
Try this and see what happens, if you keep on adding stuff to the SQL.Text it will just keep slowing it down.
Closing the Query donsent clear the text box if i am mistaken.

try moving the Qry1.Close and Open outside of the loop unless your worried about the data changing durring the search.

    Qry1.SQL.Text := 'SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName);
    Qry1.SQL.Add('AND Lastname=' + QuotedStr(tblContactsLastName.AsString));

tblContacts.First;
    Qry1.Open;
  while not tblContacts.EOF do begin
    Qry1.SQL.Text := 'SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName);
    Qry1.SQL.Add('AND Lastname=' + QuotedStr(tblContactsLastName.AsString));
    // Code to add matching names to a list will be here
    tblContacts.Next;
  end;
    Qry1.Close;

Open in new window

0
 
LVL 4

Expert Comment

by:jamerslong
ID: 24069758
opps small change
    Qry1.Open;
tblContacts.First;
  while not tblContacts.EOF do begin
    Qry1.SQL.Text := 'SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName);
    Qry1.SQL.Add('AND Lastname=' + QuotedStr(tblContactsLastName.AsString));
    // Code to add matching names to a list will be here
    tblContacts.Next;
  end;
    Qry1.Close;

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 4

Expert Comment

by:jamerslong
ID: 24069776
sorry i'm tired and realized i am mistaken
you cant move the Open and close outside fo the loop or it wont update so just inore that.

so you are just using the SQL to filter and taking the results to another place...

then you should do this
tblContacts.First;
  while not tblContacts.EOF do begin
    Qry1.Close;
    Qry1.SQL.Text := 'SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName); // instead of add you replace the current text
    Qry1.SQL.Add('AND Lastname=' + QuotedStr(tblContactsLastName.AsString));            //then you add the next line
    Qry1.Open;                                                                          //Open the Database
//do the magic                        
//tblContacts.Next
  end;

Open in new window

0
 
LVL 4

Expert Comment

by:jamerslong
ID: 24069791
Qry1.Close;
    Qry1.SQL.Text := 'SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName) + ' AND Lastname=' + QuotedStr(tblContactsLastName.AsString); // you
Qry1.Open;
could also do this.
0
 

Author Closing Comment

by:galcott1
ID: 31566674
Thanks for this. I realized that I made a dumb mistake by not clearing the SQL text between each repetition. I've done this kind of thing hundreds of times and I always include "SQL.Clear'; this time I forgot.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Correct Thousand and decimal separator in sql server 2008 3 30
Help Required 3 107
Add a step to a system backup job 6 16
Amazon RDS migrate to SQL Server 3 24
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

809 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