April 8, 2003 at 8:46 am
Two Words: Virtual Tables
It is popular to say "avoid temp tables and cursors if at all possible", but this is difficult to do when you need a result that requires an INNER and OUTER join. Since you cannot combine INNER and OUTER joins in the same simple SQL statement, most people spin up a #Temp table or a cursor. #Temp tables and cursors have performance and maintenance issues (like HUGE TEMPDBs...) and the SET NOCOUNT ON issue too. So how do you get around this?
VIRTUAL TABLES!!!
Here is a real world example:
Old SP:
CREATE PROCEDURE dbo.p_GetAttributesAndSet
@User_Or_User_Group_Id char(38),
@Attribute_Group_Instance_Id char(38)
AS
BEGIN
SET NOCOUNT ON
/* first find all the attributes definition for this instance and store off */
/* save the instance description too */
Create table #ATTRIBUTE_DEF_SET(
Attribute_Definition_Id char(38),
Attribute_Definition_Nm varchar(255),
Attribute_Group_Instance_Desc varchar(255),
Attribute_Group_Instance_Id char(38))
insert #ATTRIBUTE_DEF_SET(Attribute_Definition_Id,
Attribute_Definition_Nm,
Attribute_Group_Instance_Desc,
Attribute_Group_Instance_Id)
select ad.Attribute_Definition_Id,
ad.Attribute_Definition_Nm,
agi.Attribute_Group_Instance_Desc,
agi.Attribute_Group_Instance_Id
from ATTRIBUTE_DEFINITION ad (nolock),
ATTRIBUTE_GROUP_INSTANCE agi (nolock)
where agi.Attribute_Group_Instance_Id = @Attribute_Group_Instance_Id and
agi.Attribute_Group_Id = ad.Attribute_Group_Id
/* return all attributes definitions and those values that were found for the user */
select ad.Attribute_Definition_Nm,
ad.Attribute_Definition_Id,
ad.Attribute_Group_Instance_Desc,
av.Attribute_Val,
av.MLynx_Identifier_GUID
from #ATTRIBUTE_DEF_SET ad (nolock)
Left outer join ATTRIBUTE_VALUE av (nolock)
on av.Attribute_Definition_Id = ad.Attribute_Definition_Id and
av.Attribute_Group_Instance_Id = ad.Attribute_Group_Instance_Id and
av.User_or_User_Group_Id = @User_Or_User_Group_Id
Order by ad.Attribute_Definition_Nm
drop table #ATTRIBUTE_DEF_SET
END
Rewrite this SP as:
SELECT
ad.Attribute_Definition_Nm,
ad.Attribute_Definition_Id,
ad.Attribute_Group_Instance_Desc,
av.Attribute_Val,
av.MLynx_Identifier_GUID
FROM
(SELECT
ATTRIBUTE_DEFINITION.Attribute_Definition_Nm,
ATTRIBUTE_DEFINITION.Attribute_Definition_Id,
ATTRIBUTE_GROUP_INSTANCE.Attribute_Group_Instance_Desc,
ATTRIBUTE_GROUP_INSTANCE.Attribute_Group_Instance_Id
FROM
ATTRIBUTE_GROUP_INSTANCE INNER JOIN ATTRIBUTE_DEFINITION
ON ATTRIBUTE_GROUP_INSTANCE.Attribute_Group_Id = ATTRIBUTE_DEFINITION.Attribute_Group_Id
WHERE
(ATTRIBUTE_GROUP_INSTANCE.Attribute_Group_Instance_Id = @Attribute_Group_Instance_Id)) ad
LEFT OUTER JOIN ATTRIBUTE_VALUE av
ON av.Attribute_Definition_Id = ad.Attribute_Definition_Id and
av.Attribute_Group_Instance_Id = ad.Attribute_Group_Instance_Id and
av.User_or_User_Group_Id = '@User_Or_User_Group_Id'
ORDER BY ad.Attribute_Definition_Nm
The table "ad" in the second example is the virtual (aka derived) table. The performance enhancement is literally 100x and uses memory and other server resourses more efficently.
April 8, 2003 at 11:17 am
Contrary to popular belief, there are times when temporary tables, cursors, etc. are the best way to go.
Business Rule driven month end reports are often such an example. I've seen some that take hours to run - not because of bad design, but because of the elaborate business rules management has requested.
So, if you have an elaborate calculated process, push it off to a different database on the same server (or a different server).
So if the whole thing crashes, the main db is sound, you only loose the report.
This "processing" database can be set to "truncate log on check point" - and put the log for this db on a RAID 1 or RAID 10 spindle set - NOT RAID 5. Don't use the tempdb. If you do, you could slow down other users.
When the process has completed, push the result back to the main db.
What's the business problem you're trying to solve?
April 8, 2003 at 11:54 am
quote:
Business Rule driven month end reports are often such an example. I've seen some that take hours to run - not because of bad design, but because of the elaborate business rules management has requested.
So, if you have an elaborate calculated process, push it off to a different database on the same server (or a different server).
So if the whole thing crashes, the main db is sound, you only loose the report.
This "processing" database can be set to "truncate log on check point" - and put the log for this db on a RAID 1 or RAID 10 spindle set - NOT RAID 5. Don't use the tempdb. If you do, you could slow down other users.
When the process has completed, push the result back to the main db.
If the logic is that complex, it opens another hotly debated topic; does complex business logic belong in the SP or in the application tier? Database server are EXPENSIVE and inefficient application servers (even by WebLogic/WebSphere standards)
BTW: What's wrong with RAID 5 (with hardware support - of course)?
April 8, 2003 at 2:28 pm
Yes, complex business rule jobs often are best put on dedicated report layers, but in practice, I usually see the job falling to experienced SQL developers. These are guys & gals who can put on a suit, and talk to the business in their language. The ideal is to pull data from OLTP system onto a dedicated reporting database system. Keep in mind that these business rules driven reports often are considered critical to senior mgmt, and thus often get $$$ for necessary hardware to quicken the solution. In their minds, time is money. Always keep your focus on the business, not pure technology, and you will come out ahead. Works for me. $$$$
RAID: I'm sure there are other threads discussing RAID concepts, but RAID 5 writes are overhead intensive. Not a DBA's best friend. If the process is mainly read intensive, and not write intensive, RAID 5 is OK. log spindles are write sequential, so mirrored sets (1 or 10) are faster.
What's the business problem you're trying to solve?
April 17, 2003 at 12:49 pm
Why don't you take the Transaction log back up periodically.
When ever you run the sp,Chk the trans log size before and after and exec the backup log and truncate the inactive transaction from the log file.
Is this help?!
April 17, 2003 at 1:00 pm
quote:
Why don't you take the Transaction log back up periodically.When ever you run the sp,Chk the trans log size before and after and exec the backup log and truncate the inactive transaction from the log file.
Is this help?!
It is not a good practice to allow developers the freedom to alter the system's records and transaction logs. The user id that you are using to access the data should be able to do administrative tasks; that's a freakin' disaster waiting to happen. Backups are what DBAs do and should do. It is VERY easy to do this so that you don't fill up the transaction logs. why would you choose to do this in a way that causes problems?
April 17, 2003 at 2:00 pm
I didn't say that the developer should exec the Backup cmd.The DBA knows that how often sp exec and he could schedule a job in sql to take care of the Trans log backup.
April 17, 2003 at 3:48 pm
ok, but the point is that data that is ending up in these transaction logs are calculations that are used in (essentially) a report. This "noise" increases the size of transaction logs, but it isn't a transaction, it's a calculation. If I need to restore a database from the last backup and the tranaction logs, how much crap do I have to store and restore because of the calculations?
If you need to do the calculations on the DB server (I'll bite my tongue here), then use the right syntax and approach to the problems. Design the process to log transactions, not calculations.
April 21, 2003 at 7:22 am
Is Creating a "Work" database for these Permanent Temporary tables out of the question?
Set backup plan to simple and truncate the transactions often. You will not add these transactions to you "Production" database
You can also use this DB for other things as well. Store common SP and functions etc.
April 21, 2003 at 7:43 am
Sure you could create a work (reporting) database. This is a common solution that works well in many cases.
You still need to think about how you are writing your Stored Procs. In this case, while you could write the SP using a reporting/work database, you could rewrite the code to do it better.
Another HUGE drawback to this solution for this problem is that you are splitting the programming logic for the original database into two databases. This make maintaining and understanding the code much harder. Again, this added complexity is unneccessary if the code is written well.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply