Performance

[groleau]

On a large data set, is there any performance advantage to having a view on the server apply some filtering isntead of have the report do it?

For example, table S contains two years of services. I put in the report

SELECT * FROM S
WHERE GETDATE() - Date_Of_Service < 30

Tweak a column width, and click preview and wait two or three minutes to see the effect.

I'm patient--I only have to get it right once. But the users will be looking at it weekly, and they are not geeks, so it must be very friendly.

--
Wes Groleau

http://www.Parkview.com/

 

[xlbo]

All the texts I have read suggest that aggregtion / filter performance is better done on the server rather than in the report

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SQLBI]

Firstly, don't ever, EVER use

SELECT *...

This is an extremely inefficient method and will not make use of indexes to speed up your query.

Secondly, use Store Procedures whenever possible as they will use a pre-stored execution plan which will also increase performance. Look at books online for info on Stored Procedures.

Thirdly, create indexes on your tables for your SP's to use. As a general rule, for tables involved in joins create non clustered indexes on you foreign key columns. Look in books online for info on Indexes.

As Geoff says, do all the work you can on the database server, that's what its designed to do... Reporting Services in a rendering/distribution layer.

Let us know how you get on.

Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[SQLBI]

In addition to indexcing your foreign key columns, you should also try to index the columns used in your WHERE clause if they're not already.

Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[groleau]

I was not aware that SELECT * was inefficient. Thanks for mentioning it.

Downside of listing ALL fields explicitly is that when you have to change the table (Oh, Mr. Programmer, we forgot to include the interest rate on our input file")really want ALL fields, you have to chase down and edit every blinkin' query, sproc, view, .Net app, ASP page, etc. ad tedium. (Same complaint applies to a 'throws' clause in a low-level Java utility.)

No matter though--SQL Server 2005 doesn't give me the choice: Saving SQL expands all wildcrads, adds unnecessary parent qulaifiers to non-ambiguous names, unnecessary parentheses to otherwise clear and readable expressions, and removes enough white-space, linebreaks, and indentation to ensure commercially safe obfuscation of your code. But I'm not bitter.

Anyway, back to the subject: indexes. I think I am going to do this, although, if the report is going to be merely printed and never again viewed by the addressee, isn't the efficiency canceled by the additional load input the data?

Oh, never mind. The recipient is only effected by the retrieval speed. (And the DBA is not affected by the input speed, since input and report mailing will be automated.)

--
Wes Groleau

http://www.Parkview.com/

 

[groleau]

Wow, I sure did some sloppy typing there.

I created a view in the database that returned exactly
what the report needed. I am still nevertheless required to type all of the query other than the WHERE clause into the report.

And rendering is just as slow as ever.

Can't blame it on the query itself--viewing the query results on the same machine in the same in the same instance of Visual Studio is nearly instantaneous.

No biggie--once the report is right, the server will mail it out when I'm not looking.

--
Wes Groleau

http://www.Parkview.com/