Site slow to connect to database

[LarrySteele]

I'm working on my first ASPX site - coming in with extensive experience with ColdFusion.

We're working on a secure intranet site connecting to an Oracle 10g database. The first page is a search page. They enter their criteria and go to a list page that displays potential matches from their search criteria. This table only has 216k rows. The search fields are indexed and I've recently run stats on the table. When I run the search query, I get fast results. However, the first time I open this page, it takes ~30 seconds to load, even if it's just one record. After I've used the site for some time, the response times speed up dramatically - well under a second to load. If I'm off the site for awhile, upon return, I'm back to the ~30+ second load time.

I've search Google to see if I can find a suggestion for troubleshooting, but no luck. I found a number of false leads - most tended to be issues with queries or bringing back too much data. I didn't find any that matched my experience where I have a fast query (running at the database) return very few (one) row that renders very slow at first, but speeds up dramatically during visit, only to slow to a crawl on next visit.

Here are a couple of details that I'm sure you'll need to know:

Provider: Oracle.DataAccess.Client
IIS Application Pool: ASP.NET v4.0

Query:

select field1,
       field2,
       field3,
       field4,
       field5,
       field6,
       field7,
       case when substr(field8,-2) in ('ABC','XYZ') and nvl('" + ThisSession.Current.ad_role1 + @"','NO') = 'NO' then 'N' 
            when instr('" + ThisSession.Current.ad_role2 + @"','|' || field8 || '|', 1, 1) = 0 then 'N' 
            else 'Y' end as can_view,
       field9,
       field10
from workhist.wkhst_search
where field1 like :arg_field1
  and field4 like :arg_field4
  and field5 like :arg_field5
order by field4, field5

This query runs fast when running in SQLPlus or TOAD. I also runs fast if I've been using the site for some time - and that includes passing different parameters so I'm not relying on browser cache to ~speed up~ response. It's just dog slow at first access.

Any suggestions on where to look or how to troubleshoot this would be appreciated.

 

[MarkSweetland]

I'd say it depends on how you call the query from your application as far as the connection delay. Not sure it's only a database connection problem, but may be a result of both the connection and the query.

The connection will be pooled in IIS and kept alive for a period of time and may be the reason it seems to be quicker. Even though you're sending diferent paramerters to query on, the connection is still there and therefore available without having to reconnect to the server.

Also, anytime you query on a table using a LIKE clause, the database engine will have to do a full table scan. A table scan is the reading of every row in a table and is caused by queries that don’t properly use indexes. Table scans on large tables take an excessive amount of time and cause performance problems. Sometimes you will notice a quicker second query because the database has cached the table scan the optimized query and can reuse to perform subsequent queries.

Both have a finite time they will hold the cached objects and will need to be recreated on later visits.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[LarrySteele]

Mark - thanks for the response. Yes, all symptoms point to the DB connection as the bottleneck. Is there anything I can do to speed up connection initialization and/or extending time the connection stays alive? Then again, I'm defining the connection in web.config; should I have defined it in IIS instead?

Oh - love the tagline.

 

[MarkSweetland]

I'm not an Oracle expert by any means, but you may look at your connection string itself. Here's a link to a descriptive Oracle connection procedure that may shine some light on where you can make changes.

I also wonder if you are using an Oracle proxy account to connect or are you using some sort of windows autentication or trusted connections. If the latter, some of the delay could be caused by the account validation by the domain controller.




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[LarrySteele]

Thanks Mark. That's the pattern I'm using to connect to Oracle. I suspect this may have more to do with the infrastructure than anything else, which might explain why I haven't been able to find others with similar symptoms. I figured it was worth the shot seeing if anyone had experienced something similar with DB connections from ASP.NET.