May 22, 2009 at 7:29 am
Hi all,
I have a database (very) basic question of which, unfortunately, am not able to identify a suitable response. I have an application that is pulling huge amount of information from various infrastructures, the information pull is triggered on demand (with a button click) every time it’s needed, dropping the previously extracted information. I pull the data mostly into a temporary table or variable, with no relationships between them, and without primary keys.
Everything works fine for me as expected but, I keep asking myself if I should somehow relate the tables.
Baring in mind this information is always dropped and recreated (to fetch a more up to date inf.)
My question is: Is it good practice, to represent data entities in a database, without any Identification columns and no relationships between them, even when they have related information? Many thanks in advance.
May 22, 2009 at 7:42 am
So there r no indexes too on these tables?
Do u feel slowness while retrieving records?
How often r those tables Queried?
How many tables r there with this kind of situation?
May 22, 2009 at 7:53 am
HI Mayank,
I never noticed any sign of slowdown or something, as far as performance is concerned, I really have nothing to complain about.
No Indexes, I use this in creating a weekly report, so it's kind of used everyweek. thks.
May 22, 2009 at 7:57 am
Sorry, I missed one of your questions. they 're exactly 11 tables..
May 22, 2009 at 8:28 am
Personally, I wouldn't setup any relationships between the data (FK constraints) as this could affect the data load and it's simply a reporting database. Besides, those types of relationships are on your production databases, correct? Indexes, however, could improve the performance when processing the reports. Maybe an accurate non-clustered index or two on some of the larger tables. How much data is being loaded?
-- You can't be late until you show up.
May 22, 2009 at 9:10 am
Hi Terry,
I thought as much! We are talking about, a hundred thousand rows of information, maybe I should just work on the indexes as you suggested. Any further advice will be welcomed. Thanks.
May 22, 2009 at 9:26 am
Again, simply look at adding a non-clustered index or two on your larger tables that would help boost the performance of your selects within the report. If you have table a that joins to table b on col1, perhaps on index on col1 on the b table at a minimum. If there is also a where clause on table a, index that column on table a. Keep in mind, if the tables are small (and really, 100K rows is not large), an index may not help as it may be faster to do a table scan (SQL will handle that). Look at your queries being used by the reports. Get an execution plan and tweak where necessary. Keep in mind, there will be some overhead on the data load and, if you do add indexes, you'd probably want to reindex after the data load for better performance.
-- You can't be late until you show up.
May 22, 2009 at 9:35 am
Great feedback! Really appreciate Terry....
May 22, 2009 at 10:46 am
hi,
If everything works fine without indexes your work is easy enough, but later if the huge data is inserted or updated, your db performance will be slower if no indexes defined, instead of moving without any index on the tables, try to create non-clustered index and work as usual, it increases your performance, and also easier to rebuild or reorganize the index when huge data inserting or populating.
Goodluck
KingManjunath
May 22, 2009 at 11:26 am
Just to deviate a bit from what everyone else is saying, if I were going to put any indexes on the system at all, because of how SQL Server stores data and how the optimizer is built around access to the data, I'd put clustered indexes on the tables, not non-clustered. The exception to that would be if you can make the non-clustered indexes covering, meaning they satisfy every part of the query, the select list, the join critieria and the where clauses.
Other than that, as everyone else has said, for a reporting only system, FK's aren't really necessary, but the data load had best be pretty close to perfect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply