To View or not to View, that is the question...

In my old MDB databases, I constructed a lot of "subQueries" to filter
out records, then based a new query on the subQuery. This results in
huge speed increases on large datasets.
However...
In Accesss2000 ADP Project with SQL Server backend:
Is there any benefit to creating Views, then basing a Stored Procedure
on that View, rather than on the tables/table joins themselves?
Since I can't pass parameters to a View, all I can really do with them
is to create simple "=" type criteria.
In reading, I've noticed people mention that views are not cached on
SQL Server so that it may actually hinder performance???

Also, in constructing the WHERE clause of a Stored Procedure, is there
any difference in performance depending on the order in which the AND
strings are created?

Example:
I have a large recordset of employees tblEmployees with fields:
fldIdiot (bit), fldFirstName nvarchar(100), fldLastName
nvarchar(100)

There are 5 idiots, 250 Bobs in 10,000 records

SELECT fldLastName FROM tblEmployees...

Does WHERE clause:
WHERE (fldIdiot = 1) AND (fldFirstName LIKE '%' + @parFirstName +
'%')
return faster results than:
WHERE (fldFirstName LIKE '%' + @parFirstName + '%') AND (fldIdiot
= 1)

thanks,
lq




lq

re: To View or not to View, that is the question...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

RE: Speed increase dependent on WHERE criteria placement.
The only thing that affects the speed of execution is if a criteria
column is indexed. SQL Server uses a query analyzer to determine
which is the "best" index to use. You can use "Hints" (see SQL books
on line) to "tell" the query to use one index first instead of the
analyzed "best" index.

RE: "Subqueries."
I'm assuming you mean separate queries that have parameters. It would
seem that each "main" query could only use a named query during its
construction, e.g.:

qryOtherQuery:
PARAMETERS dteLastSale DATE;
SELECT * FROM myTable WHERE LastSale = dteLastSale

main query:
SELECT * FROM qryOtherQuery

This would mean that you'd create the stored procedures (sp) in the
same manner, but instead of have a separate query, you'd include the
query, as a tabular subquery, in the sp and pass the parameter to the
sp instead of to the other query. E.g.:

CREATE PROCEDURE usp_MySP
@dteLastSale DATETIME
AS
SELECT *
FROM (SELECT * FROM myTable WHERE LastSale = @dteLastSale)

GO

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP71jzoechKqOuFEgEQI/7ACggu8dnk6dub08AhVFt28MH12IgdIAoK6c
+8gI+kX5KdqBt9rTbCj0ockR
=6tZk
-----END PGP SIGNATURE-----

Lauren Quantrell wrote:[color=blue]
> In my old MDB databases, I constructed a lot of "subQueries" to filter
> out records, then based a new query on the subQuery. This results in
> huge speed increases on large datasets.
> However...
> In Accesss2000 ADP Project with SQL Server backend:
> Is there any benefit to creating Views, then basing a Stored Procedure
> on that View, rather than on the tables/table joins themselves?
> Since I can't pass parameters to a View, all I can really do with them
> is to create simple "=" type criteria.
> In reading, I've noticed people mention that views are not cached on
> SQL Server so that it may actually hinder performance???
>
> Also, in constructing the WHERE clause of a Stored Procedure, is there
> any difference in performance depending on the order in which the AND
> strings are created?
>
> Example:
> I have a large recordset of employees tblEmployees with fields:
> fldIdiot (bit), fldFirstName nvarchar(100), fldLastName
> nvarchar(100)
>
> There are 5 idiots, 250 Bobs in 10,000 records
>
> SELECT fldLastName FROM tblEmployees...
>
> Does WHERE clause:
> WHERE (fldIdiot = 1) AND (fldFirstName LIKE '%' + @parFirstName +
> '%')
> return faster results than:
> WHERE (fldFirstName LIKE '%' + @parFirstName + '%') AND (fldIdiot
> = 1)
>
> thanks,
> lq
>
>
>
>
> lq[/color]

re: To View or not to View, that is the question...

Thank you for the tip on the tabular subquery.

Will this:

CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
AS SELECT *
FROM (SELECT * FROM myTable WHERE LastSale = @dteLastSale)
WHERE (LastName = 'smith')
ORDER BY FirstName
GO

execute faster than that?:

CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
AS SELECT *
FROM myTable
WHERE (LastSale = @dteLastSale) AND (LastName = 'smith')
ORDER BY FirstName
GO


??? lq

MGFoster <me@privacy.com> wrote in message news:<ctdvb.8752$n56.5306@newsread1.news.pas.earth link.net>...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> RE: Speed increase dependent on WHERE criteria placement.
> The only thing that affects the speed of execution is if a criteria
> column is indexed. SQL Server uses a query analyzer to determine
> which is the "best" index to use. You can use "Hints" (see SQL books
> on line) to "tell" the query to use one index first instead of the
> analyzed "best" index.
>
> RE: "Subqueries."
> I'm assuming you mean separate queries that have parameters. It would
> seem that each "main" query could only use a named query during its
> construction, e.g.:
>
> qryOtherQuery:
> PARAMETERS dteLastSale DATE;
> SELECT * FROM myTable WHERE LastSale = dteLastSale
>
> main query:
> SELECT * FROM qryOtherQuery
>
> This would mean that you'd create the stored procedures (sp) in the
> same manner, but instead of have a separate query, you'd include the
> query, as a tabular subquery, in the sp and pass the parameter to the
> sp instead of to the other query. E.g.:
>
> CREATE PROCEDURE usp_MySP
> @dteLastSale DATETIME
> AS
> SELECT *
> FROM (SELECT * FROM myTable WHERE LastSale = @dteLastSale)
>
> GO
>
> HTH,
>
> MGFoster:::mgf
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBP71jzoechKqOuFEgEQI/7ACggu8dnk6dub08AhVFt28MH12IgdIAoK6c
> +8gI+kX5KdqBt9rTbCj0ockR
> =6tZk
> -----END PGP SIGNATURE-----
>
> Lauren Quantrell wrote:[color=green]
> > In my old MDB databases, I constructed a lot of "subQueries" to filter
> > out records, then based a new query on the subQuery. This results in
> > huge speed increases on large datasets.
> > However...
> > In Accesss2000 ADP Project with SQL Server backend:
> > Is there any benefit to creating Views, then basing a Stored Procedure
> > on that View, rather than on the tables/table joins themselves?
> > Since I can't pass parameters to a View, all I can really do with them
> > is to create simple "=" type criteria.
> > In reading, I've noticed people mention that views are not cached on
> > SQL Server so that it may actually hinder performance???
> >
> > Also, in constructing the WHERE clause of a Stored Procedure, is there
> > any difference in performance depending on the order in which the AND
> > strings are created?
> >
> > Example:
> > I have a large recordset of employees tblEmployees with fields:
> > fldIdiot (bit), fldFirstName nvarchar(100), fldLastName
> > nvarchar(100)
> >
> > There are 5 idiots, 250 Bobs in 10,000 records
> >
> > SELECT fldLastName FROM tblEmployees...
> >
> > Does WHERE clause:
> > WHERE (fldIdiot = 1) AND (fldFirstName LIKE '%' + @parFirstName +
> > '%')
> > return faster results than:
> > WHERE (fldFirstName LIKE '%' + @parFirstName + '%') AND (fldIdiot
> > = 1)
> >
> > thanks,
> > lq
> >
> >
> >
> >
> > lq[/color][/color]

re: To View or not to View, that is the question...

Sorry, another question(Views option):

Will this:

CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
AS SELECT *
FROM (SELECT * FROM myTable WHERE LastSale = @dteLastSale)
WHERE (LastName = 'smith')
ORDER BY FirstName
GO

execute faster than this:

CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
AS SELECT *
FROM myTable
WHERE (LastSale = @dteLastSale) AND (LastName = 'smith')
ORDER BY FirstName
GO

or what about this:

CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
AS SELECT *
FROM myVIEWname
WHERE (LastSale = @dteLastSale)
ORDER BY FirstName
GO

(myVIEWname is a View with the following SQL:
SELECT * FROM myTable WHERE LastName = 'Smith')



??? lq




MGFoster <me@privacy.com> wrote in message news:<ctdvb.8752$n56.5306@newsread1.news.pas.earth link.net>...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> RE: Speed increase dependent on WHERE criteria placement.
> The only thing that affects the speed of execution is if a criteria
> column is indexed. SQL Server uses a query analyzer to determine
> which is the "best" index to use. You can use "Hints" (see SQL books
> on line) to "tell" the query to use one index first instead of the
> analyzed "best" index.
>
> RE: "Subqueries."
> I'm assuming you mean separate queries that have parameters. It would
> seem that each "main" query could only use a named query during its
> construction, e.g.:
>
> qryOtherQuery:
> PARAMETERS dteLastSale DATE;
> SELECT * FROM myTable WHERE LastSale = dteLastSale
>
> main query:
> SELECT * FROM qryOtherQuery
>
> This would mean that you'd create the stored procedures (sp) in the
> same manner, but instead of have a separate query, you'd include the
> query, as a tabular subquery, in the sp and pass the parameter to the
> sp instead of to the other query. E.g.:
>
> CREATE PROCEDURE usp_MySP
> @dteLastSale DATETIME
> AS
> SELECT *
> FROM (SELECT * FROM myTable WHERE LastSale = @dteLastSale)
>
> GO
>
> HTH,
>
> MGFoster:::mgf
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBP71jzoechKqOuFEgEQI/7ACggu8dnk6dub08AhVFt28MH12IgdIAoK6c
> +8gI+kX5KdqBt9rTbCj0ockR
> =6tZk
> -----END PGP SIGNATURE-----
>
> Lauren Quantrell wrote:[color=green]
> > In my old MDB databases, I constructed a lot of "subQueries" to filter
> > out records, then based a new query on the subQuery. This results in
> > huge speed increases on large datasets.
> > However...
> > In Accesss2000 ADP Project with SQL Server backend:
> > Is there any benefit to creating Views, then basing a Stored Procedure
> > on that View, rather than on the tables/table joins themselves?
> > Since I can't pass parameters to a View, all I can really do with them
> > is to create simple "=" type criteria.
> > In reading, I've noticed people mention that views are not cached on
> > SQL Server so that it may actually hinder performance???
> >
> > Also, in constructing the WHERE clause of a Stored Procedure, is there
> > any difference in performance depending on the order in which the AND
> > strings are created?
> >
> > Example:
> > I have a large recordset of employees tblEmployees with fields:
> > fldIdiot (bit), fldFirstName nvarchar(100), fldLastName
> > nvarchar(100)
> >
> > There are 5 idiots, 250 Bobs in 10,000 records
> >
> > SELECT fldLastName FROM tblEmployees...
> >
> > Does WHERE clause:
> > WHERE (fldIdiot = 1) AND (fldFirstName LIKE '%' + @parFirstName +
> > '%')
> > return faster results than:
> > WHERE (fldFirstName LIKE '%' + @parFirstName + '%') AND (fldIdiot
> > = 1)
> >
> > thanks,
> > lq
> >
> >
> >
> >
> > lq[/color][/color]

re: To View or not to View, that is the question...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Best way to find out is to test them. :-)

You can pop them in the SQL Server Query Analyzer & check its
execution plan (look under Tools menu [I believe]). Also, put indexes
on the criteria columns for better speed.

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP75DiIechKqOuFEgEQLKGgCg3L6PeT+fORGJD0VgfGfj7M wvzvkAoN5f
DgQa83TvQdMPWPGMFnTgBmAQ
=dR7K
-----END PGP SIGNATURE-----

Lauren Quantrell wrote:
[color=blue]
> Sorry, another question(Views option):
>
> Will this:
>
> CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
> AS SELECT *
> FROM (SELECT * FROM myTable WHERE LastSale = @dteLastSale)
> WHERE (LastName = 'smith')
> ORDER BY FirstName
> GO
>
> execute faster than this:
>
> CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
> AS SELECT *
> FROM myTable
> WHERE (LastSale = @dteLastSale) AND (LastName = 'smith')
> ORDER BY FirstName
> GO
>
> or what about this:
>
> CREATE PROCEDURE usp_MySP @dteLastSale DATETIME
> AS SELECT *
> FROM myVIEWname
> WHERE (LastSale = @dteLastSale)
> ORDER BY FirstName
> GO
>
> (myVIEWname is a View with the following SQL:
> SELECT * FROM myTable WHERE LastName = 'Smith')
>
>
>
> ??? lq
>
>[/color]
< snip >