galcott1
asked on
Slow SQL Server query in Delphi
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 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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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
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;
Qry1.Close;
Qry1.SQL.Text := 'SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName) + ' AND Lastname=' + QuotedStr(tblContactsLastN ame.AsStri ng); // you
Qry1.Open;
could also do this.
Qry1.SQL.Text := 'SELECT * FROM Attorneys WHERE Firstname=' + QuotedStr(FirstName) + ' AND Lastname=' + QuotedStr(tblContactsLastN
Qry1.Open;
could also do this.
ASKER
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.
No looping code is gonna ourperform a set based query as such..