Question on performance. Which is Faster? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Question on performance. Which is Faster?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2001-07-14 : 08:19:26

Hello,

When using VBScript/ASP/ADO inside a page I know that the first query, listed below, will execute faster than the second because the process doesn't have to figure out what the "*" means.

But what about the performance inside a Stored procedure. Is there any performance boost from declaring all the fields inside an SP as there is in VBScript/ASP/ADO?

As far as SP's go which will be faster if any? My question assumes of course that you do in fact want to retrieve all of the fileds into a recordset.

'###########################################

'FIRST QUERY
Set rs = Server.CreateObject("ADODB.recordset")

strSQL = "SELECT MF_Title, MF_Fact, MF_Link, MF_Link_Text, "
strSQL = strSQL & "MF_Author, MF_Active, MF_Date "
strSQL = strSQL & "FROM METEOR_FACTS"
rs.Open strSQL, strConnString, adOpenStatic, adLockOptimistic, adCmdText

'SECOND
Set rs = Server.CreateObject("ADODB.recordset")

strSQL = "SELECT * FROM METEOR_FACTS"
rs.Open strSQL, strConnString, adOpenStatic, adLockOptimistic, adCmdText

'###########################################

'FIRST STORED PROCEDURE
CREATE PROCEDURE SP_AllMeteorFacts
AS
SELECT MF_Title, MF_Fact, MF_Link, MF_Link_Text, MF_Author, MF_Active, MF_Date
FROM METEOR_FACTS
GO

'SECOND
CREATE PROCEDURE SP_AllMeteorFacts
AS
SELECT *
FROM METEOR_FACTS
GO

Thanks for your help I am very new to Stored Procedures

John Belthoff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources