September 11, 2011 at 4:07 pm
CELKO (9/11/2011)
One page? Define "page". Are you still printing everything out on greenbar paper? Where the hell are you still finding greenbar paper? Hilarious how you complain about doing things old ways and then use archaic terms.[/quote]
You said "green bar" and not me 🙂 Actually, given 10-pitch type fonts and 10/12 leading we get 50-60 lines per page. This is a normal person's field of vision. That is why the page is that size! The actual psychological has to do with the visual field. You need to be able to see the begin and end of blocks of code when debugging. This why you align BEGIN-END or {} pairs vertically, put a COBOL paragraph on one page, etc.
I spent two years at AIRMICS consolidating all of the research on Earth on this stuff for DoD. The usual test was to put a known bug into code and then time how long it for noob and experienced programmers to find it. If the module was split across a page boundary, you added 8-12% to the cost of maintaining a system. By simply writing a text formatter that aligned the code, we saved big bucks on software maintenance. Think tens of millions of 1980's dollars.
Let me give you a T-SQL example. Write a long procedure with a SELECT TOP clause query and leave off the ORDER BY. If the query is on one screen or one page (in the visual field of the user), an experienced programmer (5 years plus employment) can find it. UNLESS the query is split over a "page" and then you can add 25 minutes to 3 hours extra. If they are less experienced, it was never found. To screw up the experienced T-SQL Programmers, mess up the order of the columns in the ORDER BY or leave one out.
I like this bug because, some days it works fine and some days, it does not. You can run tests over and over, and not see it until the internal state of this one machine and the physical ordering of the table changes a bit.
This will hold until we get "Human 2.0" eyes and brains 🙂
[/quote]
Sorry, I don't buy it. A4, "Letter" size, "Legal" size and Greenbar are all different sizes. Which is the "normal" field of vision? The reason paper is the size it is seems lost to history. I haven't printed anything out to debug in years. Formatting and commenting are far more importants than length of code when it comes to debugging.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 11, 2011 at 4:45 pm
CELKO (9/11/2011)
>> Sorry, I don't buy it. A4, "Letter" size, "Legal" size and Greenbar are all different sizes. Which is the "normal" field of vision? The reason paper is the size it is seems lost to history. <<No, actually, I have a history of paper sizes! The A, B and C series in ISO Standards are based on a 1:v2 because the series can be formed by cutting a sheet in half. The A0 sheet is ~1 square meter. This scheme goes back to Italy and Renascence.
Visually the 1:f or Golden Ratio is what people like. That was also tried, and shows up in 345mm film. Our silly 8.5x11" actually beat out 7x10" (Monarch size) in colonial times by decree. I cannot remember the phsycial reason for 132 column green bar, but had to do with people were cheap and machines were expensive and the size of ledger pages in the Middle Ages. Remember having to use a ruler to read a lien because it was too long for your field of vision?
>> I haven't printed anything out to debug in years. Formatting and commenting are far more importants than length of code when it comes to debugging. <<
Sure, we use a screen now. We have better tools than magic markers and rulers. But how often do you hit a button that shrinks or expands a begin-end block in the code? Why? So it can make the code you want to look at fit into your field of vision!
You have the history? Post some references. As I believe the web kids say these days "Citation needed".
How often do I hide code? I can't think of a time when I have. It isn't needed. Not if formatting and commenting are done well.
How often do you do it? From your comments I'd think all of your code fits on one screen.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 11, 2011 at 6:31 pm
sqlsatyam (9/11/2011)
Thanks to everyone who replied to my post.... Feels good to be in the community where experts are ready to help out....
I think that you'll find a lot of that here.
Regarding the issue i posted yesterday....i modified my cursor to use the Current Of option in the update statement (changed it from a fast-forward cursor to a normal cursor and took out the group by clause).... This change reduced the time of the curosr from about 4hr30mins to around 4hr10mins....
270 minutes to 250 minutes... how would you like to see it run in under a minute? (To borrow Joe's analogy, we'll use a bulldozer instead of tweezers to move the sand (data).)
Following is the scrubbed code for the SP..... It does a select on a table (about 100000 rows in that table) into a temp table, Concatenates the contents of the temp table (again in a cursor 🙁 ) and returns the output....And yes we are using SQL Server 2008....
We cant modify the sp in any way, so I am hoping we can modify the main cursor to use some kind of cte (or while loop ??) to avoid this sp call...
I sure wish that there was a system option: sp_configure 'Allow While Command', 0;
So, with nested cursors, your 100,000 rows are running in 4hr? Hmm... you ought to do a presentation at DevConnections or PASS Summit about how you managed to get that much performance out of a cursor! :w00t:
You still didn't post table structures/indexes or sample data. Please read the first link in my signature for how to do this. Bonus to you: you get tested code back. Without it, we're just guessing. Many folks won't even look at your code without sample data and expected results so that they know what to expect.
FYI, quote this message and look how I used the IFCode for TSQL code - it makes it a lot easier for everyone to copy/paste the code. And, since you are trying to get people to help you out (for free), making it easier for us ends up helping you.
CREATE PROCEDURE [dbo].[user_sp] (
@InputParameter1 varchar(20) = NULL,
@InputParameter2 varchar(128) = NULL,
@OutputParameter1 varchar(4000) OUTPUT,
@Return_Code int OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@sql nvarchar(MAX),
@paramlist nvarchar(4000),
@spos int,
@epos int,
@NumberOfInputFields smallint,
@Field varchar(1),
@Lookup_Effective_Date datetime ,
@Var6 varchar(4000),
@Var7 smallint,
@Var8 varchar(20);
set @Lookup_Effective_Date datetime = '12/31/2009';
set @Var6 varchar(4000) = ' ';
CREATE TABLE #Temp_Ref(
Col1 varchar(20) NOT NULL,
Col2 smallint NOT NULL);
SELECT @sql =
'INSERT INTO #Temp_Ref (
Col1,
Col2)
SELECT
Col1,
Col2
FROM
dbo.Reference_Table
WHERE
Col1 = @InputParameter1 AND
Col2 = @InputParameter2 AND
Start_Date <= @xLookup_Effective_Date AND
End_Date >= @xLookup_Effective_Date'
-- skip leading 'Ç' delimiters if any
SET @spos = 1;
SET @epos = 1;
SET @NumberOfInputFields = 1;
SET @Field = '';
WHILE @epos > 0
BEGIN
IF @NumberOfInputFields < 9
SET @Field = '0';
BEGIN
SET @sql = @sql + ' AND ' +
'(Field' + @Field + CONVERT(VARCHAR(2), @NumberOfInputFields) +
'_Text' + '=''%'' OR HASHBYTES(''MD5'', Field' + @Field + CONVERT(VARCHAR(2), @NumberOfInputFields) + '_Text)' + '=';
SET @epos = CHARINDEX('Ç', @Var6, @spos);
IF @epos = @spos
SET @sql = @sql + '''HASHBYTES(''MD5'', '')';
IF @epos > @spos
SET @sql = @sql + 'HASHBYTES(''MD5'', ''' + SUBSTRING(@Var6, @spos, @epos - @spos) + '''))';
END
IF @epos != 0
SET @spos = @epos + 1;
SET @NumberOfInputFields = @NumberOfInputFields + 1;
END;
BEGIN
SET @sql = @sql + 'HASHBYTES(''MD5'', ''' + SUBSTRING(@Var6, @spos, LEN(@Var6) - @spos + 1) + '''))';
END
SELECT @paramlist = '@xInputParameter1 varchar(20),
@xInputParameter2 varchar(128),
@xLookup_Effective_Date datetime'
EXEC sp_executesql @sql, @paramlist, @InputParameter1, @InputParameter2, @Lookup_Effective_Date
SELECT @Var7 = MIN(Col2) FROM #Temp_Ref;
-- @@Var7 can be NULL
IF @Var7 IS NOT NULL
BEGIN
DECLARE Conformed_Cursor CURSOR FAST_FORWARD
FOR SELECT Col1 FROM #Temp_Ref
WHERE Col2 = @Var7
ORDER BY Col1;
OPEN Conformed_Cursor;
FETCH NEXT FROM Conformed_Cursor INTO @OutputParameter1;
IF @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Conformed_Cursor INTO @Var8;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @OutputParameter1 = @OutputParameter1 + 'Ç' + @Var8;
FETCH NEXT FROM Conformed_Cursor INTO @Var8;
END;
END;
CLOSE Conformed_Cursor;
DEALLOCATE Conformed_Cursor;
END;
IF @OutputParameter1 IS NULL
BEGIN
SET @Return_Code = -1;
END
ELSE
BEGIN
SET @Return_Code = 0;
END;
END;
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 12, 2011 at 12:17 am
If you manage to lose the dynamic sql part, you may be able to write this proc using a single sql statement !
(IMO the MD5 handling will not be indexable anyway, so will be a non-sargable argument, and may only be executed very late in the queries execution, so you may as well work out a hard coded solution for that sequence if the numbers aren't to high)
In that case, you can get rid of the sproc, which may help out losing the cursor in your proc from the original post.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2011 at 2:12 am
If now we could focus on the original issue, that would be great.
-- Gianluca Sartori
September 12, 2011 at 3:49 am
😀
Edit: which now makes absolutely no sense due to a prior message being altered. Oh well...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 12, 2011 at 6:28 am
CELKO (9/11/2011)
Darn, I need to rewrite 150+ reports of mine. Too bad they actually do what they need, and fast too :-D.
You should not be writing reports in SQL at all. It is a data retriever tool, not a report writer. You grab the query results (which should be in a normal form, without any formatting and minimal calculations) and throw it over the wall (tier) to an application, report writer or a report server. The sure sign of someone who "just do not get it" is code loaded with CONVERT(), CAST(), STUFF(), PAD() and string functions.
Odd... I have to throw data over the proverbial wall to one of our applications, and oddly enough, that application needs to receive that data in a format it can work with. Since the format it can work with is different than the way the data is stored, there are a number of conversion functions in play. Fortunately, my code works quickly and provides data faster than the receiving application can process it.
-Ki
September 12, 2011 at 8:21 am
I would need source tables and sample data to help convert that proc to an inline function. It looks like it could be converted to a simple Cross Apply on the source table for the cursor, but I'd need to have a test environment before I could be certain on that point.
If it can (I'm about 80% certain it could), you should be looking at it running in a few seconds instead of hours/minutes/whatever.
- 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
September 12, 2011 at 8:05 pm
Okay, the balls in your court. There are two guys here that have stated that they believe this will run in under a minute. All we need is sample data... waiting on you to provide, and then there will be some awesome sql learning going on in this thread!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 13, 2011 at 12:14 am
... All we need is sample data... waiting on you to provide, and then there will be some awesome sql learning going on in this thread!
That wouldn't need to be the full 2M rows, just a little sample of a couple of rows would do, including that ddl to create all tables ( and their indexes and FK ) used and a couple of matching rows.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 14, 2011 at 8:13 am
Stefan Krzywicki (9/11/2011)
CELKO (9/11/2011)
While temp tables in the Sybase/SQL Server model can be indexed, constrained, etc. nobody does.Speak for yourself. I do and I know others who do.
Ditto...I've also had to cudgel the developers over the head because they forgot to create proper temp tables and for those that did, they forgot to create indexes on their temp tables and slowed down the system. CTE's are elegant, no doubt, but in the end, performance is always at the back of my mind when I'm writing scripts and massive volumes of data need to be indexed so I use temp tables. The code is not as pretty as a CTE, but it's understandable (imho) and faster.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
September 14, 2011 at 3:07 pm
CELKO (9/11/2011)
The quote is not exactly right, but Nick Wirth once said that a "good programming language makes good programming easier and bad programming harder. But it cannot cause the first or prevent latter."
Wirth probably did say something like that - he always was a great believer in flying pigs (for example Tenenbaum, Knuth, and Wilkes can't all have been wrong in their views on the dangerousness of the Wirth-Dijkstra axis views on formal verification, which are perhaps best illustrated by Knuth's famous statement "Beware of bugs in the above code; I have only proved it correct, not tried it"[*]).
Anyway, he was wrong again when he said that; it is futile for any language designer to deny Flon's law, which states "There is not now, nor has there ever been, nor will there ever be, any programming language in which it is the least bit difficult to write bad code", which is a fundamental law of computer science.
This law (despite its attribution to Flon) was stated in slightly different forms by many people; possible Alan Schwartz was first to say it, or maybe it was Kenneth Iverson, or possibly Christopher Strachey; I've heard Joe Stoy say it in one form or another, and C.A.R.Hoare, and Robin Milner, and David Turner but I don't think any of them was the originator. And I know Cliff Jones believes it too (as he and I once agreed that VDM was a hacker's paradise - we were using "hacker" in the sense "a person who rapidly churns out reams of incomprehensible code").
Of course Wirth got things right a lot of the time too: "Software's girth has surpassed its functionality, largely because hardware advances make this possible" is as true now as when he wrote it, and "Software gets slower faster than hardware gets faster" is still as true as it was when he said it.
[*]Knuth also had a go at Dijkstra on another front: in 1974 he published "Structured Programming with go to Statements" in ACM Surveys.
Tenenbaum published his "In Defense of Program Testing or Correctness Proofs Considered Harmful" in Sigplan notices in 1976, and Wilke's attack on "the cult of verification" was in his keynote address at 2nd ICSE in October that same year (printed in IEEE ToSE in December).
I haven't read Paul Abrahams' "Structured programming considered harmful" which sounds from its title like another attack on the Dijkstra-Wirth school of thought but may have been a joke rather than a real attack (it was published in SYGPLAN Notices in April 1975, a date which makes me suspicious) so I haven't added him my list of those who disagreed with Wirth's views.
Tom
September 14, 2011 at 6:15 pm
Thank you all for your valuable feed back... Apologize for the delay in response.... I will make sure that from the next time I will post the code in a more readable form and include DDL's \ Sample data...
Regarding the issue I have posted, I could reduce the execution time from 4hr 30 mins to around 1hr 30 mins by moving the update to the temptable outside of teh cursor.... I created a temptable (with index 🙂 ) and used that table to store the values the SP was returning within the cursor.... I then joined the new temptable with the table outside the cursor to update...
We could probably tune this more by following some of the suggestions posted here, but the higher up's are happy with the time it is taking right now 🙂 .... They assigned me three more similar scripts to tune now 🙁 .....
This code wasn't written by me... I am just the new dba on the block tasked with tuning somebody else's code 🙁 ..
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply