CODEWITHSUNDEEP
sql-servercoldfusioncoldfusion-9coldfusion-8
Daniel
Daniel

Reputation: 35724

coldFusion and SQL interaction slow initial connection

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?

Upvotes: 0

Views: 620

Answers (3)

Daniel
Daniel

Reputation: 35724

My guess is that the reason the first connection is slow is that that's when the connection is first established, and the credentials get checked. After the initial connection is made, the subsequent queries are much faster. It's just a theory though...

Here is some more info:

I'm not sure what the default is set as, but might be worthwhile to look into it more.

Upvotes: 0

Desdoyer
Desdoyer

Reputation: 82

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.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

The delay is most likely created by compiling and creating an execution plan. I suggest using stored procedures.

Upvotes: 1

Related Questions