What's the quickest and most efficent method for recalling data 1

[abienz]

Hi there,

I wass just wondering which way is the fastest and/or most efficent way of recalling data from a DB.

My setup at work is a Sql Server 7 DB and I use ASP to communicate with it, I also use Ultradev. I don't like having lots of unnecesary code and I find that Ultradev does this sometimes so instead of using it's built in functions using command parameters and such like I use this method...

Dim Conn 'Database Connnection
Dim RsN 'Recordset for the News
Dim Sql 'Sql Code variable

Set Conn = Server.CreateObject("ADODB.Connection&quot
Set RsN = Server.CreateObject("ADODB.Recordset&quot
Conn.Open MyConnection

Sql = "SELECT newsID, " _
& "newsText, " _
& "newsDate " _
& "FROM news "_
& "ORDER BY newsDate DESC"

SET RsN = Conn.Execute(Sql)

I understand that using a Stored Procedure might be quicker, but for this example I wanted to write out the fields individually so I could see them on the page.

Also I understand that it's quicker to write out each field that you want selected instead of just using * is this true?

What methods do other people use to grab data from a DB for their recordsets?

 

[dbrom]

You are right.

Stored procedure is going to be the fastest since it is pre-compiled imside SQL server.

And it is pretty simple to use.
Instead of

SET RsN = Conn.Execute(Sql)

you just do

SET RsN = Command.Execute()

(Of course you need to create stored procedure and command with appended parameters. Here's an article on it:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q164485

. Also, just do a search on Google for "ASP Command Stored Procedure&quot

As far as * being inefficient - you are right again. If you write a star as opposed to specifying individual elements, then SQL Server has to do some more work to get you the data - it has to look up column names for the table, etc.

When I started out with ASP, I did not use stored procedures at all, but now I am using them more and more.
Also, I use recordset paging a lot. Here's a great resource on that :

http://adopenstatic.com/experiments/recordsetpaging.asp

Hope this helps

<Dmitriy>
dbrom@crosswinds.net