June 5, 2015 at 11:21 am
I have a database in sql server which contains around 12 tables and it's not normalized. i.e. no primary key , index, foreign keys. This database is being used for excel reporting. Queries are written data from these tables and present it in Report format in excel. queries written behind these excel sheets have many left, right, inner joins. some of these tables contain millions of data. reports take too much time to load once refreshed. I am planning to normalized the database first including primary key, indexes (will it make performance enhancement???) or will it make any -ve impact on my database? What could be the best way to make these reports work faster.
Any suggestion will be helpful. Thank you!!
June 5, 2015 at 1:00 pm
A solution specific to your situation is impossible without more detailed information about ALL the tables, which may or may not be practical to provide. Additionally, it might be a lot more effort than the folks available on the site have time to work on. However, some general advice. Indexes do have a cost, and it occurs at INSERT time, so you need to know how these tables are populated before you decide how to index them. For example, if they're exclusively used for reporting, and perhaps are re-loaded on a nightly basis, you might well choose to TRUNCATE the table and drop the indexes before loading the data, then create the indexes fresh after each load. On the other hand, if these tables are high-volume OLTP tables, you might not be able to do much with indexes, and may want to consider replication. There is a wide range of possibilities that may contain elements from both extremes, so keep your eyes open and look for practical alternatives. When you have a more specific scenario to work with, you're likely to get more specific help.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 5, 2015 at 1:05 pm
Just to throw this out there but normalization is a methodology of data structures are designed, constraints like primary keys or foreigns keys are means to enforce that method of data storage and indexs are there to improve performance at query time.
If this is just for reporting purposes those tables might very intentionally be denormalized, which wouldn't prevent you from creating primary and foreign keys and indexs would almost certainly improve performance while running reports.
June 9, 2015 at 9:43 am
Thank you for your response
These tables (around 12/15) are used for reporting purpose. there are 3 jobs which fill these tables from another server and then there are queries written behind excel sheet for various calculation to show report in excel. There are many outer joins written within the queries
In each job, every table is being dropped and then refilled with the data.
If i drop the indexes too along with dropping the table will it tune my database and queries.
New in tuning database, any direction will be helpful.
Thanks:-)
June 9, 2015 at 9:45 am
These tables are just for reporting. jobs fetches data and reload these tables every time job is run. then queries are written to outer join different tables to fetch reports in excel. Each report takes around 7 mins to generate, i am not sure if this much time is normal or is there any better method to showcase report that is faster.
June 9, 2015 at 11:01 am
madan.preeti2005 (6/9/2015)
Thank you for your responseThese tables (around 12/15) are used for reporting purpose. there are 3 jobs which fill these tables from another server and then there are queries written behind excel sheet for various calculation to show report in excel. There are many outer joins written within the queries
In each job, every table is being dropped and then refilled with the data.
If i drop the indexes too along with dropping the table will it tune my database and queries.
If you drop the tables, I don't think the indexes stick around, do they? What the heck would they index, if the table is no longer there???
On the other hand, if you're creating the tables each time, you could add in some code to create the indexes also, either before or after creating the tables.
June 9, 2015 at 11:09 am
Oh yes i forgot to mention that, once data is populated, indexes will be recreated. Do you think that will tune query to some extent?
June 9, 2015 at 11:16 am
madan.preeti2005 (6/9/2015)
Oh yes i forgot to mention that, once data is populated, indexes will be recreated. Do you think that will tune query to some extent?
SQL Server will definitely change its execution plan if there are indexes and statistics that can indicate to the server that using an index will be better than not using an index. LOL pretty vague eh? Thats because adding indexes is not a guarantee, but I know its worked for me in the past in some situations.
June 9, 2015 at 4:32 pm
madan.preeti2005 (6/9/2015)
Oh yes i forgot to mention that, once data is populated, indexes will be recreated. Do you think that will tune query to some extent?
Do you know how to read an actual execution plan at the property level?
--Jeff Moden
June 9, 2015 at 5:38 pm
ZZartin (6/5/2015)
If this is just for reporting purposes those tables might very intentionally be denormalized, which wouldn't prevent you from creating primary and foreign keys and indexs would almost certainly improve performance while running reports.
+1
Don Simpson
June 9, 2015 at 8:25 pm
madan.preeti2005 (6/9/2015)
Oh yes i forgot to mention that, once data is populated, indexes will be recreated. Do you think that will tune query to some extent?
Jeff Moden asks a good question. The indexes are likely there to help with reporting, but without any detail about whether performance is good or bad, or a particular situation / problem to look at, there's nothing for us to go on. To answer one question that cropped up, when you drop a table, the indexes on it disappear with it.
Your indexes "tune" a query to some extent by getting used by the query, and you can reveal what's taking place via the execution plan. The ideal index is a "covering index(es)", which means that the query can get all of it's information from the index(es). However, a poorly written query might be difficult to get a covering index for, so there's no simple answer. As almost always is the case, the answer is usually "it depends".
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 10, 2015 at 7:09 am
No, any start up article will be helpful.
June 10, 2015 at 8:07 am
madan.preeti2005 (6/10/2015)
No, any start up article will be helpful.
The book at the following link is probably the worlds best on the subject and it's written in such a fashion the beginners and experts alike can easily get what they want out of it. It's a "MUST HAVE".
http://qa.sqlservercentral.com/articles/books/94937/
--Jeff Moden
June 11, 2015 at 12:59 am
I was doing some tuning on a database a couple of days ago and added indexes as there were none. Came back the next day and they'd vanished!
The developers' ssis job to load the tables was dropping and recreating all the tables nightly rather than truncating it. Grrr!
Also rather than actually DROPping the non-clustered indexes before a reload, I just DISABLE them as it keeps the index structure, then after the dataload I REBUILD those indexes.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply