Re: Is sql server as good as access 97?

From: Stephany Young (noone_at_localhost)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 13:47:58 +1200

Been there, done that, got the tee-shirt.

Once you really get into using Query Analyzer (isql) for writing your
Transact-SQL and for formulating 'queries' for exporting to your code, you
will wonder how you ever did without it.

For your 'stacked' queries, you will find that you will be able to turn an
entire stack into a single stored procedure and you should find that the
resulting stored procedure runs in a small fraction of the time it took to
execute the query at the top of the stack. Passing the required parameters
to the stored procedure is a simple matter.

There may still be places where it is appropriate to use a view, but the
view itself cannot take any parameters. That said you can still select a
subset from the view, e.g.:
    select * from <viewname> where <columnname>=<value>
What you really have to remember here is that (depending on the design of
the view) there may be more overhead (on the server) than for a stored
procedure.

"Michael Culley" <mculley@NOSPAMoptushome.com.au> wrote in message
news:%23irfGjCpEHA.2340@TK2MSFTNGP11.phx.gbl...
> "a" <a@a> wrote in message news:#uWCgCCpEHA.1152@TK2MSFTNGP11.phx.gbl...
> > You will want to look at creating stored procedures to reaplce the
> > parameterized queries you used in access97.
>
> Hi Jason,
>
> Thanks for the reply, I was locked into converting the query to a view and
> didn't think of stored procs. Unfortunately the database I am converting
> uses queries stacked up to 10 levels deep with parameters at any of one or
> more of those levels. AFAIK, the stored proc will only work if the
> parameters are at the very top level query because you can't use a stored
> proc in a select statement.
>
> > Also, I'd recommend getting used to using the Query Analyzer to
interface
> > with your SQL databases over Enterprise Manager. It may eat up a bit of
> > time now learning to write straight T-sql but you'll save significant
long
> > term time.
>
> I've written all of the T-sql for the database as well so that the app can
> create the database. Besides being for learning purposes is there any
other
> reason to use Query Analyzer? Wouldn't it get cumbersome, say, if you
wanted
> to do a quick check of the relationships in the database?
>
> --
> Michael Culley
>
>

Relevant Pages

  • Re: Mysterious Disappearing Field
    ... Have you queried the field from Query Analyzer or a Pass-Through query in ... > the database, ... > with the base tables - there were no delete queries set up ... > on the server which houses the SQL database. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Partitioned Views and parameters
    ... statistics io on) would be better for actual measurement than the % shown ... between two queries. ... Query Analyzer. ... my standard practice for optimising a query or stored procedure was to ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem with SQL Query Analyzer
    ... cache using DBCC DROPCLEANBUFFERS before testing the queries using. ... I measure the response time and I get something around 400 ... > Now if I call the very same stored procedure within the SQL Query ... > My bigger problem is that I use the query analyzer to fine-tune my ...
    (microsoft.public.sqlserver.tools)
  • RE: Differences in execution times for a stored procedure
    ... If OleDb, ... "Vincent Keller" wrote:> I am trying to execute a time-consuming stored procedure in my application. ... > Are there some known performance differences in executing a query directly through Query Analyzer and through ADO.NET? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: "On Error Resume Next" in SQL Server
    ... When you are running your query in query analyzer, ... > would occur would be due to bad user input which does not ... > that my stored procedure will run to the end of the cursor? ...
    (microsoft.public.sqlserver.programming)