May 3, 2007 at 8:34 am
I have got the adventureworks database installed but I see few samples there for it in BOL. I know I am trying to get too much in too little time but want to have all things before I embark on my motive of having good SQL knowledge.
Regards
May 3, 2007 at 8:57 am
Grant, I can see a case such as the example in BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0208b259-7129-4d9f-9204-8445a8119116.htm) where due to the recursive nature of the data a CROSS APPLY will be efficient. However, do you have a non-table value function or non-recursive example you can post that demonstrates improved performance using the APPLY opperator? I am continually on the lookout for ways to do things more efficiently. I must say that often new programming constructs (take row_number/over for example) may result in "cleaner" code but are SIGNIFICANTLY less efficient than other mechanisms.
BTW, in 14 years of database development I have had precisely two situations where I needed recursion to access data. And no, I haven't developed many HR apps. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2007 at 10:02 am
I agree on the recursion. I've implemented one solution using it and I was VERY sorry afterwards.
We have a need to only every insert data, no updates. So, we have a mechanism for marking a version of a given row. A brain dead example would be:
SELECT...
FROM MainTable mt
INNER JOIN VersionTable vt
ON mt.Id = vt.Id
INNER DataTable dt
ON mt.ID = dt.Id
AND dt.VersionId = (SELECT TOP(1) dt2.VersionID
FROM DataTable dt2
WHERE dt2.Id = mt.Id
AND dt2.VersionId <= vt.VersionId
ORDER BY dt2.Id DESC, dt.VersionId DESC)
This works extremely well. However if we change it to:
SELECT....
FROM MaintTable mt
INNER JOIN VersionTable vt
ON mt.Id = vt.Id
CROSS APPLY (SELECT TOP(1) dt.whatever
FROM DataTable dt
WHERE dt.Id = mt.Id
AND dt.VersionId <= vt.VersionId
ORDER BY dt.Id DESC, dt.VersionId DESC) AS dt
ON dt.Id = mt.Id
And it cuts the IO & Scans in half for the tables where you can apply it. This, as you point out, works for table valued data, but it doesn't have much applicability outside it. It's not the answer to all life's problems, but it is another very useful tool in the toolbox.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 3, 2007 at 10:23 am
>>...We have a need to only every insert data, no updates. ...
Can't you simply do a not exists clause to see if the record of concern already exists? I can't see a need to check any versioning since you NEVER do an update regardless of any values. Assuming a properly indexed key, this should be VERY efficient.
Simplified example:
create table #tmp (a int constraint pk1 primary key, b char(1), c varchar(10))
insert #tmp values (1, 'a', 'asdf')
insert #tmp values (2, 'b', 'xxxx')
insert #tmp values (3, 'c', 'kkkk')
select *
from #tmp
--this does insert a row
insert #tmp
select a, b, c
from (
select 4 a, 'd' b, 'nnnn' c) as t
where not exists (select * from #tmp t2 where t2.a = t.a)
--this does not insert a row
insert #tmp
select a, b, c
from (
select 1 a, 'd' b, 'nnnn' c) as t
where not exists (select * from #tmp t2 where t2.a = t.a)
select *
from #tmp
drop table #tmp
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2007 at 10:33 am
Sorry, we don't check to see if records exist, we simply insert them as part of changes otherwise your approach would work very well. What I showed is the select criteria because a given table may or may not have data from a given version of the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2007 at 1:47 pm
Having attended a few of Itzik's presentations I have to say he is a master at presenting ways around what you think you can't normally do , however, I didn't think we were talking starting to program in T-SQL and the inside sql 2005 books are very good, for sql 2000 I'd recommend the Ken Henderson "Guru" books.
I assumed from the original post the request was for more advanced T-SQL.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 7, 2007 at 2:01 pm
Funny how different people can read the same post and take away two completely different ideas about what was said/asked!! :-))
In my 15 years or so of database development (10 as a consultant), a programmer with 2 years experience is typically VERY poor at writing transact sql queries. A programmer with 2 years experience specifically writing transact sql queries (as opposed to doing VB code, etc) is often only moderately better than the former because many don't/can't take the time and effort to study to become good at writing efficient queries. They are too busy doing real work, meeting deadlines, sleeping, watching tv, family, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 8, 2007 at 8:03 am
I will some what agree with TheSQLGuru in saying that myself being 2+ years experienced progarmmer am mediocre in writing efficient SQL queries as I have other things to concentrate on. And thats the reason i asked for a book that will allow me to have a scenario for which I can write a query. I know what a derived table is, but, cannot use or don't use when is needed.These types of things happen very often.To get pass them i wanted a questionnaire kind of book with sample scenarios.Now, that I am working on SQL 2005,the books recommended in earlier posts line MS press Inside TSQL books are really helping me to know whats new in SQL. I am above average in SQL concepts but average in implementation.So, i felt need of the kind of book i mentioned. Thanks all for your constant replies and if I could get my kind of book, it will be great.
Regards
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply