SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 mm/dd/yyyy format
 Forum Locked
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2007 :  11:00:25  Show Profile  Visit jsmith8858's Homepage
quote:
Originally posted by Jeff Moden

Don't know about how fast a front end could do it... I'm not a GUI guy anymore... gave it up a long time ago. But I don't think you're considering things like I didn't have to pass the million rows to the client to build the report. It would be interesting to see what happens if someone actually did try to do the same thing from the front end.

So far as being completely worthless to the front end, you gotta remember, it's a REPORT, as requested! It's supposed to be worthless to the front end for anything except a simple serial display. And, it wouldn't take much more to put a header, subtotals, grand totals, etc on this. Shoot... run this "pig" through sp_MakeWebTask and you don't need the front end to do a bloody thing.

Anyway, you asked the question and I did it with some pretty good speed. Write some front-end code to do the same thing and let's compare performance notes... Not being a smart guy here... I've really been out of the GUI business for a long time and, between the million row data transfer and the required formatting, it just seems like the GUI would be slower. So I'm curious.

--Jeff Moden



I think we're talking about two different things here ... we are not talking about summarizing at the front end versus the database layer, we are talking about formatting and/or suppressing data.

Of course you summarize data at the database. But in this case, it is not the # of rows that are returned that is different, it is the fact that we are suppressing values in a certain column for certain rows to make things "look good". Either way, the client receives X rows -- doing it at the front versus at the back makes no different there. The difference is the work that SQL Server must do to calculate and return those X rows. If the client formats the data, SQL just returns the rows. Done. If SQL formats the data, it must do a self-join on the data and based on a range of rows (i.e., just less than a cartesian product). Run the two SQL statements side by side in Query Analyzer and see the difference in the execution plan to see how much more work SQL Server needs to do to return those results; this doesn't even mention the work we must do in T-SQL to write the code to produce those results, and the complication that it does to our simple SELECT statement making it much harder to understand and maintain.

Either way we present this, database layer or presentation layer, the client gets X rows. If the client formats the data, it very simply keeps track of the current "ID" and if it doesn't match the previous one, it displays it. Just about no work at all -- a simple compare operation between two strings or ints is performed. If SQL does the work, not only is the data messed up and we are no longer returning an Int but an VARCHAR (in this case) as the primary key, but now all rows do not contain the necessary data for the client to do any further work with that data.

Finally, if a report is being produced, a reporting tool is much, much more equiped to handle the formatting rather than , again, forcing it to be done in T-SQL, making your report less efficient and also completely limiting the things that the reporting tool can do with the data since, again, the data is not a complete and valid resultset with proper datatypeing and without the ability to sort or identify individual rows.

- Jeff

Edited by - jsmith8858 on 01/03/2007 14:43:08
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 01/04/2007 :  00:53:31  Show Profile  Send madhivanan a Yahoo! Message

Jeff Moden,

From my previous reply

"I have done all those numbering in Vb6 and Crystal Reports with simple while loop or making use of Reports special formula (RecordNumber, Running Total fature, etc). I also beleive that it will reduce execution time of server when they are done in front end.

Please note the highlighted part. By saying "it will reduce execution time of server when they are done in front end" I didnt mean that front end will do that fastly, what I meant is exactly what Jeff explained.
If you do Serial No, running Total, suppressing columns, etc in front end, the amount of Time that SQL Server takes to do these will get reduced. When I reply actually I kept that thought in my mind but again I didnt explain to you as Jeff Explained

Now, to show you how easy to do all the things that I specified at #4, I give you code example for both VB6 and Crystal Report(I didnt use other front ends)


(a) Generate Serial No

VB6 

Dim i as integer
i=1
While not Rs.EOF
	print i
	i=i+1
	Rs.MoveNext
Loop

Crystal Report
	Make use of Special field RecordNumber and add that field in the report
	

(b) Format Dates

VB6 

Print Format(Rs("Datecol"),"dd/mm/yyyy")
Print Format(Rs("Datecol"),"mm/dd/yyyy")
Print Format(Rs("Datecol"),"dd-mmm-yyyy")
Print Format(Rs("Datecol"),"YYYYMMDD HH:MM:SS")
Print Format(Rs("Datecol"),"YYYY-MM-DD HH:MM:SS")
Print Format(Rs("Datecol"),"DD MMM")
Print Format(Rs("Datecol"),"MMMM YYYY")
Print Format(Rs("Datecol"),"DD")
Print Format(Rs("Datecol"),"MMM")
Print Format(Rs("Datecol"),"YYYY")

Crystal Report
	Format the field and select the required Date Format from the available list
	


(c) Suppress Duplicated Values

VB6

Dim S as integer

If not Rs.EOF
	Rs.MoveFirst
	Set S=Rs("RowNum")
	print Rs("RowNum")
	Rs.MoveNext
end if

While not Rs.EOF
	If Rs("RowNum")<>S 
		print Rs("RowNum")
	else
		print ""
	end if
		Set S=Rs("RowNum")
		Rs.MoveNext
Loop

Crystal Reports
	Group the Report by the column that you want to suppress. 
	Now it will display only one value for that column and corresponding records below to it
	
	or
	
	Format the field and check the check box labelled Suppress if duplicated
	

(d) Running Toal

VB6

Dim amount as double
amount=0
While not Rs.EOF
	amount=amount+IFF(ISNULL(Rs("Amount"))=True,0,Rs("Amount"))
	print amount
	Rs.MoveNext
Loop

Crystal Report
	Make use of its Running Total feature by selecting the column that you want and add that field in the report
	

(e) Generate Comma Seperated Values

VB6

Dim S as String
While not Rs.EOF
	S=S+","+IFF(ISNULL(Rs("Column"))=True,"",Rs("Column"))
	Rs.MoveNext
Loop
print Right(S,len(S)-1)

Crystal Report

	Create a Formula field having the following code
	
	StringVar S;
	
	WhilePrintingRecords
	If {Column} is not NULL
		S:=S+","+{Column}
	endif
	S:=Right(S,length(S)-1)
	
	Now use this formula field in the report




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/04/2007 :  07:44:22  Show Profile
Perfect, Madhivinan... thank you so much. This explains a lot that no one has taken the time to do before. You should post this in the script section under the title of "Why you shouldn't format SQL".

Just one final question and I'm afraid it borders on being just plain stupid about apps, but I really don't know the answer to the question... let's take the following VB6 running total example you posted...

Dim amount as double
amount=0
While not Rs.EOF
	amount=amount+IFF(ISNULL(Rs("Amount"))=True,0,Rs("Amount"))
	print amount
	Rs.MoveNext
Loop


I understand that the code is reading from a "result Set"... Is that "result set" data that's already been downloaded to the client when the result set was instantiated or is it making a separate trip to the server on each "MoveNext"?

--Jeff Moden
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/04/2007 :  08:24:03  Show Profile  Visit jsmith8858's Homepage
jeff -- it depends on how you open the recordset, and what kind of locking you use. You can open a server-side cursor, or a client-side cursor, or a snapshot. A server-side cursor would be the least efficient, a snapshot (if I recall correctly) will download all of the data to the client.

In .NET, often the data is pulled in completely into a DataTable or DataSet, the connection is terminated, and then the web page's controls are bound to the DataTable and formatting/totalling are done while disconnected completely from the server.

As Madhivinian demonstrates, the code for display a row number for each row, or displaying the total row count at the end on your web page, might look like this:


Dim i as integer
i=1
While not Rs.EOF
	print i
	i=i+1
	Rs.MoveNext
Loop


The important thing to remember is: even if you don't accumulate that row number variable, you still need to loop through the data, so the difference if you do the row numbering one the server (at a significant cost of course and complicating your T-SQL) results in only removing the code that increments an integer variable. The savings of not incrementing that integer are probably too small to measure even for a huge resultset.




- Jeff
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 01/04/2007 :  10:01:08  Show Profile  Send madhivanan a Yahoo! Message

Jeff Moden, it is nature of the RecordSet you create. If you set the cursor Location of the Recordset to Client, then all data are available to the client and when you access records using While or For loop, no round trip to the server is made

The declaration of the Recordset is

Set Rs.CursorLocation=AdUseClient

Now Rs.RecordCount will give you count of all rows and you dont need seperate Select count(*) statement

If you want to work with the loaded data, you can disconnect from Server

Set Rs.ActiveConnection=Nothing

If you have enough time, read these
http://www.devx.com/getHelpOn/10MinuteSolution/20407
http://trixar.com/~makai/adodisc.htm

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/04/2007 :  10:42:45  Show Profile  Visit jsmith8858's Homepage
by the way, madhivanan, nice work with all those examples!

- Jeff
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/04/2007 :  19:25:10  Show Profile
And great links, too!

Thank you both for the education on all this... even an old dog like me can learn new tricks with this kind of help!

I understand well enough now how all this works... like I said, the basics really didn't change... I just needed a major refresher. Thanks again.

--Jeff Moden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 01/05/2007 :  07:49:05  Show Profile  Send madhivanan a Yahoo! Message
quote:
Originally posted by jsmith8858

by the way, madhivanan, nice work with all those examples!

- Jeff


Thanks Jeff Smith

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 01/05/2007 :  07:56:06  Show Profile  Send madhivanan a Yahoo! Message
quote:
Originally posted by Jeff Moden

And great links, too!

Thank you both for the education on all this... even an old dog like me can learn new tricks with this kind of help!

I understand well enough now how all this works... like I said, the basics really didn't change... I just needed a major refresher. Thanks again.

--Jeff Moden


Thanks Jeff Moden

I am happy that I also helped you in understanding the things

Also, please note that I didnt underestimate you. I simply thought you were not aware of some features or Built-in functions available at front ends that could do those things easily than expected

I hope you will contribute more posts at SQLTeam

Thanks again

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/05/2007 :  19:23:04  Show Profile
I appreciate that and you were absolutely correct... I under-estimated the power in the front end. Again, I thank you much for the time you've spent helping me to see that particular end of the tunnel.

--Jeff Moden
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000