|
Yes, split the records in more than one table is a part of normalization.
I am sure if you keeps records in a single table then defintely it will consist redundant (duplicate) information. So that is why we split our records across more that one table on the basis of any keys. This is the part of normalization.
Parwej Ahamad
R & D with IIS 5.0/6.0
modified on Thursday, June 5, 2008 1:36 AM
|
|
|
|
|
interesting, I assumed he had a normalised DB and wanted to split a very large table to improve performance.
If that is the case then I suggest you use filegroups (sql server) and have each file group on a different physical drive. The next step after that is to use partitioning to segregate your data.
Performace tuning and maintenance of a good data structure is an ART and DBAs study for years to achieve a high level of skill, you will need to put in some serious study time to get a feel for this area.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Depends on many factors, such as the amount of data, how/what you'll split up, the set up of the database, how you measure performance etc.
Generally speaking you can reduce IO if you split sensibly, take a look at the PARTITIONING of tables in SQL Server 2005 (if you use that, and other vendors might have similar functionality),which automates this a bit and which seriously can increase read performance, but can hurt write performance a little bit. Some simple tests I've done for myself showed in situations that I could get ~66% faster read simply by partitioning.
Also as suggested by a previous poster, if your tables aren't normalized, there could be a gain from simply doing that to avoid redundant data, and thus decrease the number of operations with read/write.
|
|
|
|
|
It depends. Are you talking about splitting the rows (for example having 1 table for 2008 data, 1 for 2007 etc) or splitting the columns (for example taking the address out of the customer table into an address table).
If its the former you will probably only see gains if you have very large tables, but properly partitioned there can be significant benefits where large volumes of data are involved.
If it the latter you are talking normalisation. Before leaping into too much normalisation take a look at your data useage. Normaalised data will result in joins when querying and this may have a detrimental effect on performance. For performance it is sometimes better to use denormalised data.
Its all down to volumes and use really.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I'm trying to connect a remote database (SQL Server 2000) thru internet.
I'm working on VB.NET. Also I do have a Static IP.
I'm using the following connection string.
"Data Source=<<IP ADDRESS>,1433;Initial Catalog=<db name="">;User ID=<<User ID>>;Password=<<PASSWORD>>"
But it did not work.
Can you help me with the correct connection string & other details required to access remote DB .
Thanks,
Hariram.
|
|
|
|
|
It might be some security issues or user right issues.
Can you post here exception message which you are currently getting so that we can do more analysis with issues ?
Parwej Ahamad
R & D with IIS 5.0/6.0
|
|
|
|
|
I have not seen this attempted for many years, not since there were hackers and viruses. If somone gets hold of your connection string they have access to your database. If you are not managing your security VERY tightly you have a serious problem.
They invented web services to address this problem (among others) your client should not connect to the database directly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes,
Can you sugest me the other helpfull ways to accomplish this.
Also i don've idea about using webservice for data access.
Can you help me in any ways.
--Hariram
|
|
|
|
|
|
You could try the following:
Data Source=[server name or IP];Initial Catalog=[database name];Persist Security Info=True;User ID=[User];Password=[@@#$%@#@#@*****]
|
|
|
|
|
Can anyone tell me where the default value for the logfile path is stored? I presume it is in one of the system tables somewhere...
The problem is that at the moment, using the "Create New Database" option generates a path of:
...C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\YourDBname for the DB and
...C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DAT\YourDBname for the logfiles
Obviously I can change this at creation time, but I'd rather that my colleagues and I don't have to do this every time (sigh).
Google (so far) has not been my friend.
|
|
|
|
|
I have never tried this myself, but what happens if you change it for the "model" database?
|
|
|
|
|
I see where you are coming from - but model database files location are both correct (i.e. both \DATA)
Thanks for your attention. 
|
|
|
|
|
In Management Studio, right-click the server in the Object Explorer tree, select Properties, go to Database Settings, and change the paths under Database Default Locations.
IIRC, you need to restart the SQL Server service before the change takes effect.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Woohoo! Yup - the default locations were blank (dunno where it was getting them from before  )
Changed and successful - did not even have to restart SQL server agent or anything.
Cheers Mike! ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
Hello!
My hosting company gave me a backup of my online DB (a BAK file), which I've attached to my local SQL Server, in order to perform the tuning locally at first, and then on the online DB.
I use a trace file, which was created by the Profiler, that ran on the online DB.
I get no suggestions at all, but errors. The most frequent errors are:
1. Event does not reference any tables
2. [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure '(some stored procedure name)'.
Thanks in advance!
|
|
|
|
|
Hello everyone ,
I am trying to back up a database using VB.net . Is there any coding for that ? I need to backup a database connected to !!! Not a database that is not connected to !! Because when I open my program it will automatically connect to the database .
J ,
|
|
|
|
|
An Access Database is just a file... you could do a file copy to a backup location. I'm not sure that the mdb file being opened will affect this process. If it does, you could always temporarily close the connection and restore?
|
|
|
|
|
Hy ,
Thx for ur answer . But whats the code like ? Check if the file exist and the copy it to another directory on a button click ? How the code look like ?!
Thx again.
J ,
|
|
|
|
|
John Kh wrote: Check if the file exist and the copy it to another directory on a button click
You could do something like that... that would be the easiest. Only thing is, you will need to make sure that the backup copy works. So restore the database back to original location and check that it actually has been copied over correctly.
|
|
|
|
|
Can any one tell me if it is possible to get the page total in the body section of a report....not the footer...thanks in advance
|
|
|
|
|
I think you'll find that the page total is only produced AFTER the page is rendered therefore it is not available inside the actual page. Having said that I beleive you can assign it a variable and access the var from withing the page, this will reflect the current page - 1. Kludge but we have used it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The problem for me is not calculating the pg total. The main problem is that if the table is spanning multiple pages then how do i get a report item to appear on each page below the table so that i can display the total without using the page footer. i cant use the table footer and cause it to repeat on every page because the table footer is processed only once at the end of the table
|
|
|
|
|
Hi, I have develop database application in vb 2005 and using sql express as database back end. I have declare a connection as below
Dim cnnDICAdmin As New SqlConnection("Data Source=.\ SQLEXPRESS;Database=DICDbase;Integrated Security=True;User=Admin;Pwd=") but when my application but when i run amy application its display an error as below:
System.Data.SqlClient.SqlException was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message="Cannot open database "DICDbase" requested by the login. The login failed.
Login failed for user 'CAMBODIAGFATM\roathkanel'."
Number=4060
Procedure=""
Server=".\ SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at DIC_Administration.frmProvince.btnAdd_Click(Object sender, EventArgs e) in C:\Documents and Settings\roathkanel\My Documents\Database\DIC Administration\DIC Administration\Province\frmProvince.vb:line 38
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at DIC_Administration.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Please let me know how to solve this problem. Thank in advance.
|
|
|
|
|
Check out this:
^
It shows conenction strings for most databases.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
