February 19, 2002 at 9:32 am
We have stored procedures that hit different regions in our database and when a large change comes along it requires changes to many of them. It would be fewer, but we have been converting stored procedures from dynamic SQL to static SQL and usually this means 1 stored procedure becomes more than 1. The major change was when 1 became 22.
I wish we could plan ahead for the changes, but often we don't know until a month or 2 before it goes live. Sometimes we have a 3 or 4 month warning, but that is rare.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 19, 2002 at 10:08 am
Different regions meaning different parts of a table?
Andy
February 19, 2002 at 10:12 am
sorry, meaning different databases with distinct but related purposes. One is our data on 13F filings (stocks), one is for our N30D filings (mutual funds), one for staff that buy stocks and manage mutual funds, one for staff that sell stocks. Analysts are in there somewhere too. And a few other databases.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 4, 2002 at 8:17 am
Does anyone have any problems printing this article out? I'm trying to print it using IE6, but with no joy. No print job is sent to the printer. Other articles from this site print fine. Strange!
March 4, 2002 at 10:12 am
September 13, 2002 at 11:07 am
I would love to do with the WHERE clause what you do with the ORDER BY. Is there a way?
September 13, 2002 at 11:25 am
Which example are you looking?
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 13, 2002 at 12:02 pm
The static SQL version of the second case.
October 22, 2002 at 12:46 pm
Forgive the delayed response.
I think you were looking at my article called Dynamic SQL vs Static SQL part 2. That one has code in the 2nd case that uses the case function in the order by.
In the article I wrote called How Dynamic SQL Can Be Static SQL example 1 shows how you can use the case function in the where clause.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 23, 2002 at 5:27 am
There is a common belief, as you mention in your article, that dynamic sql does not have compile plans. This is not true.
Any adhoc query i.e not an SP, is parameterised, compiled and put in the cache. Depending on the complexity of the query (sub tree cost) the query will stay in the cache until it is booted out because of another query.
the main issue is with parameterisation, it doesn't really work. It is supposed to convert values into parameters so that when you call the same query with another value you can get the same plan.
i.e select * from mytable where col1 = 1
and select * from mytable where col1 = 2
should result in the same parameterised query
select * from mytable where col1 = @p1
Because this doesn't work you should use sp_executesql and do the parameterisation yourself
i.e the above becomes
sp_executesql N'select * from mytable where col1 = @p1', N'@p1 int', 1
So if you use sp_executesql the performance will be the same as an SP, (except adhoc queries are booted out the cache before SPs).
Articles to read on this are
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/default.asp
This is detailed in depth somewhere but I can't find it at the moment.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply