I'm noticing that the first query my web app does to the SQL server that is hosted locally is quite slow. Does anyone have experience with this and know what I can do to improve the performance/latency.
I've got a local testing server with a CF9, whereas the active server is using CF8. In both cases the SQL is in the same machine, and there is a slight SQL version difference: 10.5 RTM vs 10.0.5 SP3. (Though the production server is also running another sql database 9.0 at a different IP)
It looks like the queries are fast, but the initial connection is slow, any ideas on things i can try to improve this?
We are having the same problem here. The first query of every single request is taking significantly longer than any of the next queries.
We have created two tables in SQL Server to illustrate the situation. Both tables, tableA and tableB, are identical. One is a copy of the other.
If we execute this:
<cfquery datasource="test" name="queryA"><!--- This query takes 104 milliseconds --->
SELECT * FROM tableA WHERE id = 1
</cfquery>
<cfquery datasource="test" name="queryB"><!--- This query takes 3 milliseconds --->
SELECT * FROM tableB WHERE id = 1
</cfquery>
But, if I revert change the order of the queries, the slower query is still the first one:
<cfquery datasource="test" name="queryB"><!--- This query takes 104 milliseconds --->
SELECT * FROM tableB WHERE id = 1
</cfquery>
<cfquery datasource="test" name="queryA"><!--- This query takes 3 milliseconds --->
SELECT * FROM tableA WHERE id = 1
</cfquery>
As you can see, the difference is important.
Queries are very simple, querying by primary key and against tables that don't have more than 10 records.
We have ColdFusion 10 here, but it's also happening with ColdFusion 9 and MS SQL Server 2008.