SQL Server Performance Forum – Threads Archive

lookup tables in other DB

Hi All, Our developers are generating reports from our datamart database. They are accessing look up tables that are in staging database ( i mean joining tables in that db.. like db1..lookuptable1).No space problem. They can store the lookup tables in datamart itself. It is good practice? Is it impact the performance?
Please share your comments. Thanks,
apak

Are there alot of records in the lookup tables? SQL server will use row level locking by default for the lookup tables. However, if the lookup tables will not be amended by the users, it would be better to use table lock instead of having many individual row level locking.
This is because SQL server got to work harder maintaining row level locking as compared to table locking. Below stored procedure execution will turn off the row level locking if you think you would like to switch to table locking instead. SP_INDEXOPTION ‘yourtblname’, ‘AllowRowLocks’, FALSE

yes, There are lots of records in the tables. Ex .. 47418800 of records in one the tables. My question is, is it better to have those reference tables in datamart db itself or accessing them from staging db is okay? Any way they are using WITH NOLOCK while joining… Thanks,
apak

If both of your databases are in the same server, performance will not be affected.

thanks a lot.
Yes, both the dbs are in the same server.

and they are using the same RAID/DISK channels? That means that the staging db/operational db/presentation db (datamart) are actually on the same physical network unit. Usually in a grouping concept, you place the final reference data in the presentation database. But 4M records is a lot of lookup data. Its probably on a raw level that still needs to be process so it is in the staging database. May the Almighty God bless us all!
www.empoweredinformation.com

Also check what is the Transaction log contention while performing these actions. Whatever operations processed on the SQL Server depends upon the available resources such as Memory and Disk I/O.
Are there any cursor operations involved in these queries?
http://www.sql-server-performance.com/olap_performance.asp fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

]]>