469,945 Members | 2,389 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

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
Nov 12 '05 #1
4 1707
-----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:
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


Nov 12 '05 #2
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:<ct*****************@newsread1.news.pas.earth link.net>...
-----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:
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

Nov 12 '05 #3
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:<ct*****************@newsread1.news.pas.earth link.net>...
-----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:
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

Nov 12 '05 #4
-----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:
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

< snip >

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Neil | last post: by
12 posts views Thread by Neil | last post: by
2 posts views Thread by Jan Mueller | last post: by
reply views Thread by Palle Girgensohn | last post: by
2 posts views Thread by anonymous | last post: by
10 posts views Thread by Zack Sessions | last post: by
6 posts views Thread by Bill44077 | last post: by
4 posts views Thread by randy.buchholz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.