So we are AGREED that, even though the performance gains are relatively small (on simple queries), even simple queries ARE faster in a storedprocedure than sending the SQL string via CF???
Someone was telling me that using simple INSERTS/UPDATES/DELETES in stored procedures is a waste of time and I should confine my SPs to more complex SQL statments. Is there ever a scenario when a stored procedure ISN'T faster?? Carmen [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Cahall Sent: Wednesday, December 19, 2001 2:52 PM To: [EMAIL PROTECTED] Subject: RE: stored procs I do not understand your first sentence. It seems to me that you are saying simple queries take more time than complex ones and I do not think that is what you mean. If you are saying that the simpler the query, the less performance gain for stored procedures versus sending the select statement. I would agree. However, any time you can call a simple query (stored procedure or not) the query will run faster and stored procedures (regardless of their complexity) will always run faster than the same query if you send the SQL statements via a string. My original statement was intended to mean that you can let your ColdFusion determine which stored procedure to call and that would be faster than having the if logic inside a complex stored procedure. Again, according to the book I read, decision logic in the database is not very efficient. So calling one of several small stored procedures is more efficient than sending a bunch of parameters to a single stored procedure and having to parse through the logic inside the stored procedure. The primary advantage of stored procedures (according to my understanding) is to gain the speed of having queries precompiled. When the database has to parse the SQL and make decisions it slows down the processor significantly (again according to what I have read). -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of BILLY CRAVENS Sent: Wednesday, December 19, 2001 2:28 PM To: [EMAIL PROTECTED] Subject: Re: stored procs I've found that the simpler the query, the less of a performance gain. Reason being is the determination of the execution plan - in SP's this is pre-compiled, but in inline SQL, it has to be calculated each time (actually, ODBC connection pooling should keep this from happening, but we'll assume that a query is executed each time it is called). On queries with larger joins, the execution plan is more complex and takes more time to generate, thus the gain in speed. --- Billy Cravens ----- Original Message ----- From: "Schreck, Tom" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 19, 2001 1:40 PM Subject: RE: stored procs I found a solution on Google. Here's the url: http://www.sqlteam.com/item.asp?ItemID=2077 There's a function called COALESCE which picks the first non-null value in a comma delimeted list. Check out the examples in the above link on how to apply this for dynamic WHERE clauses. My biggest hangup to using stored procs has been the inability to create dynamic WHERE clauses, so now I'll start incorporating them into my development. Has anyone used COALESCE function as described above? Will this degrade performance? I prefer not to have several different stored procs versus putting the logic into 1 file. Does anyone know the performance gains from stored procs versus using a CF query? Thanks for your help - Tom -----Original Message----- From: Hinojosa, Robert A [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 19, 2001 1:28 PM To: '[EMAIL PROTECTED]' Subject: RE: stored procs You may want to consider building the sql as a string and then use exec to execute the statement. Something like this. SELECT @sqlstring = "SELECT * FROM <tablename> WHERE <fieldname> " + @dynamicWhereClause + " IN (" +@parameter = ")" EXEC(@sqlstring) HTH, Robert Hinojosa [EMAIL PROTECTED] 972.243.4343 x7446 -----Original Message----- From: Schreck, Tom [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 19, 2001 11:58 AM To: [EMAIL PROTECTED] Subject: stored procs I'm trying to create a stored procedure that accepts any combination of 5 parameters and filters a recordset. I'm applying defaults to the parameters so I know if one of the parameters matches the default then I do not include that parameter as part of the filter. Here's a snippet: DECLARE @numContentTypeID int,@numWorkFlowStateID int,@numTaxonomyID int, @dtStart datetime, @dtEnd datetime SELECT @numContentTypeID = 0 SELECT @numWorkFlowStateID = 0 SELECT @numTaxonomyID = 7 SELECT @dtStart = getDate() SELECT @dtEnd = dateadd(month,1,getdate()) --PRINT @dtStart --PRINT @dtEnd --set top 3 content SELECT TOP 3 numContentID, numHitCount FROM tblContent WHERE 0=0 if @numContentTypeID > 0 AND tblContent.numContentTypeID = @numContentTypeID if @numWorkFlowStateID > 0 AND tblContent.numWorkFlowStateID = @numWorkFlowStateID if @numTaxonomyID > 0 AND tblContent.numContentID IN( SELECT tblTaxonomyContent.numContentID FROM tblTaxonomyContent WHERE (tblTaxonomyContent.numTaxonomyID = @numTaxonomyID) ) ORDER BY numHitCount DESC Am I missing the syntax on how to dynamically create the WHERE clause? Could it be that stored procedures can not create dynamic WHERE clauses? If so, then do you have to make up a huge if statement block to try to determine all possible permutations of 5 different parameters? If stored procs do not allow for dynamic WHERE clauses, then this seems to be a very serious week point for using stored procs. What are your thoughts? Thanks - Tom ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org
