Re: VB - Stored Procs vs queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 09:39:29 -0500

One thing to note here. Sometimes the truth is that procedures can make
things more difficult than just sending queries, and sometimes it can be
hard to optimize a query using a stored procedure. But if you hit this kind
of wall, realize one thing. Every time this question is asked here in this
group, no one ever says, "stored procedure suck, don't use them." In fact
we try to give ideas to make them work in all cases.

Tom and Deepak' reasons are excellent, and for me, troubleshooting is the
best part. The user can only touch the database using a strict, specific
interface so I can test this interface. It gives complete control, and
easier testing a year from now when something is going slow. 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Deepak" <letsdotnet@hotmail.com> wrote in message
news:eHyCzLtJEHA.3848@TK2MSFTNGP09.phx.gbl...
> Hey Chris,
> Stored procedures are always a better choice when executing code from a
tier
> other than data. One of the benefits you will get is that stored
procedures
> are pre-compiled and thus enhance performance.
>
> -- 
> With Regards,
>
>
> Deepak
> [I code, therefore I am]
>
> "Chris Whitehead" <cwmsdn@distinctive-systems.com> wrote in message
> news:OmEQfwsJEHA.4072@TK2MSFTNGP12.phx.gbl...
> > I'm about the start a new project which is a VB 6 application connection
> to
> > SQL Server 2000 using ADO.  My question is when I is best to use stored
> > procedures to return a query as opposed to running the query through
code
> > using a commandtext?
> >
> > If I am processing a large query, I presume it's better to do it with a
> > stored procedure passing though a few parameters instead of sending the
> > whole SQL query through a command text? Correct?
> > What about small queries? For example if I want to do "SELECT
ProductName
> > FROM Products WHERE ProductID = 'Car'" then am I best doing this with a
> > commandtext or should I use a stored procedure and simply pass through
the
> > ProductID as a parameter?
> >
> > I am just after some guidelines on how best to optimize.  Thanks.
> >
> > Chris
> >
> >
>
>

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... > Noiseword varcharNot Null ... > the data from the noise file to the noise_words table. ... >> A clause of the query contained only ignored words. ... >> into query analyzer before starting the stored procedure. ...
    (microsoft.public.sqlserver.fulltext)
  • RE: ODBC query in VB code Need HELP
    ... I am trying to get a stored procedure to run on info I send it I do not need ... I played around and got the code below to work using a pass-through query, ... Dim strpass As String ...
    (microsoft.public.access.formscoding)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)
  • Re: Query parameters - Tricky Stored Procedure Question
    ... Dim tCommand As SqlCommand = New SqlCommand ... Here you would pass in your constructed ad-hoc query ... if I have a stored procedure such as ...
    (microsoft.public.dotnet.languages.vb)