Can someone tell me what the heck this guy is talking about? 1

[mflancour]

I found this site with a bunch of useful codes and was going over them to see how i might use them. This is the only one that I just could not figure out. Can someone explain what it's doing and how it might be useful to a dba?

Before I kick this one off, please remember that these measures can only be taken into consideration when the full scope of the database model and its implementation are known. 

The Abstraction Indicators try to measure the databases capacity to cope with change and the "profile" of the database to calling applications.  Abstraction in this context means to hide internal schema changes from users.

These indicators are relevant to the entire database only and must be taken with a huge grain of salt.

There are 2 components

   1. View Abstraction
          * I won't go into the all the reasons why views are so good, but simple state that the power of a view to abstract the underlying model is second to none.
          * This indicator considers the most basic relationship possible between 2 tables that a view could practically be considered for.  (PK-FK)
   2. Procedure Abstraction
          * Stored procedures (when used with a security model) effectively create an API for calling applications/users.
          * Based on the principle of CRUD, this indicator assumes that 4 procedures are needed to abstract a table from a user.

Interpreting the results

    * View Abstraction
          o A score over 1.00 usually indicates a well thought out implementation. 
          o Scores below 0.10 need  to be investigated.
    * Procedure Abstraction
          o A score over 1.00 is assumed to have a tight API defined.  This assumes that each table has a CRUD equivalent.
          o Any score under 0.25 usually means that the calling application is doing most of the SQL writing.

Select ProcCount/4.00/TableCount as ProcedureAbstraction
    , ViewCount/0.5/TableCount as ViewAbstraction
from
(
Select count(*) as ProcCount
    , (Select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE') as TableCount
    , (Select count(*) from INFORMATION_SCHEMA.VIEWS ) as ViewCount
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE' AND LEFT(ROUTINE_NAME, 2) != 'dt'
) as X

 

[r937]

i have no idea what he's on about, but i ran it on my database and got

procedure abstraction 0.00568
view abstraction 0.0

yikes!! i need to be investigated!!

r937.com | rudy.ca

 

[mrdenny]

These appear to be fairly arbitary numbers that the author has decide make a good database design.

From what I'm seeing in the code he's got two scores.

For the procedure score he's taking the number of stored procedures divided by 4 divided by the number of tables. He's assuming that if the number of procedures in the database is 4x the number of tables in the database then you have a good database design.

For the procedure score he's table the number of Views divided by .5 divided by the number of tables. He's assuming that if the number of views is less than half the number of tables then you have a good database design.

The number of procedures in a database has nothing to do with the number of tables in the database. You could have 5 tables and a single stored procedure handles inserts into all of them, while another procedure handles updates. That's two procedures for 5 tables. According to this scoring system that's a poorly written database.

Any scorring system like this should be taken with a grain of salf.

These indicators are relevant to the entire database only and must be taken with a huge grain of salt.

I would ignore this information as it appears to be meaningless (to me at least).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mflancour]

Thanks denny. You know any sites that have useful samples of code I can brows?

 

[harebrain]

The command (and I use the term loosely) of English demonstrated in the OP's quoted passage is appalling. That alone should tell you that it's probably crap.

 

[mrdenny]

There's no way to write a script to tell you if your database design is good or not. It takes careful eximanation of the system (database, application, batch processing, etc) by someone with experience in the field to tell you if it's a good design or not.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

Who wrote that garbage?

Instead of this
AND LEFT(ROUTINE_NAME, 2) != 'dt'

you do this

AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') =0


What if I have ( but I don't) procs that start with dt ??? Those won't be included


>>He's assuming that if the number of procedures in the database is 4x the number of tables in the databa

Let me guess? Oh I got it
delete
update
select
insert
proc for every table, how original
How ever most people write joins and will combine tables in procs

<g>


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mrdenny]

Yep. According to his script our main loan system is horrible. While it's not great, it's not as bad as his script says.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[AlexCuse]

What website did you find this on? I really want to read more of the brilliant theory behind developing this script ;-)

I apparently need to be investigated for some of my databases (while some others are looking just great). I would never let a user delete anything from a live database, so that is going to reduce my score by 25% right there.

Also, what about DTS? I use this for the majority of my loads, and since they handle all the insertion I don't need stored procs for these. So there goes another 25%. I am down to an F already, I think I am just going to go into a different line of work :-(

Ignorance of certain subjects is a great part of wisdom