SQL Server Performance Forum – Threads Archive

Stored procedure or trigger

Hi all, I hesitate what to use SPs or triggers. I would like my queries to be as fast as possible. My data base is something like this: UsersTable ———— UserID(PK) | UserName | Password | Name | Addr | E-mail | UsrType | Role | LastLogOn EstatesTbale ———– EstateID(PK) City Phone Heating Furniture TypeEstate(FK) TypeOffr(FK) TypeConstr(FK) OfferTable ———— EstateID(FK) UserID(FK) Value ParamID(FK) Param Table ————– ParamID(PK) Param TypeEstateTable —————- Unikey(PK) TypeEstate TypeOFFRTable ————- Unikey(PK) TypeOffr TypeConstr ————- Unikey(PK) TypeConstr PictureEstateTable ——————— ID(PK) EstateID(FK) PictureFilePath DescriptionEstateTable ————————- ID(PK) EstateID(FK) TextDescription I develop Web based application using ASP.NET/C#/MS SQL. I’d like to ask you would you change something in the tables? And also, I would like what would be better: 1. To import user data by registration whit SP or Trigger? 2. When a registrated user would like to place an Advertisement what would be better again stored procedure or trigger? For this inserting I wrote my first SP: CREATE PROCEDURE prSVOFFRDATA @CITY VARCHAR(20), @PHONE CHAR(1), — Insert ‘Y’ or ‘N' @FURN CHAR(1), — Insert ‘Y’ or ‘N' @HEATING CHAR(1), — Insert ‘Y’ or ‘N' @TYPEESTATE CHAR(3), @TYPEOFFR INT, @TYPECONSTR CHAR(1), @USERID INT, @ValuePrice BIGINT, @ValueFloor BIGINT, @ValueArea BIGINT, @ValueRooms BIGINT, @PICTURE VARCHAR(50), @DESCRIPTION VARCHAR(255) AS INSERT INTO BLEK.EstatesTable(City,Phone,Furn,Heating,TypeEstate,TypeOffr,TypeConstr) VALUES(@CITY,@PHONE,@FURN,@HEATING,@TYPEESTATE,@TYPEOFFR,@TYPECONSTR) DECLARE @IDENT INT SELECT @IDENT = SCOPE_IDENTITY() INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID) VALUES(@IDENT,@USERID,@ValuePrice,’price’) INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID) VALUES(@IDENT,@USERID,@ValueFloor,’floor’) INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID) VALUES(@IDENT,@USERID,@ValueArea,’area’) INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID) VALUES(@IDENT,@USERID,@ValueRooms,’room’) IF (@PICTURE IS NOT NULL) INSERT INTO BLEK.PICTUREESTATETABLE(EstateID,PICTUREFILEPATH) VALUES(@IDENT,@PICTURE) IF (@DESCRIPTION IS NOT NULL) INSERT INTO BLEK.DSCRIPTIONESTATETABLE(EstateID,TextDescription) VALUES(@IDENT,@DESCRIPTION) You can see that it is my first SP<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I dont know how to optimize it. I don’t have an idea would be better if I CREATE VIEW and use TRIGGER to populate data in the tables. I have to UPDATE this rows when a user wants to change the data in his/her advertisement. I have also a big SEARCH query between 4 tables. What would you mean? To CREATE VIEW and to use SELCT QUERY from this VIEW or to create Stored PRocedure. Could help me please, could you give me an advice? Thank you very much!

Hi ya, This is always very difficult to answer online as there would be a thousand and one questions to work out which might be the most optimal… First question is why do you have so many tables, i.e. why not have price, floor, area, room, picture and description in your main table? second question, it sounds like the main performance is going to be in the searching aspect, so what are the searches that you are going to need to support? This may change the table design too whether a view or sp is faster for a select will depend on the complexity of the sql. I’d suggest try both and use Profiler to check which one is faster. A view allows you to have lots of different search conditions, where as a procedure would be quicker if you have specific predefined searches that you have to be able to do inserting using sp is likely to be better performing than a view with ‘instead of triggers’ Sorry not many answers… Cheers
Twan

]]>