January 25, 2011 at 9:35 am
I am looking for reference materials and/or strategies for improved reporting performance that apply to SQL2005 but are not SSRS-centric.
Please let me know if I am posting in the wrong place.
Thanks!
January 25, 2011 at 10:07 am
Somewhat of a nebulous question.... Are you using a tool *other* than SSRS now? If so, what tool and what issues are you seeing?
Steve.
January 25, 2011 at 10:13 am
Well, that sounds like performance tuning your report queries. The simplest means are only return the fields and rows you need to generate the report and tune the query as much as possible to use indexes. And if it makes sense overall, add indexes, however I rarely will agree to add an index just to make 1 report faster, rarely but it depends.
CEWII
January 25, 2011 at 10:24 am
I concede that the request is somewhat nebulous, but I am looking for rather general replies as well.
I am hoping that you good people will throw a lot of stuff at my wall (books, articles, forum threads, etc.) and I will wade through it and find what I need.
some details:
1.) We are using a 3rd party tool.
2.) Issues are timeouts - some on reports and application searches and some on other application processes.
3.) This is beyond just adding indexes(indices); I believe I need to create a separate reporting database with some denormalization and an index strategy that is optimized for reports (which should, conversely, allow me to optimize the index strategy of the transactional dbs for transactions).
Thanks again.
January 25, 2011 at 10:42 am
duncfair (1/25/2011)
---SNIP---1.) We are using a 3rd party tool.
2.) This is beyond just adding indexes(indices); I think I need to create a separate reporting database with some denormalization and an index strategy that is optimized for reports (which should, conversely, allow me to optimize the index strategy of the transactional dbs for transactions).
Well that changes things a bit.. Depending on the version of SQL (Std/Ent) you have some different options.. In Enterprise (and with some additional work Std) you could generate an indexed view that denormalizes data and "materializes" it. What this means is that the view is stored similarly to a table and is updated in real-time as the underlying data changes. It does limit the underlying table modifications. You can also copy (via SSIS, replication, or some other method) data either to another database or server and denormalize/pre-aggregate it there. A large portion of the discussion would be tied to latency. if some marginal latency is allowed then transactional replication could be done. This could use custom sprocs update the required tables or just copy the data which is then manipulated from the copies. So how much latency is generally allowed?
CEWII
January 25, 2011 at 10:51 am
First off, thanks for the responses.
I see, however, that further details are needed.
1.) While some customers use Enterprise or Standard versions of our product, we also allow and support using SQL Server Express version of SQL2005 (we will be transitioning to 2008).
a. this means no SSIS, SSRS, or any of the other goodies available only on paid versions of SQL server.
January 25, 2011 at 11:03 am
Then you are relatively up a creek unless you bind in an MS Access front end for reporting, Crystal Reports (I think you can pay them some royalties to include some stuff from them in deployable products, but don't quote me directly on that, ask 'em), or another reporting software.
There are no good, free, reporting tools out there that I'm aware of that seamlessly, or even reasonably, connect to a SQL Server. You might try something with MS Excel, but that's gnaw off your own arm painful sounding even in the best of situations.
No SSIS, no replication, none of the mirroring tricks to create a reporting database, either? Yeah, I'd drop either supporting this app on Express at all, or don't try to support reporting on Express.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2011 at 11:07 am
Supporting Express versions of SQL and the 3rd party reporting tool (we are using Logi) are givens that I must work with.
January 25, 2011 at 11:17 am
Steve,
SSRS isn't an option because the decision to use a 3rd party reporting tool isn't negotiable at this time.
Thanks
January 25, 2011 at 11:23 am
duncfair (1/25/2011)
Steve,SSRS isn't an option because the decision to use a 3rd party reporting tool isn't negotiable at this time.
Thanks
Welp, I'm afraid not knowing much, if anything, about Logi, we're going to be hard pressed to help you on that. We can assist you in getting everything up to Logi running as seamlessly as possible, though. If you're willing to post DDL, sample data, and the query logic you're using to feed Logi (that sounds like some dark god somewhere... Feed Logi, or he'll eat you in the dark) we can help you there.
But you're strapped in using a low end version of SQL Server, which is specifically built to NOT support high availability, heavy usage requirements.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2011 at 11:54 am
Well those are some pretty messy requirements.. Ok... Keep in mind supporting express also limits database size so we will have to keep that in mind too.. you also lose SQL Agent, but CAN use Windows Scheduler, not as nice but still workable..
Basically all the good ways to copy data are out.. Ok, so what we have left are triggers or trunc/load using BCP(or bcp like tech). With triggers we can either write the data to the other DB directly or use something like service broker to queue it up. We still have indexed views available but to use them:
http://msdn.microsoft.com/en-us/library/ms187864(v=SQL.100).aspx
"Indexed views can be created in any edition of SQL Server 2008. In SQL Server 2008 Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used."
You will probably have to use a couple technologies to get good performance AND latency is all but guaranteed.
CEWII
January 25, 2011 at 12:42 pm
Elliott Whitlow (1/25/2011)
With triggers we can either write the data to the other DB directly or use something like service broker to queue it up.
Side comment: Service Broker's busted in Express as well.
From http://msdn.microsoft.com/en-us/library/ms345154(v=sql.90).aspx#sseover_topic10
SQL Server Express can use Service Broker only in combination with other SQL Server 2005 editions. If SQL Server Express receives a Broker message from another Express instance, and if another SQL Server 2005 edition has not processed the message, then the message is dropped. So the message can originate from an Express instance and end up at one, but it must be routed through a non-Express instance if that is the case.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2011 at 1:12 pm
Sorry, missed that bit 🙂 (actually, i missed all comments o the second page!, my comments below basically echo those from others)
Is there any option to offer a potentially lesser functionality reporting option with the lesser functionality DB option chosen by the client? Not sure if this is the case here, but I've seen different product offerings that have run on free s/w where the client has then run 'large data' (read this as a large enough data size as makes you uncomfortable without partitioning etc) through the app, and expected 'Enterprise' performance. The reason why Msft sells Std and Ent and gives away Express is that these editions support features that can provide performance improvements (typically for larger data or more complex data scenarios).
Steve.
January 25, 2011 at 2:54 pm
Good catch Craig..
So basically it is ALL manual using Windows Scheduler and something like BCP to copy the data. That is painful..
CEWII
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply