Re: How To Misuse SQLs From Clause

I have just been reading How To Misuse SQL's From Clause (http://www.onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html) (thanks to Rudy who pointed it it out on his website r937.com).

It makes me go "hmm". It says "...this is a very bad query; it's logically flawed" - well no, actually I don't agree with that. Logically, the query is just perfect. However, it is true that a DBMS with a less than perfect optimizer might not recognize that the query can be transformed into one with a correlated subquery, which may be faster to process.

But, dammit, the whole point of SQL is that we define what we want, not how the DBMS should go about getting it. That's what the optimizer is for! Why should we have to waste our time re-phrasing our queries when the optimizer should be quite capable of doing that itself (by following the algorithm given in the article).

I don't think I'm beaing unreasonable here! ;)hands up all of you who have never tried to influence the way the optimizer handles a query by rephrasing it

:rolleyes:|||"From Clause"?? Influence the Optimizer?? I would just like the user to be able to tell me what he what results he really wants from his query.|||I would just like the user to be able to tell meheh, good one

yes, that's often the biggest hurdle, isn't it

:)|||hands up all of you who have never tried to influence the way the optimizer handles a query by rephrasing it
Not me: in my early Oracle-using days before the cost-based optimizer was introduced, we used to have hours of fun modifying queries by re-arranging the order of the tables in the FROM clause, and the order of predicates in the WHERE clause, because the old rule-based optimizer processed the tables and predicates in the order it read them (right to left).

Thankfully, those days are long gone, and so in most cases has the need to restructure queries in other ways (like the one the article recommends). If two queries are semantically equivalent, then it shouldn't matter which one you write, the optimizer should try both and use the fastest. And it should be much better at it than a human!

RDLC vs RDL Revisited

I have searched the archive of this forum, and based on some previous answers have read the followin FAQ

http://www.gotreportviewer.com/

However, even so, I still have a questoin about when to use RDLC vs RDL from the point of view of licensing and load sharing betwee DB and report server.

1. As we all know that SSRS will take a license of SQL Server if the web service is deployed on any other machine other than SQL Server (which is normally the case because no serious DBA will allow IIS apps to be installed on the DB Server). Also, if the SSRS is deployed on NLB cluster then each node of the cluster would need a "SQL Enterprise" license. (which can be very expensive).

2. Co deploying the SSRS with SQL Server is a bad idea because it puts excessive load on the DB+Report Server.

3. Since there is no "load balancing" in SQL all the rendering has to be done by the SSRS service which is running on the DB Server.

Now compare this to RDLC

1. RDLC gives me the same engine as RDL

2. However it has no licensing tags to it.

3. I can deploy ASP.NET report viewer on my web farm and scale it out as much as I like.

4. Extract data from the DB using stored procedures.

So it seems that RDLC solution is cheaper, gives better scale out capabilities and also moves the rendering of reports on the web farm rather than the Report Server.

Why should me or anyone else choose RDL at all?

There seems to be one disadvantage that the data has to fetched programmatically and manually binded (like an ASP.NET app) as RDLC does not provide any feature to connect to data sources. But this is not much when you see the cost and scalability benefits.

regards,

Abhishek.

bump!|||You have some great reasons for using RDLC.

A few off the top-of-my-head advantages for using RDL rather than RDLC.

1. Subscriptions
2. Caching / Snapshots
3. Role Based security
4. "My Reports"
5. Configuration / Definitions stored in Report Catalog DB
6. Additional rendering formats
7. Sharepoint webparts integration

Without a doubt you can implement similar functionality in your application hosting the RDLC, but these are out of the box when you're using the report server.

I have had several clients start out heading down the road of wanting to use RDLC to avoid the licensing fees. In the end, they all ended up using the report server. There's no one size fits all solution and in many cases, RDLC would be a valuable choice.

Hope this helps.|||

Thanks, I also found information on the MSDN site.

http://msdn2.microsoft.com/en-us/library/ms345248.aspx

regards,

Abhishek.

RDLC printing/scaling issues?

I am trying to print a .RDLC file programmatically as per the article here: http://msdn2.microsoft.com/en-us/library/ms252091.aspx. The only difference is that I am actually printing to a real printer (HP 1320n).

I am doing this in the context of an ASP.NET web service.

The issues that I am having are that when I execute the web service on my development workstation running IIS, the .RDLC prints to the network laser beautifully; but when I try to run the exact same code on the server (Win2003 SP1), the report prints, but is scaled up by about 125% so that it doesn't fit on the printed page.

This is obviously very frustrating. I have the same driver version installed on both machines, the drivers are configured identically, and the .RDLC file is identical.

Any clues?

Thanks,
Matthew

Are you running RS2005 SP1? I believe some scaling issues were fixed in it.