I want to begin developing a new asp.net app to replace a current Cold Fusion app. This app uses and updates a 5 GB MSSQL DB every day. It will also be required to create reports based on queries with over 200,000 records each and produce over 10,000 html reports every week night.

Here is my problem: The queries seem to run a little slower on asp.net and I do not seem to have the same type of builtin tools in asp.net as I do in CFMX.

Lets start with just a few basics:
1. is there a query.recordcount variable for asp.net?
2. is there a query.currentrow variable for asp.net?
3. Can I re-read a curent query and reference a record by RRN or do i have to re-run a query each time and dump the result into an array?
4. Someone show me how to get asp.net to match CFMX's query speed?
Every test I run has CFMX winning every time.

thanks

kes

If you're using a DataSet, you can get the record count by using:


If you're using a DataReader, you must modify your select statement to output a SQL variable:


In my experience w/ other .NET developers not alot of people search for a particular row with this technology because you usually bind your data to another object rather than looping thru the information returned (like with classic ASP). Both the DataReader and DataSet can inform you of which row you're currently using, but I guess to answer your question better I'd need to know exactly what you're searching for the current row for.

Maybe I'm being thick but I don't know exactly what you're asking with your third question.

Also, just as a hint, if you're using disconnected data, do NOT use the DataSet... it creates alot of extra information that you'll never ever even use. Instead, use the DataReader. It's MUCH faster as it was built to spew information quickly.

I take it back about the query speed being slower. I think it is faster, but any processing seems to be slower to the point where the net result gives CFMX a clear advantage. Please, someone prove me wrong!
kes

Are you using a DSN or DSNless connection. If you're utilizing a DSN, you're already putting yourself at a disadvantage. If you're using SQL, using the SQLClient namespace and connect straight to the DB server.

You can find connection strings at http://www.connectionstrings.com



Of course, if you're using CodeBehind then make the necessary changes.

I made a migration from ColdFusion to ASP.NET over 3 years ago and have seen nothing but big improvements in useability, features, and speed.

Also, remember if you have Debug mode enabled it's going to give you a decent performance hit. Disable it when you don't need it, and use Try... Catch... End Try blocks where ever it makes sense.

going straight to the sql namespace. However, it seams that asp.net queries are running faster, but the page rendering is slower. I've been using a CFM type style and i will try to bind data to object as was suggested in another comment.
thanks
kes

No problem. Might as well make the most of the tools at hand. ASP.NET works in a very different manner than other "scripting" languages.

Also, along the lines of speed, if you're just dumping data, use the DataReader (as was discussed) as well as the Repeater control, or even the DataList, in place of the DataGrid. DataGrid is the most used control, and often by many devs just not knowing any better.

Take a look around and you'll see what I mean, but i use the DataReader and Repeater controls AAAAALLLLLLLLLLLLLLLLL the time and my page download and render times are FAR better than anywhere else I've seen.

If you're dumping a repeater/table with like 30000 rows in it, expect a bit of render time too... that's alot of work. I've even seen instances (on large page dumps) where the browser activity icon stops animating, but the page is still actually downloading. It's wierd, but it happens.

Rendering is also going to be a culprit of the client workstation vs the server. All the server is doing is passing the data (HTML). It's up to the client to build everything, so if you're using a workstation w/ less than 1.3ghz and 512MB RAM, you're going to need a bit more time for rendering than somebody w/ a more up-to-date system.

here is the code and the cfm code that beats it.
this query returnd 34,500 records:
any thoughts would be helpful

<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim conCsi As SqlConnection
Dim cmdSelectAuthors As SqlCommand
Dim dtrAuthors As SqlDataReader
Dim intI As Integer
conCsi = New SqlConnection( "Server=zzzzz;uid=yyyy;pwd=xxxxxx;database=ttttt" )
conCsi.Open()
cmdSelectAuthors = New SqlCommand( "Select COUNT(csi_csiSym) as cnt, csi_csiSym, csistk1,csistk2, csiInstrument, csiExchange From csi", conCsi )
dtrAuthors = cmdSelectAuthors.ExecuteReader()
intI = 1
Response.Write( "done")
Response.Write( "<table border=1>" )
While dtrAuthors.Read()

Response.Write( "<tr><td>" )
Response.Write( intI )
Response.Write( "</td><td> " )
Response.Write( dtrAuthors( "csi_csiSym" ) )
Response.Write( "</td><td> " )
Response.Write( dtrAuthors( "csistk1" ) )
Response.Write( "</td><td> " )
Response.Write( dtrAuthors( "csistk2" ) )
Response.Write( "</td><td> " )
Response.Write( dtrAuthors( "csiInstrument" ) )
Response.Write( "</td><td> " )
Response.Write( dtrAuthors( "csiExchange" ) )
Response.Write( "</td></tr>" )
intI = intI +1
End While
Response.Write( "</table>" )
dtrAuthors.Close()
conCsi.Close()
Response.Write( intI )
%>
---
CFM

<cfquery name="X" datasource="#APPLICATION.dsn2#">
SELECT csi_csiSym,
csistk1,
csistk2,
csiInstrument,
csiExchange
FROM csi

order by csiExchange, csistk2
</cfquery>
done
<cfoutput>#x.recordcount#</cfoutput>
<table border=1>
<tr>
<td>row</td>
<td>csi_csiSym</td>
<td>csistk1</td>
<td>csistk2</td>
<td>csiInstrument</td>
<td>csiExchange</td>
</tr>
<CFOUTPUT query="X">
<tr>
<td>#currentrow#</td>
<td>#csi_csiSym#</td>
<td>#csistk1#</td>
<td>#csistk1#</td>
<td>#csiInstrument#</td>
<td>#csiExchange#</td>
</tr>


</CFOUTPUT>


</table>[/code]

Try that on for size. You're still thinking with the Scripting brain... ASP.NET is not scripting. To reap its advantages you have to use it's tools the way they were intended to be used. The way you did it was "classic ASP style" which is going to be slower.

Also, remember that the first time you run this it's going to be slower because it's being compiled. After the first run you'll get the TRUE speed.

Thanks. I've started in that direction and will post the speed test results!
thanks!!
kes

So far CFM is still winning by about 5 seconds, same as the other script template, but i get the idea. There is an aggregate issue with "SELECT COUNT(csi_csiSym), csi_csiSym, csistk1, csistk2, csiInstrument, csiExchange...", but again i get the issue and thanks for your help.

with thanks!!

(i think the try section is pulling the first record. is there a way to get around this?)
kes

*applauses CFMX*

Well, it's bound to happen sometimes. That is still interesting. Are either of those applications running (or not) on the same server as the MSSQL DB?

Yeah that was a quick *SLAM* !BANGi script off the top of my head so I don't guarantee anything

Hope everything helped.

Thanks again for your help. It is greatly appreciated and I learned a lot about asp.net. I'm going to be re-writting a lot of the site in .net. Yes, all are running on the same box in the test environment.

All your postings helped and i appreciate you spending a lot of time on this topic.

Thanks!!

kes

well if u wanna really make it smoke .net then use it with coldfusion components (cfc) and of course use cfqueryparam correctly and watch those cfm times get even faster! not to mention with the use of cfqueryparams will be a lot more effeciant and secure too

btw~
on the current major .net security flaw

Opinion: Is the ASP.Net Bug a Big Deal? MS Thinks So
You might have to wait six months or more to get your "highly critical" browser bugs patched

oh btw~ u might wanna wait a bit for the new release of coldfusion (blackstone) or u will really regret moving the app.
the new version is friggin SICK! it is so sweet.
the one thing i can publicly say is that in regards to your reports, it will now be built in and is friggin really cool!

maybe u should also checkout the .net version of cfml at www.newatlanta.com

which basically lets u run cfm code on the .net platform