July 30, 2003 at 12:00 am
If you're going for dynamic sql, your statements will always have to be compiled, optimized, security-checked, .... So you'll have to provide more access rights to the user(s).
When you chose for sp(s), you pull the complicity to yourself, but _you_ can manage access on a far better way, and you can always have the "shortcut"-query as an escape route. In your sp, you'll have multiple but optimized selects, nested in an if- or case- structure. Make sure your structure has as last else-branch your "overall"-solution query.
Wen it is running, start perform on a regular basis, so you can see if your "overall"-solution query isn't run to mutch. After all, you would want to predict accesspaths as mutch as possible, so you can finetune.
Basic rule no.1 in Rdbms-ses : tell your engine what you know.
Never said this was an easy job 😉
Regarding sp(s) This may help : http://www.sql-server-performance.com/david_gugick_interview2.asp
Edited by - alzdba on 07/30/2003 01:54:02 AM
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 30, 2003 at 2:07 am
I agree with Jay, I do everything on procs although I have'nt needed to have complex queries with optional where tests. I try to keep my sql as far away as possible from my apps, makes maintenance easier. In my early days I did have a search requirement with upto 10 optional conditions which I did by building the sql in the app. It is still horses for courses. If only one criteria required what a waste to have to do 9 redundant tests. In .NET I use Filters although I haven't yet needed to have a lot of optional criteria.
BTW lenardd, I see nothing wrong with your query it is similar to how I would write it except that I do NOT NULL and = checks. I would definately put it in a proc but would do performance checks and tuning, it is surprising what a difference a little tweak here and there, I've reduced query times from minutes to less than a second just by rearranging code and looking for what is good and what is not so good.
Far away is close at hand in the images of elsewhere.
Anon.
July 30, 2003 at 7:03 am
alzdba, DavidBurrows
When I said I would use Dynamic SQL, I was thinking of the sp_executeSQL way (as per Scott's suggestion), and NOT doing it from the client side. As a matter of fact, my data layer doesn't even allow me to use Dynamic SQL because my base routines are designed for stored procedures only.
From what I've read, SQL Server reuses the execution plan with sp_executeSQL. Again, I would only use sp_executeSQL for when I deal with search forms to handle a user's search criteria. Maybe the example I posted above is OK and not much 'overheating' would occur. The SP that the above code is in, I actually use an IF-ELSE block as I have to link in the LocationsFoundInCountry table function if the user enters a location.
I wish I could test between the different ways, but to be honest, I just don't have enough data to test with. I DO want to make sure I don't get into any bad habits early on in my project and I'm grateful for any input of others' past experiences.
--Lenard
July 30, 2003 at 7:46 am
Seems fine, but check this :
(You'll need to grant read access to all needed objects in stead of only to the sp(s))
-- TESTSCRIPT based on Northwind
-- create SQL-user-login
sp_addlogin @loginame = 'TestUser'
, @passwd = 'TestUser'
, @defdb = 'Northwind'
go
use Northwind
go
-- User only gets access to Northwind. No extra stuff (spc later on)
sp_adduser @loginame = 'TestUser'
, @name_in_db = 'TestUser'
go
-- prepare NEW test table because public has rights to all tables.
CREATE TABLE [dbo].[TESTCategories] (
[CategoryID] [int] NOT NULL ,
[CategoryName] [nvarchar] (15) NOT NULL ,
[Description] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into TESTCategories select CategoryID, CategoryName, Description from dbo.Categories
go
create procedure spc_test
@crit varchar(25)
as
set nocount on
select CategoryID,CategoryName,Description
from dbo.TESTCategories
where description like '%' + @crit + '%'
order by CategoryName
go
grant execute on spc_test to TestUser
go
create procedure spc_testDYN
@crit varchar(25)
as
set nocount on
Declare @strSql as nvarchar(2000)
select @strSql = N'select CategoryID,CategoryName,Description from dbo.TESTCategories where description like ''%'' + @paramcrit + ''%'' order by CategoryName '
print @strSql
exec sp_executesql @stmt = @strSql
, @params = N'@paramcrit varchar(25)'
, @paramcrit = @crit
go
grant execute on spc_testDYN to TestUser
go
-- now connect as TestUser
-- execute this as user TestUser
/*
- run this as user TestUser -
execute spc_test @crit = 's'
go
execute spc_testDYN @crit = 's'
go
*/
/*
-- RESULT
CategoryID CategoryName Description
----------- --------------- -------------
1 Beverages Soft drinks, coffees, teas, beers, and ales
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings
3 Confections Desserts, candies, and sweet breads
4 Dairy Products Cheeses
5 Grains/Cereals Breads, crackers, pasta, and cereal
6 Meat/Poultry Prepared meats
8 Seafood Seaweed and fish
select CategoryID,CategoryName,Description from dbo.TESTCategories where description like '%' + @paramcrit + '%' order by CategoryName
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'TESTCategories', database 'Northwind', owner 'dbo'.
*/
-- Cleanup TestObjects
drop TABLE dbo.TESTCategories
go
drop procedure spc_test
go
drop procedure spc_testDYN
go
exec sp_droplogin @loginame = 'TestUser'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply