July 2, 2008 at 5:01 am
I have inherited a database which is comsuming all the memory allocated to SQL when certain jobs are run. One in particular is horrendous - it runs about 5000 procedures which have loads odf "ands", "ors" and "nots" in them. They are run serially, and they all have slightly different variations of this.
I am at a loss to know where to start quite honestly.
Any advice would be gratefully received.
July 2, 2008 at 5:16 am
If you want to improve performance by adding indexes and keys to the tables - then a good start would be chapter 3 of "Inside Microsoft SQL Server 2005: T-SQL Querying" - its a bit complex, but its worth ploughing through. What it shows you is how to isolate the sql statements which are causing the most waits ( or most cpu, or most memory etc) - then you can concentrate on making those statements more efficient. The second half of the chapter is on index tuning, but I guess if the thing is currently non-sargable then just adding some indexes in the right places will help.
Its not a quick solution but you'll have a good understanding of performance tuning after reading this.
July 2, 2008 at 5:25 am
Could you post a particuarly nasty example?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2008 at 7:48 am
And and Not can quite often be dealt with by a good index. Or is the problem, most often. One of the more efficient ways to deal with Or in many cases is to replace it with a Union/Union All statement. Makes the code a bit harder to maintain, but can be much faster.
(I recently helped a guy to reduce a query from taking an hour to taking 2 seconds, by replacing an Or statement with a Union statement.)
There are other ways to accomplish the same thing, but I'd have to see some sample code to know what's applicable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 8:45 am
Thanks for the reply.
On closer inspection, they are not straightforward or, ands and nots. The main query has this:
WHERE ( dbo.vuln_Or( dbo.vuln_And( dbo.vuln_And( T0.T337, T0.T302 ),
T0.T589 ),
dbo.vuln_Or( dbo.vuln_And( dbo.vuln_And( T0.T194,
dbo.vuln_And( dbo.vuln_Not( T0.T5 ), T0.T62 ) ), T0.T590 ),
dbo.vuln_Or( dbo.vuln_And( dbo.vuln_And( T0.T194, T0.T22 ), T0.T591 ),
dbo.vuln_Or( dbo.vuln_And( dbo.vuln_And( T0.T194, dbo.vuln_And( T0.T5,
T0.T62 ) )
, T0.T592 ), dbo.vuln_Or( dbo.vuln_And( dbo.vuln_And( T0.T196, dbo.vuln_Not( T0.T128 ) )
, T0.T593 ), dbo.vuln_And( dbo.vuln_And( T0.T196, T0.T62 ), T0.T592 ) )
) ) ) ) ) = 1
And to gove an idea of the functions (all are pretty similar):
CREATE FUNCTION dbo.vuln_Or (
@p1 tinyint,
@p2 tinyint
) RETURNS tinyint
AS
BEGIN
DECLARE @retVal tinyint
DECLARE @truthTable char(42)
SET @truthTable = '111111012345201333330143444015345501234560'
SET @retVal = CAST( SUBSTRING( @truthTable, (((@p1 - 1) * 7) + @p2), 1 ) AS tinyint )
RETURN @retVal
END
GO
The whole thing has ground to a halt, as memory has been maxed out.
TBH, I don't think this belongs in the db!!
July 2, 2008 at 9:10 am
Looks like some incredibly arcane lookup table that seems to be getting called recursively. My initial reaction would be - there has GOT to be a better way to do this! That much indirection has got to be killing perf quite a bit.
I kind of see a few options involving a lookup table (an actual one), and a temp table to do some calculations, but we're missing quite a few pieces before we could make this cleaner.
Just to give you a nudge in the right direction. If you were to create your truth table as an ACTUAL table, with indexes, you might end up with a table that looks like:
P1 P2 result
1 1 1
1 2 1
2 1 1
2 2 2
etc...
index the crap out of that, and you could have a bunch of derived table statements to pull all of the OR's in a whole lot faster.
Using something like a CTE or a series of CTE's - you should be able to whittle that down to something manageable.
If you get stuck - try posting ALL of the specifics as to the functions, and perhaps some sample data so that we can help you with that. Here's a good guideline to follow on that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 2, 2008 at 9:57 am
It looks to me like someone was trying to build some sort of procedural many-to-many system, without understanding how to do that the right way in a relational database.
With some sample data, and the rest of the functions, I'm sure it could be turned into something that will work a whole lot better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 12:37 pm
OleHank (7/2/2008)
DECLARE @retVal tinyintDECLARE @truthTable char(42)
SET @truthTable = '111111012345201333330143444015345501234560'
SET @retVal = CAST( SUBSTRING( @truthTable, (((@p1 - 1) * 7) + @p2), 1 ) AS tinyint )
<Stares>
Oh my, boolean algebra in SQL without using boolean operators. Now I have seen everything.
I think we're going to need a lot more information to help with this one. Do you have any idea what this is supposed to do? Is the original 'developer' still around?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2008 at 12:58 pm
thanks for all of the replies.
No, the developer is not still around. He is about 4 developers ago!! But I believe he was a c programmer!!
The code is to run a series of tests, and then compare the combination of results of the tests against some known combinations. The truth table for the function I posted is:
The table T0 just contains integer values, and is 1000 columns wide.
| T F E U NE NA
-----|-------------------
T 1 | T T T T T T
F 2 | T F E U NE F
E 3 | T E E E E E
U 4 | T U E U U U
NE 5 | T NE E U NE NE
NA 6 | T F E U NE NA
Where T - True, F= False, E = Error, NA = Not Applicable NE = Sorry I've forgotten (I'm at home now so don't have access to the code or docs).
July 2, 2008 at 1:22 pm
You have my sympathy. As expected, a procedural programmer with no clue how relational databases work.
It can be done as a series of joins, which will be faster, but I don't currently have time to write that for you. The conversion is, quite possibly, a semi-major project all by itself.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 1:43 pm
Wow...
I'm with Gus, but let me go one step further. With 5000 procedures (I assume that's like saying "a bazillion"), and all those ugly WHERE clauses, etc, etc, I would strongly recommend turning it into a mini-project... it will take much less time to redefine the problem and redevelop the code from scratch than it will be to try to salvage any of the current mistake. Don't even look at the old code... define the problem and rewrite the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2008 at 1:59 pm
Agreed. Sorry, but I don't think we're going to be able to help very much here, except to offer commiserations and sympathy. Much as I'd love to dive into your code, it's a little beyond what I can manage.
It might be a good time, if you don't have a good SQL resource in-house to get a competent consultant in to help you rewrite.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2008 at 2:45 pm
Jeff Moden (7/2/2008)
Wow...I'm with Gus, but let me go one step further. With 5000 procedures (I assume that's like saying "a bazillion"), and all those ugly WHERE clauses, etc, etc, I would strongly recommend turning it into a mini-project... it will take much less time to redefine the problem and redevelop the code from scratch than it will be to try to salvage any of the current mistake. Don't even look at the old code... define the problem and rewrite the code.
My concern with the idea of ignoring the legacy code is that those tables and functions are obviously some sort of attempt to create rules. Decoding the rules is going to be a majorly laborious process, most likely. There's probably not a good way to even find out all the rules without going through the existing code and data. That, by itself, is probably a week's work, or more.
Once that's done, and it's all deciphered, then scrap it, burn it, dance on the ashes, cast a curse of impotence on the original coder and all his descendants for a thousand generations, etc., and start from scratch on the code.
But the first step is going to have to be going through that code and reverse-engineering what the heck it's trying to do. I do not envy (there's gotta be a concept-inversion for envy added to the English language) the OP that job. It's gonna suck.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 3:02 pm
GSquared (7/2/2008)
cast a curse of impotence on the original coder and all his descendants for a thousand generations
Ummmm... isn't THAT a bit of an oxymoron? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2008 at 4:02 pm
I know I'm commenting late, but at least OleHank titled the post appropriately.
I do not envy your position. :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply