I have two stored procedures that do the same thing. I would think the
latter would be faster than the former. Can anyone explain why it is not?
First one...
CREATE PROCEDURE old_webclientacctholddaily @caccountid int, @OrderBy
varchar(50)
AS
DECLARE @mySQL nvarchar(500)
SET @mySQL = N'SELECT holdings.*,securities.* FROM holdings, securities
WHERE holdings.secid = securities.secid AND holdings.accountID=@xparam ORDER
BY '+ @OrderBy
EXEC sp_executeSQL @mySQL , N'@xparam int', @caccountid
(Records=162, Time=942ms)
SQL = old_webclientacctholddaily
Second one...
CREATE PROCEDURE webclientacctholddaily @caccountid int, @OrderBy
varchar(50)
AS
IF @orderBy = 'sector'
SELECT *
FROM holdings,
securities
WHERE holdings.secid = securities.secid
AND holdings.accountID=@caccountid
ORDER BY sector,
secName
ELSE
IF @orderBy = 'pctport'
SELECT *
FROM holdings,
securities
WHERE holdings.secid = securities.secid
AND holdings.accountID=@caccountid
ORDER BY pctport desc
ELSE
IF @orderBy = 'country'
SELECT *
FROM holdings,
securities
WHERE holdings.secid = securities.secid
AND holdings.accountID=@caccountid
ORDER BY country,
secName
ELSE
SELECT *
FROM holdings,
securities
WHERE holdings.secid = securities.secid
AND holdings.accountID=@caccountid
ORDER BY secName
(Records=162, Time=971ms)
SQL = webclientacctholddaily
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.