search

flourishlib / flourish-classes

The class files for Flourish
http://flourishlib.com
182 stars 78 forks source link

traslatedQuery Execution Time Issue #200

Open superphonic opened 10 years ago

superphonic commented 10 years ago

Doesn't look like these issues are actively being looked at any more, but I'll log my problem in case someone has any idea.

I have an MSSQL Database and am using translated queries.

When I run the following query:

$result = $db->translatedQuery("SELECT * FROM industries LIMIT 5");

This is the is the execution time

437.34908103943 milliseconds
0.43734908103943 seconds

Takes a loooooong time as you can see...

When I directly connect to the database using PDO and run the query below:

$sql = "SELECT TOP 5 *, CAST(\"industries\".\"industry\" AS VARBINARY(MAX)) AS fmssqln__industry FROM industries";
$sth = $conn->query($sql);

This is the execution time:

85.041999816895 milliseconds
0.085041999816895 seconds

Much quicker.

The SQL I used for the second query is the translated query that was spat out by the translatedQuery() function in the first step so the actual query is identical.

My question is - what is the translated query function doing to take this long. If I have 3 or 4 translated queries on one page it takes 2 to 4 seconds for the page to load !?!?!

How do I speed up the translated query function? It is too slow to be usable in it's current form.

Thanks