March 6, 2012 at 10:14 am
yes I have read it quickly but will read it at leisure sometime
However there was a topic few days back and that was my first time on this site and I said it need a With Command and the Pivot Command to solve it without realising who jeff Moden was. I didnt have time to post my solution but some one else had using Temp Table and Pivot (Shoud we have used With Command or Temp Tables ?)
But the discussion went on for few more instnces and he insisted the solution didnt include the PIVOT command I could clearley see it did. I am confused by that
March 6, 2012 at 10:17 am
I sort of remember that thread too. I think that Jeff was trying to say that the "best" solution did not need to use a pivot. I don't recall if he ever posted his way of doing that without a pivot or not but I know that several others had posted solutions using a pivot.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2012 at 10:24 am
Then and now, I try to solve the problem mathematically and then write the TSQL
That would always get a solution but might be heavy on resources
March 6, 2012 at 11:48 am
siva 20997 (3/6/2012)
yes I have read it quickly but will read it at leisure sometimeHowever there was a topic few days back and that was my first time on this site and I said it need a With Command and the Pivot Command to solve it without realising who jeff Moden was. I didnt have time to post my solution but some one else had using Temp Table and Pivot (Shoud we have used With Command or Temp Tables ?)
But the discussion went on for few more instnces and he insisted the solution didnt include the PIVOT command I could clearley see it did. I am confused by that
Siva...go back to that thread ...... I think it was this one
http://www.sqlservercentral.com/Forums/FindPost1261326.aspx
reread it from the beginning...and at the end you admit you were referring to the "wrong code"
if you want to reopen the discussion suggest you create new thread
kind regard
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 6, 2012 at 12:11 pm
siva 20997 (3/6/2012)
Then and now, I try to solve the problem mathematically and then write the TSQLThat would always get a solution but might be heavy on resources
What people are trying to say is that you should always write your code with the idea of efficiency and scalability.
March 6, 2012 at 12:16 pm
Ok I get it now. You had posted the solution earlier on which I had not tested or Sree had accepted
The thread continued for another 2 pages after my comments
and abishalt had some code and then you followed by Abishalt
I thought that was the solution which was based on my Pivot suggestion
You asked me to show how I would do the varying months etc
I didnt have time to do it then but Abishalt did it with a String parameter
which was new to me. Anyway I thoght that was the solution
I would have done it sligtley differntley because that needed 3 Steps
The way I do things are sligtley differnt because I am trying to shift the buiness logic
into TSQL and have no logic in the middle tier at all
I was hoping by slowley getting involved I could discuss that
butr I have been shot down here for my approach and I think I better keep quiet
March 6, 2012 at 12:26 pm
siva 20997 (3/6/2012)
Ok I get it now. You had posted the solution earlier on which I had not tested or Sree had acceptedThe thread continued for another 2 pages after my comments
and abishalt had some code and then you followed by Abishalt
I thought that was the solution which was based on my Pivot suggestion
You asked me to show how I would do the varying months etc
I didnt have time to do it then but Abishalt did it with a String parameter
which was new to me. Anyway I thoght that was the solution
I would have done it sligtley differntley because that needed 3 Steps
The way I do things are sligtley differnt because I am trying to shift the buiness logic
into TSQL and have no logic in the middle tier at all
I was hoping by slowley getting involved I could discuss that
butr I have been shot down here for my approach and I think I better keep quiet
If you keep quite you won't learn anything. You need to ask questions if you want to improve. The key is to not get defensive. Be willing to try new ways of accomplishing the same task. That is how you will discover better or not so better ways of accomplish a task. As I read through this thread it appeared to me that you were adament about defending the way you solved the problem instead of listening to what others suggested and testing out the alternatives.
One thing you should consider is creating a sandbox database and testing your solutions on the million row table. This is a way to ensure that the code you write is scalable. Also, remember set-based doesn't necessarily mean all in one statement. Sometimes it helps to break things down into smaller pieces, the divide and conquer method.
March 6, 2012 at 12:43 pm
I am pursuing my methods and developing applications
The T SQL Logic which every one seems to want is effient code
What they dont seem to reaalise is that it is becoming a programming lanaguage on its own
I didnt like using temp tables because I felt I had to maintain them, delete them etc
With Command did all that for you and since then I have been hooked on writing application logic with in the TSQL
before that people would have been writing that in the middle tier.
Even now when say I am writing applications just with SPs few whome I have spoken to belive I am doing the wrong thing
All what I need the application programmer to do is call the stored procedures for differnt tasks without undertanding it
I have written accounting system simialr to Sage entirley using TSQL
I dont think about the Speed and effiency there. I think about the logic
Analyse the problem matahematicaly,
write the steps required to solve the problem
and then write the code
I wanted to discuss how I am doing this on this forum but I have spent half the day today defending my self. I dont need that
So most of my TSQL looks the same
March 6, 2012 at 12:53 pm
siva 20997 (3/6/2012)
I am pursuing my methods and developing applicationsThe T SQL Logic which every one seems to want is effient code
What they dont seem to reaalise is that it is becoming a programming lanaguage on its own
I didnt like using temp tables because I felt I had to maintain them, delete them etc
With Command did all that for you and since then I have been hooked on writing application logic with in the TSQL
before that people would have been writing that in the middle tier.
Even now when say I am writing applications just with SPs few whome I have spoken to belive I am doing the wrong thing
All what I need the application programmer to do is call the stored procedures for differnt tasks without undertanding it
I have written accounting system simialr to Sage entirley using TSQL
I dont think about the Speed and effiency there. I think about the logic
Analyse the problem matahematicaly,
write the steps required to solve the problem
and then write the code
I wanted to discuss how I am doing this on this forum but I have spent half the day today defending my self. I dont need that
So most of my TSQL looks the same
What we are trying to make clear to you is that your code is worthless if it is not efficient. If your car can get you from point A to point B, but it is faster to walk... Why use a car? We do not write applications in SQL. We use SQL when we write applications as an efficient way to access and store data.
It is good that you put together a plan first to get from point A to point B. that is the sign of a good developer. However, once you have that plan it is essential, not "nice", but essential to make sure that the path is the best way that you know to get from point A to point B. Speed is only a part of it. Efficiency involves making sure that you are utilizing the resources as best you can. You may accept a slow query and think it is getting the job done just fine, until it crashes because you are out of memory because you did not filter the results early on. Most query tuning does not have an effect on the end user at all. It is tuning that allows you to use your server as best you can.
Jared
CE - Microsoft
March 6, 2012 at 12:54 pm
I dont think about the Speed and effiency there. I think about the logic
Analyse the problem matahematicaly,
write the steps required to solve the problem
and then write the code
And there is nothing wrong with your approach. People around here tend to get a bit hasty when you keep saying that you basically ignore performance. Getting the logic correct is only half the battle. You not only need the correct logic you need to do it fast.
The paradigm shift in your thinking is stop thinking about what you want to do to a row and instead think about what you want to do to a column. It is a difficult mental migration for a developer to make. Trust me on this one, I have been a developer for quite a long time. As with any type of programming/development you have to continually update your skill set or you will just keep writing the same old code that works no matter how much the rest of the world has passed you by.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2012 at 1:03 pm
siva 20997 (3/6/2012)
I am pursuing my methods and developing applicationsThe T SQL Logic which every one seems to want is effient code
What they dont seem to reaalise is that it is becoming a programming lanaguage on its own
I didnt like using temp tables because I felt I had to maintain them, delete them etc
With Command did all that for you and since then I have been hooked on writing application logic with in the TSQL
before that people would have been writing that in the middle tier.
Even now when say I am writing applications just with SPs few whome I have spoken to belive I am doing the wrong thing
All what I need the application programmer to do is call the stored procedures for differnt tasks without undertanding it
I have written accounting system simialr to Sage entirley using TSQL
I dont think about the Speed and effiency there. I think about the logic
Analyse the problem matahematicaly,
write the steps required to solve the problem
and then write the code
I wanted to discuss how I am doing this on this forum but I have spent half the day today defending my self. I dont need that
So most of my TSQL looks the same
And again, you are getting defensive. I'm not saying it is wrong to put the business logic in the database, nor am I saying it is right either. If you want to learn to write better code, you have to stop defending your methods to the extent that others stop even trying to help you.
Yes, there are going to be poeple that will tell you it is wrong to put everything in the database. There are others that will tell you it is fine. I have worked with both types of people.
If the code you have written works correctly, great. The next step is to ensure that it is scalable. This may mean changing how it accomplishes the task(s) needed. Your original code may be fine for a few hundred or even a few thousand rows of data, but if the amount data starts hitting hundreds of thousands or millions of rows and the users start complaining about the system being slow and unresponsive, then the code is not scalable. This is what we are trying to get you to start thinking about when you are developing code.
Personally, I like to develop the code to get the correct answer first, then start working on scalable code. This way I have something to test against. As I rewrite the code, I check the new code against what worked. If it consistantly returns the same values/answers/whatever and does it faster with better scalability, then I have what I was looking for and new baseline to work from should I need to improve the code more.
I will say that I don't agree with this statement: All what I need the application programmer to do is call the stored procedures for differnt tasks without undertanding it. I do believe that the developers need to understand what the procedures they are calling are doing. If they don't understand what the procedure is doing, how do they know if what they are doing is actually correct? How do they test their code to ensure accuracy?
March 6, 2012 at 1:19 pm
Dear Siva,
No one is picking on you. All tehy say, you MUST consider speed and efficiency of the application(or code) while designing or suggesting
Here is the set-up on a million row table.
SET NOCOUNT ON ;
IF OBJECT_ID('TempDB..#SivaTest') IS NOT NULL
DROP TABLE #SivaTest
GO
CREATE TABLE #SivaTest
(
iD INT PRIMARY KEY,
igroup INT
);
; WITH Tens (N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
)
, Hundreds(N) AS
(
SELECT T1.N
FROM Tens T1
CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N
FROM Hundreds T1
CROSS JOIN Hundreds T2
)
, Millions(N) AS
(
SELECT T1.N
FROM Thousands T1
CROSS JOIN Hundreds T2
)
, Numbers(N) AS
(
SELECT RN = 0
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY N)
FROM Millions
)
INSERT INTO #SivaTest (iD , igroup )
SELECT iD = N , igroup = ( N % 5 ) + 1
FROM Numbers;
DECLARE @id INT
, @Grp1 INT
, @Grp2 INT
, @Grp3 INT
, @Grp4 INT;
PRINT '********* HowardW''s COUNT Method ********* ';
SET STATISTICS IO, TIME ON;
SELECT @id = COUNT(*),
@Grp1 = COUNT(CASE WHEN igroup=1 THEN 1 END) ,
@Grp2 = COUNT(CASE WHEN igroup=2 THEN 1 END) ,
@Grp3 = COUNT(CASE WHEN igroup=3 THEN 1 END) ,
@Grp4 = COUNT(CASE WHEN igroup=4 THEN 1 END)
FROM #SivaTest
SET STATISTICS IO, TIME OFF;
PRINT REPLICATE('-' , 20) ;
PRINT CHAR(13);
PRINT '********* davidandrews13''s SUM Method ********* ';
SET STATISTICS IO, TIME ON;
SELECT @id = SUM(1),
@Grp1 = sum(case igroup when 1 then 1 end) ,
@Grp2 = sum(case igroup when 2 then 1 end),
@Grp3 = sum(case igroup when 3 then 1 end),
@Grp4 = sum(case igroup when 4 then 1 end)
FROM #SivaTest
SET STATISTICS IO, TIME OFF;
PRINT REPLICATE('-' , 20) ;
PRINT CHAR(13);
PRINT '********* Siva''s WITH & PIVOT Method ********* ' ;
SET STATISTICS IO, TIME ON;
;With ConsolidatedCTE(iGroup,nCount) as
(Select 0,Count(id) FROM #SivaTest
union all
SELECT igroup,COUNT(id) FROM #SivaTest group by igroup)
select
@id = coalesce([0],0) ,
@Grp1 = coalesce([1],0) ,
@Grp2 = coalesce([2],0) ,
@Grp3 = coalesce([3],0) ,
@Grp4 = coalesce([4],0)
from
(Select iGroup,nCount from ConsolidatedCTE) #aTable
Pivot
(Sum (nCount) for iGroup in ([0],[1],[2],[3],[4])
) as PivotTable
SET STATISTICS IO, TIME OFF;
PRINT REPLICATE('-' , 20)
Here are the results (muliple time run;results average the same)
********* HowardW's COUNT Method *********
Table 'Worktable'. Scan count 1, logical reads 2106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 514 ms, elapsed time = 516 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
--------------------
********* davidandrews13's SUM Method *********
Table 'Worktable'. Scan count 1, logical reads 2106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 671 ms, elapsed time = 688 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
--------------------
********* Siva's WITH & PIVOT Method *********
Table 'Worktable'. Scan count 2, logical reads 4212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 626 ms, elapsed time = 517 ms.
--------------------
THough yours seems to be on par with the other solutions, look at the logical reads and Scan Count. On a multi million table, this will matter a looooot!
March 6, 2012 at 1:30 pm
I may be defensive but I am commited to my methods. No one is going to changing that
SQL Databse was based on Matahematical principles. The solutions we are suggesting are based on what is best for perfomance. Stop thinking that it is on a hard disk which is accessed by pages and hence our solution must be this. Look at this problem in the current thread, and the other one. Give a matahematician the problem on paper and tell him I have these data how do I get this. Both would have involved grouping first and Pivoting. It may waste resources working on current hardware technology which might be solved in the long run. It is when we think of a solution for each and every problem you will end up 1000 solutions for a simple task of grouping and pivoting.
I am not doing RBAR, it is fully set based code. neither am I writing RBar look like code. There is not a single loop anywhere. I am using the set based commands from the lanaguage as it is supposed.
Also I do not beilve as someone said that I am scanning the databse twice etc. The first scan would have reduced the number of rows to just a handfull before it Pivots it. The UNION was a problem to get the grand total, may be that could be done in the Pivot itself
Guys I have been involved just 3 times since I joined the form . all 3 times it has sucked my energy out.
March 6, 2012 at 1:34 pm
siva 20997 (3/6/2012)
I may be defensive but I am commited to my methods. No one is going to changing that.
There's your problem. Good luck to you.
Jared
CE - Microsoft
March 6, 2012 at 1:52 pm
If effeincey was the objective the C++ lanaguage would have been kicked in the first place. All object oriented lanaguages added so much to burden for the application. But we still pursued here we are today we use nothing but Object oriented code in every language. If Microsoft thought that this 386 Intel chip is useless for Windows we would have never had the windows.
I want to address the point of scalability. I think of scalability has reaching the masses. Not increasing the size. That you guys have achived it. Microsoft wanted to kill Access in favour of SQLExpress far back as year 2000. It still has not managed it. Only by developing system entireley in SQL then you can shift to millions and then put your own front end on it be it chinese or english write in ASP,PHP, C or VB. Just like changing the skin on a mobile phone. Just like people use paypal functions to post payments use TSQL stored procedure to post invoices etc. No need to undertand how it does it or check the data. because it is packaged and shipped as such.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply