Query slow on first run but fast on subsequent calls - Subsonic 2.0

We've come across an issue when we open our application and run a Subsonic Select the select takes a while to return data. Any subsequent calls to the same select run very quickly. The equivilent T-SQL also runs very quickly. It's as though SubSonic is caching something on the first connection and reusing this on subsequent connections. The queries run fast until we close the application and then open a new one, again the first run takes a while but all subsequent calls开发者_Python百科 are fast.

Any ideas on this? Do we need to upgrade to a newer version?

I couldn't find any mention of this anywhere.

The app is a windows app although we have a similar issue with a web app. The query is a simple select with an inner join:

Dim status As New subsonicdal.Status("statusCode", "active")
Dim clientPreferencesDataSet As DataSet = New SubSonic.Select(subsonicdal.ClientPreference.ClientIdColumn, subsonicdal.ClientPreferenceType.AssetOrUnitColumn, subsonicdal.ClientPreferenceUsage.UsageTypeColumn) _
                                                                .From(subsonicdal.ClientPreference.Schema) _
                                                                .InnerJoin(subsonicdal.ClientPreferenceType.Schema) _
                                                                .InnerJoin(subsonicdal.ClientPreferenceUsage.Schema) _
                                                                .Where(subsonicdal.ClientPreference.ClientIdColumn).IsEqualTo(clientId) _
                                                                .And(subsonicdal.ClientPreference.StatusidColumn).IsEqualTo(status.Statusid).ExecuteDataSet()

Basically if I put a breakpoint on Dim clientPreferencesDataSet As DataSet line, execute the line it takes around 6 seconds. If I break on the line again it is immediate (under a second).

The ClientPreferences has foreign Integer keys to ClientPreferenceUsage & ClientPreferenceType there are around 70 records in ClientPreferences, 8 in ClientPreferenceUsage and 12 in ClientPreferenceType. Its a very simple structure. If I run the SQL that Subsonic generates in Mgt Studio it is immediate. I have tried InnerJoining the Status table, using strings for the fields returned but there is no difference.

Two things come to mind:

• Possibly the database connection is made at first use (lazy)
• Or if it is a prepared query, after the first run, the execution plan is cached and subsequent calls will be faster

SubSonic doesn't cache anything - it just tries to execute whatever query you throw it. Is this a web app? If so (which you probably know) the load time is very slow at first.

The more detail you can offer the better - such as a test that pinpoints the load issue as well as your DB structure (if you can).