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

Question

Hi,

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.

Thanks

Craig

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

Answer 3

Sorry for the delay in replying Rob. 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).