March 27, 2012 at 10:17 am
Hi,
I'm hoping I'm missing something obvious.
We have a 3rd party database to which I am not allowed to make any changes, even putting on indexes on key fields - this is giving me a headache because reporting performance against the database is terrible.
I have a separate database holidng stored procedures which access the data in the source database - I had been hoping to use indexed views but I cannot create a view looking at data in another database. Logshipping and snapshots are read only so I cannot use those to create an index and I think equally mirroring is not what I'm looking for.
Performance of the box is good, the stored procedures I have written are ok (I'm tweaking them now but that's are not going to provide the performance boost I want to achieve) - the bottleneck is simply lack of indexes & I've tested just a couple of key indexes on a copy and they revolutionise performance.
The only solution I can think of at the moment is to copy the data to my reporting database on a frequent interval on the basis of primary keys (which where they exist are unordered GUIDS) or update dates (unindexed) but performance is still going to be an issue and users want data now so an overnight update is not going to work.
So, in short, does anyone know of a way to improve query performance while accessing a third party database without making changes to it - any pointers appreciated.
Thanks
March 27, 2012 at 10:35 am
The top 5 targets I look at when trying to improve performance are:
1. queries
2. queries
3. queries
4. indexes
5. schema
It sounds like you cannot change any of those.
If by chance you're having trouble with bad execution plans you can look into plan guides, but that is usually a corner case, and usefulness is far behind fixing the items stated above.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 10:47 am
Thanks - I am a bit stuck at the moment and your list is pretty much where I look.
I will admit my queries are not going to be perfect, for instance I need to know for a given parent if there are any children, when I wrote the code I counted the number of children so I'm just trying to change that to an "exists" but performance is always going to be impacted by the absence of an index on the "parent id" field.
What's worse, the chances of any ever agreeing to archive any old data are pretty low so performance is only going to get worse.
March 27, 2012 at 10:52 am
Can you use transactional replication to bring data into scope, but into a different schema where you can add indexes? Know that it is possible to replicate data in one database to another database on the same instance, and the subscriber table can have a different set of indexes than the publisher.
This would solve your "want data now" issue, and if you do control the queries (I didn't catch onto that in the initial read) then you can go from there.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 11:01 am
That's exactly the sort of concept I was missing - that gives me something worth looking into, many thanks.
March 27, 2012 at 11:26 am
Anytime, thanks for posting back, happy to assist 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 4:40 am
Ahh the old indexes on OLTP databases issue...
Indexes add overhead to the inserting, modifiying and deleting of records. If you have a high volume OLTP (On-line Transactional Processing) database (e.g. Amazon) then new indexes for reporting can cripple application performance.
Depending on what you are trying to do and the volume of data you may want to look at SSAS and creating an OLAP (on-line Analytical Processing) database. The data logic, structure, storage and querying are completely different but the speed of reporting results is awesome. The simplest way to describle OLAP is like a huge Excel pivot table; it has any aggregations and summaries that you may need already calculated and you simply drill in to it (rows, columns and pages) to find the right data segments. As with all MS products - the implementation is not so straightforwards and it requires a paradigm shift in the way you think about your data and its access.
This is not a quick fix and may require months of planning and proper project management
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply