SPROC vs Inline Times.

[dj shane]

Hey guys. I have a page which is generating fairly lengthly reports. I
understand most of the parsing time is being created from the many tr and td's
being created. I've thought about converting to divs, but don't think it will
help all that much either.

Anyway here is my question. While I'm trying to follow "best practice" and
seperate out the Data layer from the Application layer I noticed my proc taking
significantly longer to execute then my inline query.

This query expects 3 parameters. I've hardcoded them in the query for this
example.

Here is my inline query - Takes 31ms to execute and bring me 2900 results.

SELECT a.brokernumber,
rtrim(a.bound_sym) + ' ' + rtrim(a.bound_pol) + ' ' + rtrim(a.bound_mod) as
policy,
a.nameinsured,
a.quotetype,
a.dateeffective,
a.bound_premium,
a.producername,
a.controllingagent,
a.uwaction,
a.actionreason,
b.quoteaction,
c.declinereason,
d.name
FROM q_temp_tbl a
LEFT JOIN q_action_tbl b ON a.uwaction=b.action_id
LEFT JOIN q_actionreason_tbl c ON a.actionreason=c.decline_id
LEFT JOIN q_brokerinfo_tbl d ON a.brokernumber = d.brokernumber
WHERE DateEffective >= 1/1/2003
AND DateEffective <= 9/1/2005
AND Lob_ID= 3
ORDER BY a.brokernumber,dateeffective


My SPROC is essentially the same thing with the 3 parameters as @vars. Here is
how I'm calling it. My SPROC takes 700ms

<cfstoredproc procedure="QT_Report_Agency" datasource="#application.dsn#"
returncode="Yes">
<cfprocparam type="In" cfsqltype="CF_SQL_DATE" dbvarname="DateStart"
value="#CreateODBCDateTime(starting)#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_DATE" dbvarname="DateEnd"
value="#CreateODBCDateTime(enddate)#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="Lob_ID"
value="#Lob_ID#" null="No">
<cfprocresult name="rsQuote">
</cfstoredproc>

Inline: 31ms, SPROC: 700ms - I've refreshed, tried differenet machines, etc.
I'd like to keep my queries in SPROCS, but that's a pretty big % difference.

Any thoughts?

[dj shane]

I should mention, CF is running on the Same box as MSSQL2000 (Not ideal, but it's what I have to work with).

[JonathanBigelow]

That's probably a huge portion of the problem.... CF is going to glob a lot of
memory and not leave a lot for SQL to work with.

Also, are you using the same variables every time? CF will cache your query
to a degree regardless of whether you've used the cachedwitnin parameter, but
stored procs can't be cached at all. Try running your test but varying the
variables each time. Put the inline query and the stored proc on the same page
and compare your results then.

Stored procs are valuable as much for security and portability as much as for
speed. That said, in a more robust environment they'd almost undoubtedly be
considerably faster (dedicated RDBMS).

[JonathanBigelow]

PS. "Varying the variables"? Haha, try saying that 10 times as fast as you can...

[dj shane]

That is exactly how I was doing my optimization is on the same page. Was trying BETWEEN vs the <= and => as well. Noticed a slight difference. Understand the portability and security, thanks.

[dj shane]

With further testing I verified both the normal query and SPROC code run in
less then a second in Query Analyzer. I believe the major contributing factors
in the slowness of these reports are:

1. Returning 15 columns x 3,000 records (makes for plenty of td's and tr's)
and yes I tried a clean div version and it did nothing except make the code
look prettier.

2. The fact SQL and CF are on the same box. Although this is a powerful box,
perhaps someone can fill me on on the latency issues with this type of
architecture.

[JonathanBigelow]

Shared memory + shared processor + shared disks = latency issues up the ying
yang.

CF makes a request to SQL, thread hangs open waiting for SQL to respond. SQL
receives request from CF, has to grab memory that CF is already using a grip
of. SQL has to process the request and read data from disk (hopefully you're
not at a point where your memory is low enough that you're using the pagefile
at all). SQL can return the request and close it's thread. CF still has it's
thread hanging open until it's done processing the results from SQL.

Now, on a busy site, multiply that by 30 - 40 concurrent requests and you're
box aint gonna be too happy.

I love CF, but frankly, its performance on busy sites with a lot of downstream
requests (to SQL, webservices, CFHTTP, etc.) sucks, especially if, god forbid,
something happens downstream and CF has to wait a little bit for a response.
Ever seen CF glob onto 400MB + of memory? A loaded, busy SQL server will
easily grab another 300MB. Add in IIS and other miscellaneous Windows crap and
you need at least a gig of RAM just to squeak by.