Actually, I was answering Schreck's question about speed in inline SQL vs. stored procedures. The difference between the two is smaller on simple queries than on complex ones.
I would agree that dynamically evaluating SQL is expensive - however, there's situations where it's preferable to having many stored procs (like a search engine based on an infinite number of variable criteria). --- Billy Cravens ----- Original Message ----- From: "Dave Cahall" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 19, 2001 2:51 PM 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
