Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2

    Unanswered: Exploding views based on views

    Hi All

    I've been upsizing a database from wholly Access 2k to SQL 2k back-end/Access 2k ADP front-end. I did this manually - partly to get a better feel for SQL, partly because JET-SQL and T-SQL are so very dissimilar that functions in one aren't in the other and partly because some of my Access queries are based on other queries, up to six levels deep. This is because it was easier in Access to break each problem down and build my way to the results that I needed.

    Sorting out the functions was relatively easy (GETDATE() instead of Now(); CAST(FLOOR(CAST [Date-TimeValue] AS float)) AS datetime) instead of Format([Date-TimeValue], "yyyy-mm-dd", that sort of thing). Setting up the views was easy once I worked out the relative levels of them all.

    However, I'm concerned that basing views on views that are based on views is not terribly efficient. I've started exploding some of them so that they are only based on tables for example:
    BEFORE
    Code:
    SELECT TOP 100 PERCENT
    	a.Descr + ' - ' + a.CheckName AS CheckDetails
    ,	a.HowOften
    ,	a.CheckDate
    ,	a.Result
    ,	a.Score
    ,	a.Unit
    FROM
    	dbo.qryResults a
    WHERE
    	(CheckDate BETWEEN CAST(FLOOR(CAST(DATEADD(mm, - 1, GETDATE()) AS float)) AS datetime)
    AND	CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime))
    AND
    	(Descr = 'Customer Service report')
    AND
    	(CheckName = 'New calls')
    ORDER BY
    	CheckDate
    AFTER
    Code:
    SELECT TOP 100 PERCENT
    	b.Descr + ' - ' + c.CheckName AS CheckDetails
    ,	a.Descr AS HowOften
    ,	c.CheckDate
    ,	dbo.CheckSuccess(b.HasSuccess, c.IsSuccessful) AS Result
    ,	dbo.ScoreVal(b.HasScore, c.Score) AS Score
    ,	dbo.ScoreUnit(b.HasScore, b.ScoreUnit) AS Unit
    FROM
    	dbo.tblFrequencies a
    INNER JOIN
    	dbo.tblCheck b
    ON
    	a.Frequency = b.Frequency
    INNER JOIN
    	dbo.tblData c
    ON
    	b.CheckNo = c.CheckID
    WHERE
    	(c.CheckDate BETWEEN CAST(FLOOR(CAST(DATEADD(mm, - 1, GETDATE()) AS float)) AS datetime)
    AND	CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime))
    AND
    	(b.Descr = 'Customer Service report')
    AND
    	(c.CheckName = 'New Calls')
    ORDER BY
    	c.CheckDate
    Is there any value in me doing this? While my employers use a large number of SQL databases for various things, we're woefully undermanned in terms of DBAs and programmers.

    TIA!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    There is value in doing it. Views built on other views can carry along with them various tables that the uppermost query does not care about. For example, if you have a view that involves an Orders table and a sum on a Line_Items table (call it Orders_aggregate), and you want to know who your most active customer is (by count of orders), do you want to query just the Orders table, or the Orders_aggregate view? One includes an extra table scan that you don't really need.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Thank you!

    It's not the most enthralling work to do, but it should keep me looking busy!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I think you can replace
    Code:
    	(c.CheckDate BETWEEN CAST(FLOOR(CAST(DATEADD(mm, - 1, GETDATE()) AS float)) AS datetime)
    AND	CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime))
    by
    Code:
    	(c.CheckDate BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE()
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Hi Wim

    I tried that to start with, but GETDATE() also returns the time value. This throws off the range by a day. That unholy conglomeration of three functions was the easiest way that I found to strip out the time value and just leave the date, but I'd be happy to hear from anyone who's got a neater way of doing it.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I did some Googling on this subject. Seems you are already using one of the known solutions. Another one is:

    Code:
    DATEADD(dd, DATEDIFF(dd, 0, @DateTime), 0)
    I don't know what solution is the most efficient.

    I have a DB2 background. I never had to deal with this problem.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you have SQL 2008, you could also use
    Code:
    convert(date, getdate())

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Wim - I'm amused that I managed to reinvent the wheel! (I also encountered the problem of datetimes after midday converting to the following day, but that was because I misread the help file. Took me a while to track that one down...)

    MCrowley - still on 2000, although I think that shortly we'll be migrating our SQL estate to 2005. Once we've all learned how to get along without DTS packages, anyway.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    still on 2000, although I think that shortly we'll be migrating our SQL estate to 2005.
    Any reason why you would not migrate to 2008 R2?
    Once we've all learned how to get along without DTS packages, anyway.
    Same problem here. We are talking about more than 200 DTS packages. Most of the oldest without a single line of comment. Shiver.
    No-one here has had an SSIS-training, so we don't really know what we're facing.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    The version of software inflicted on the users is a decision made so far above my pay grade that I can't see it with the help of a telescope.

    We are, however, getting SSIS training next month!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by weejas View Post
    We are, however, getting SSIS training next month!
    Envy! Envy!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The DTS to SSIS conversion wizard does a pretty good job. It fixes almost everything that should have run in the rist place, although code that uses undcoumented features can make the wizard gag.

    Purely as an observation (I emphatically recommend the upgrade to SSIS for many reasons), you can actually run DTS packages on later versions of SQL Server. The DTS runtime is part of the Feature Pack.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Oh, that sounds interesting. Thanks!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  14. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Pat, thank you for your reassuring words.

    I love the simplicity of DTS but have also noticed its rough edges. A few packages I wrote, operate on a 2005 database where I can use the far richer 2005 SQL syntax; basically reducing 2000 as a central repository for the DTS packages.
    Quote Originally Posted by Pat Phelan View Post
    ...I emphatically recommend the upgrade to SSIS for many reasons...
    Can you elaborate a bit on that?
    I have read contradicting stories about DTS - SSIS. Haven't got a clue what side to believe, although I am convinced we should bite the bullet and go the SSIS way.
    Last edited by Wim; 06-18-11 at 17:40. Reason: corrected a typo in Pat's name
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Wim View Post
    Can you elaborate a bit on that?
    Can you imagine a SQL related subject that I wouldn't elaborte on if prompted ???

    DTS was envisioned as a "dry run" to get the kinks worked out of the ideas that Geert was trying to address. I don't think that he ever envisioned it being released outside of the SQL lab and certainly not outside of Microsoft when he created the package that would become DTS. The use of the package spread like wildfire amongst the MS-PSS crew, especially the folks that were the "triage specialists" because it was the best thing that they'd ever seen for quickly and easily fixing large data problems.

    An odd sequence of events lead to the package evolving into DTS as it was released with SQL 7. The project was "frog marched" to production for inclusion with the SQL 7 release to keep Microsoft "ahead of the curve" in terms of databases and the tools to support them. Geert wasn't completely happy with the way things were done and wanted to do a lot more work on the project before it was released, but that didn't happen.

    The DTS version that was released with SQL 2000 was a significant improvement, but it was handicapped by the flaws in the product and the vast number of user developed packages that were dependant on those flaws. This is kind of like trying to build a modern apartment complex onto a log cabin... The log cabin was far better than sleeping under the stars especially on a rainy night, but it makes a poor foundation for an apartment building.

    SSIS takes the basic principles behind DTS and builds on those principles in a more structured and stable way. SSIS eliminates some of the unsafe structures, adds some much more "industrial duy" structures, speeds up everything, and best of all it provides far better logging and communications than DTS could support. As one example, DTS provides basic email notification functionality while SSIS provides dependable and relatively full-featured email notifications.

    To put things into perspective, I see DTS as a power tool and SSIS as an industrial grade tool. DTS is for developers and dbas like power screwdrivers and circular saws are to homebuilders. SSIS is for data professionals what jackhammers and bulldozers are to contractors. DTS and SSIS are comparable and they clearly have some overlap, but SSIS can go cheerfully into areas that DTS can't safely venture,

    The DTS to SSIS wizard is pretty good in my opinion. Some DTS packages never should have run in the first place, and some only run on specific versions of the DTS engine and these packages may well cause conversion problems. For DTS packages that "play by the rules", the wizard will almost always successfully convert those packages to equivalent SSIS packages.

    In my experience if you have to manually convert a DTS package to SSIS, you would have had to do the same type of work to get that package to run on another version of DTS so the problem lies in the package implementation not in DTS, SSIS, or the Conversion wizard. Granted that if you don't have the manpower to make a conversion happen you don't really care where the fault lies, it is still important to recognize where that fault lies because it WILL come back to byte you later!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •