Slow Query

[skatamatic]

I have a fairly simply query that is taking a long time when run from C# via MySql.Data.MySqlClient.MySQLCommand but the same query runs about 100x faster when I run it through the MySQL Workbench (GUI utility)

Here's the code in C#:

tCom.CommandText = "(SELECT ID, Timestamp FROM jobdata WHERE `Timestamp` >= "
                   + (dCurTimeStart - dTolerance).ToString() 
                   + " ORDER BY ID ASC LIMIT 1) UNION " 
                   + "(SELECT ID, Timestamp from jobdata WHERE `Timestamp` <= "
                   + (dCurTimeStart + dTolerance).ToString() + " ORDER BY ID DESC LIMIT 1) " 
                   + "ORDER BY ABS (`Timestamp` - " 
                   + dCurTimeStart.ToString() 
                   + ") DESC LIMIT 1";

And in the MySQL Workbench in a .SQL file:

(SELECT ID, Timestamp FROM jobdata WHERE Timestamp >= 40967.44041508102 - 0.002 ORDER BY ID ASC LIMIT 1) UNION (SELECT ID, Timestamp FROM jobdata WHERE Timestamp <= 40967.44041508102 + 0.002 ORDER BY ID DESC LIMIT 1) ORDER BY ABS(Timestamp - 40967.44041508102) DESC LIMIT 1

Method 1 takes about 13 seconds, method 2 takes 0.l seconds. As far as I can tell they are the exact same query - the parameters in the C# snippet are set to the same numbers used in the raw sql method (dTolrance is 0.002 and the dCurTimeStart is 40967.44041508102).

Both methods work on the same DB table and connection. The 'jobdata' table is indexed by timestamp and ID in a binary tree.

Anyone have any ideas?

[thines01]

Have you tried this as a stored proc?
It seems as if it should run better than 13 seconds -- especially since the other runs quickly.

I would also want to know if you ran each half as a separate query, which one would take longer.

I also assume the timestamp column is indexed, right?

[Mitja Bonca]

Maybe it would be better if you would use Parameters, and pass these variables inside of your query to them.
Example:

tCom.CommandText = "(SELECT ID, Timestamp FROM jobdata WHERE Timestamp >= @param1 ...";
tCom.Parameters.Add("@param1", SqlDbType.DateTime).Value = dCurTimeStart - dTolerance;
//..

This is only an exmaple, you add all the other parameters.

[skatamatic]

Have you tried this as a stored proc?
It seems as if it should run better than 13 seconds -- especially since the other runs quickly.

I would also want to know if you ran each half as a separate query, which one would take longer.

I also assume the timestamp column is indexed, right?

No, haven't tried it as a stored proc yet. But I'm not running it as a stored proc in the MySQL workbench so that shouldn't be affecting anything.

Both halves take just about the same amount of time to execute with the timestamp value provided. It would likely vary based on how near the center of the table it is though.

I should mention there's about 1.7 million records in the table - and yeah it's indexed by both timestamp and ID.

@MitjaBonca - How is that faster? Or is it just something to try? The way I understand it, the MySQLCommand object simply sends the exact same query text that it contains to the SQL server for parsing. Using parameters would essentially do the same, would it not?

[thines01]

The parameters should only make a difference AFTER the first call.
Most databases issue a "prepare" behind the scenes, so subsequent runs will work by ID and parameters rather than sending/parsing the whole query.

I hope MySQL works that way(at least the newer versions).

[skatamatic]

The parameters should only make a difference AFTER the first call.
Most databases issue a "prepare" behind the scenes, so subsequent runs will work by ID and parameters rather than sending/parsing the whole query.

I hope MySQL works that way(at least the newer versions).

Well that's good to know - I'll modify to use parameters since this will get executed quite a few times in a loop.

But this doesn't help my issue lol. The tests I did only used 1 call, and sending the full query text to the server should be a very very small part of the total 13 seconds it takes to execute (in the order of microseconds I would think).

[Ketsuekiame]

The issue itself may be because of .NET.

Something to try...
Run the query in MySQL Workbench and make a note of how long it takes to display the results, not simply the search time.

.NET will do things such as assigning the returned data into arrays, parsing it, possibly some reflection as well...
A good test of that is to see where the processor is spending its time. Is it the mysqld process or is it your .NET application?

[skatamatic]

The issue itself may be because of .NET.

Something to try...
Run the query in MySQL Workbench and make a note of how long it takes to display the results, not simply the search time.

.NET will do things such as assigning the returned data into arrays, parsing it, possibly some reflection as well...
A good test of that is to see where the processor is spending its time. Is it the mysqld process or is it your .NET application?

That's kind of what I'm thinking. I may just put the whole thing in a stored proc. I'll see if I have time to test out the resource consumption today. The time I was going by was in fact the total time it took to display (MySQL Workbench automatically benchmarks both the display and the fetch time).

[Ketsuekiame]

I've tried to reproduce the problem using a database from one of our clients, but I've been unable to do so. It runs in .NET as quickly as Workbench.

I did use a method to try and fake a large amount of records (approx. 7million) and the Workbench took 0.017ms to run the query and 327 seconds to parse and display it.
In contrast, the .NET application crashed.

So as far as my own personal testing, I've not been able to get much further with this :(