one CURSOR or 5 SELECT WHEREs?

Question

one CURSOR or 5 SELECT WHEREs?

tomiz

SSCommitted

Points: 1842
More actions
June 16, 2002 at 1:29 pm

#158593

Hello people!
Anyone know which is faster:
a stored proc with one cursor that selects 15 records from lets say 5 tables and then uses FETCH NEXT FROM INTO and asigns the values to 15 output variables...
or
a stored proc with 5 SELECT WHERE clauses?
Isn't the cursor faster because it only neends one WHERE keyword? Or is it slower because it needs 4 INNER JOINs? Or should I use a UNION instead? How do you test these things? I can't see a difference. Well, that is propably because the DB is still small.
Thanks!
/Tomi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Can you post what you have tried so I can undestand what the logic is doing and give you a proper response? Either could be better and there may be other ways, but without the logic and if possible the Execution Plan (SET SHOWPLAN_TEXT ON) can be helpfull in telling you.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

tomiz SSCommitted Points: 1842 More actions · Answer 2

Ok, here's what I meant...

I have two SPs. And I don't think either one of them is as fast as they can get. This is the first one:



Create Procedure "usp_GetAllSettings"



@memID numeric(9, 0),

@Email varchar(200) OUTPUT,

@Gender tinyint OUTPUT,

@Age tinyint OUTPUT,

@Description varchar(64) OUTPUT,

@Country char(2) OUTPUT,

@City numeric(3, 0) OUTPUT,

@Keywords varchar(200) OUTPUT,

@RealName varchar(200) OUTPUT,

@Address varchar(200) OUTPUT,

@Phone varchar(30) OUTPUT,

@MailingList bit OUTPUT



As

SET NOCOUNT ON



DECLARE settings_cursor CURSOR FOR

SELECT Email, Gender, Age, Description, Country, City, Keywords, RealName, Address, Phone, MailingList

FROM memPage

INNER JOIN memSearch

ON memPage.memID = memSearch.memID

INNER JOIN memInfo

ON memSearch.memID = memInfo.memID

WHERE memPage.memID = @memID

FOR READ ONLY;



OPEN settings_cursor;

FETCH NEXT FROM settings_cursor INTO @Email, @Gender, @Age, @Description, @Country, @City, @Keywords, @RealName, @Address, @Phone, @MailingList;



CLOSE settings_cursor;

DEALLOCATE settings_cursor;

And the second one:



Create Procedure "usp_GetAllSettings2"



@memID numeric(9, 0),

@Email varchar(200) OUTPUT,

@Gender tinyint OUTPUT,

@Age tinyint OUTPUT,

@Description varchar(64) OUTPUT,

@Country char(2) OUTPUT,

@City numeric(3, 0) OUTPUT,

@Keywords varchar(200) OUTPUT,

@RealName varchar(200) OUTPUT,

@Address varchar(200) OUTPUT,

@Phone varchar(30) OUTPUT,

@MailingList bit OUTPUT





As

SET NOCOUNT ON



SELECT @Email = (SELECT Email FROM memPage WHERE memID = @memID);

SELECT @Gender = (SELECT Gender FROM memSearch WHERE memID = @memID);

SELECT @Age = (SELECT Age FROM memSearch WHERE memID = @memID);

SELECT @Description = (SELECT Description FROM memSearch WHERE memID = @memID);

SELECT @Country = (SELECT Country FROM memSearch WHERE memID = @memID);

SELECT @City = (SELECT City FROM memSearch WHERE memID = @memID);

SELECT @Keywords = (SELECT Keywords FROM memSearch WHERE memID = @memID);

SELECT @RealName = (SELECT RealName FROM memInfo WHERE memID = @memID);

SELECT @Address = (SELECT Address FROM memInfo WHERE memID = @memID);

SELECT @Phone = (SELECT Phone FROM memInfo WHERE memID = @memID);

SELECT @MailingList = (SELECT Phone FROM memInfo WHERE memID = @memID);

I bet there is a much faster and more practical way of doing this... Right?

Thank you!

/Tomi

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Sorry to say you are going a little overboard here. A SELECT statement can be used to set multiple variables at one time without a cursor. It is a good practice to use SET NOCOUNT ON to elminate some talk from the server that generally is not needed. Also, you do not need to use ", ' is the standard way and quoting names is normally avoided in general.This should give you the exact same results with the least overhead.

CREATE PROCEDURE usp_GetAllSettings

@memID numeric(9, 0),

@Email varchar(200) OUTPUT,

@Gender tinyint OUTPUT,

@Age tinyint OUTPUT,

@Description varchar(64) OUTPUT,

@Country char(2) OUTPUT,

@City numeric(3, 0) OUTPUT,

@Keywords varchar(200) OUTPUT,

@RealName varchar(200) OUTPUT,

@Address varchar(200) OUTPUT,

@Phone varchar(30) OUTPUT,

@mailinglist bit OUTPUT

AS

SET NOCOUNT ON

SELECT

@Email = Email,

@Gender = Gender,

@Age = Age,

@Description = Description,

@Country = Country,

@City = City,

@Keywords = Keywords,

@RealName = RealName,

@Address = Address,

@Phone = Phone,

@mailinglist = MailingList

FROM

memPage

INNER JOIN

memSearch

ON

memPage.memID = memSearch.memID

INNER JOIN

memInfo

ON

memSearch.memID = memInfo.memID

WHERE

memPage.memID = @memID

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

tomiz SSCommitted Points: 1842 More actions · Answer 4

Doh!!!

So, that's the way it's done! Thank you!

That is propably the fastest way of doing that

Thank you!

/Tomi

Antares686 SSC Guru Points: 125444 More actions · Answer 5

U R Welcome. Never worry you will always learn something or find you know the answer and still do it wrong. I love these forums as they are the best learning and sharing tool out there. Also check USENET and other sites dedicated to SQL. Just know you will never know it all.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)