slow select command

[sandy771]

I have a table with about 150K rows which I access via a query component.

The path column is a varchar(250) and indexed

If I put a break point on the execute statement in the code below I see that the function takes about 25 seconds to return

Code: Select all

	MyQuery3->Close();
	MyQuery3->SQL->Clear();
	MyQuery3->SQL->Add("SELECT * FROM TABLE1 ORDER BY PATH);
	MyQuery3->Execute();

If I then open a MySQL commond prompt and run the following command, it returns in 0.02 seconds

SELECT * FROM TABLE1 ORDER BY PATH LIMIT 50;

I have fetchall = false

It seems to me that the problem is with the MyDAC component - Any ideas why this might be so and what i can do to get around it

[Antaeus]

When you open a query in FetchAll=False mode, a new connection to the server is created. So open time in FetchAll=False mode includes time to establish one more connection to the server. How much time your application spends to establish a connection to the server?

[Bernhard Geyer]

It seems to me that the problem is with the MyDAC component - Any ideas why this might be so and what i can do to get around it

No, it's a problem of the MySQL-Server which doesn't support server-sided curser so you need to get all data to the client.

And 150k Records * 250 Bytes = 37,5 MB Data only for your path-column.

FetchAll=False needs a extra connection and doesn't help you if you want to use the data in a existing transaction.