Question

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 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.

Answer 1

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

Answer 2

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).

友情链接