"Kjell Arne Johansen" wrote:

> Hi
>
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new layout.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server running
> a stored procedure located in one of the databases?
>
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
>
> Most of the tables are the same as before but there is one table that have
> been normalized.  Text fields have been moved to their own tables and
> referenced from the main table.
>
> Thank You for your help.
>
> Regards
> Kjell Arne Johansen
>
>
>

"Kjell Arne Johansen" <kjell***@online.no> wrote in message
news:SXihe.10020$SL4.226519@news4.e.nsc.no...
> Hi
>
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new
> layout.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server
> running a stored procedure located in one of the databases?
>
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
>
> Most of the tables are the same as before but there is one table that have
> been normalized.  Text fields have been moved to their own tables and
> referenced from the main table.
>
> Thank You for your help.
>
> Regards
> Kjell Arne Johansen
>
>

"DJP" <d**@snotmail.com> skrev i melding
news:pNkhe.2481$E7.1770@news-server.bigpond.net.au...
> Hello Kjell
>
> To copy the data, probably the best thing to do is use Data Transformation
> Services from within Enterprise Manager. Have you also considered adding
> indexes to the old database instead of creating a new one. Also, is your
> database setup to automatically update statistics? If the database has
> been in use for a while, the statistics that the server uses for your
> database may be out of date which may be causing the slow queries.
>
> HTH
>
> D.
>
> "Kjell Arne Johansen" <kjell***@online.no> wrote in message
> news:SXihe.10020$SL4.226519@news4.e.nsc.no...
>> Hi
>>
>> The old database layout is very slow on queries so I have created a new
>> layout and want to copy the data from the old database into the new
>> layout.
>> How can this best be done?
>> Do I need an application do this or can it be done by the SQL Server
>> running a stored procedure located in one of the databases?
>>
>> Can you give me an example of a script copying data from one table in one
>> database to a table in another database?
>>
>> Most of the tables are the same as before but there is one table that
>> have been normalized.  Text fields have been moved to their own tables
>> and referenced from the main table.
>>
>> Thank You for your help.
>>
>> Regards
>> Kjell Arne Johansen
>>
>>
>
>

"Kjell Arne Johansen" <kjell***@online.no> wrote in message
news:lllhe.9690$ai7.234663@news2.e.nsc.no...
> Hi
>
> My company deliver process control systems and a part of this system is
> logging of alarms and events to an SQL alarms and events database.
> We have just moved from Access to SQL Server and may have some tuning and
> architecture problems, I think..
>
> There is one application writing to the database and several applications
> that will read from the database.
> Existing records in the database will not be edited but new one will be
> added as soon there is a not normal situation in the process control.
>
> The old database layout is not fully normalized.  Many of the text fields
> can be moved to their own tables.
> I am experimenting with tables, relations and indexes in different new
> layout to make the database so fast as possible both for writinig and
> reading.
> It is very important that the writer application always have access to the
> database.  The main table must not be locked for a longer time be the
> readers.
> Also the readers must have fast access to the data in the database even
> when the main table grow to houndred millions of records.
>
> To make the testing as complite as possible the database must be filled
> with relevant data.  I have got a database from one of our customers where
> the main table has several million records with relevant data from their
> process.  I want to copy these data into a now layout and test reading and
> writing.
>
> The requrement is that it shall be possible to write bursts of data into
> the database, up to 8000 records a minute.
> This will not be the normal situation.  In a good tuned process control
> there will be less than one record each ten minute.
> -But during configuration and equipment tuning there will be a lot of
> situations that will cause an alarm or event.
> I will try to write 2000 - 8000 alarms and events each minute to the
> database, at same time as I am reading records from the database filtering
> on different fields.
> The repsons from the SQL Server shall be fast and the load on the SQL
> Server shall not be high.
> "Fast" and "High" is not fully defined yet.  What is a fast respons,  what
> is high load on the SQL Server?
> Typically I can say that the user will not want to wait for a respons for
> more than 10 seconds.
> The load on the SQL Server should not be so high that the writing
> application does not get access to it.
>
> I think normailizing the the database by putting fields that will have
> much duplicated data into their own tables should be done.
> The clustered primary key in the main table will be a combination of an
> auto number desc, time desc, milliseconds desc, sequence desc, station
> desc.
>
> I do not know about database statistics setup.   Is this important?
>
> Regards
> Kjell Arne Johansen
>
>
> "DJP" <d**@snotmail.com> skrev i melding
> news:pNkhe.2481$E7.1770@news-server.bigpond.net.au...
>> Hello Kjell
>>
>> To copy the data, probably the best thing to do is use Data
>> Transformation Services from within Enterprise Manager. Have you also
>> considered adding indexes to the old database instead of creating a new
>> one. Also, is your database setup to automatically update statistics? If
>> the database has been in use for a while, the statistics that the server
>> uses for your database may be out of date which may be causing the slow
>> queries.
>>
>> HTH
>>
>> D.
>>
>> "Kjell Arne Johansen" <kjell***@online.no> wrote in message
>> news:SXihe.10020$SL4.226519@news4.e.nsc.no...
>>> Hi
>>>
>>> The old database layout is very slow on queries so I have created a new
>>> layout and want to copy the data from the old database into the new
>>> layout.
>>> How can this best be done?
>>> Do I need an application do this or can it be done by the SQL Server
>>> running a stored procedure located in one of the databases?
>>>
>>> Can you give me an example of a script copying data from one table in
>>> one database to a table in another database?
>>>
>>> Most of the tables are the same as before but there is one table that
>>> have been normalized.  Text fields have been moved to their own tables
>>> and referenced from the main table.
>>>
>>> Thank You for your help.
>>>
>>> Regards
>>> Kjell Arne Johansen
>>>
>>>
>>
>>
>
>

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> skrev i melding
news:ODz6QoIWFHA.3840@tk2msftngp13.phx.gbl...
> Sounds like you just need to hire a competent databasedesigner to work
> with you for a few days to get the schema correct.  What you are asking
> SQL Server to do is quite reasonable and should not be a problem with
> proper normalization and indexing.  Your PK sounds a bit much.  If you
> have an autonumber then it should be unique on its own.  And SQL Server
> only has a Datetime datatype which includes values down to the millisecond
> so you don't need several columns for this.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "Kjell Arne Johansen" <kjell***@online.no> wrote in message
> news:lllhe.9690$ai7.234663@news2.e.nsc.no...
>> Hi
>>
>> My company deliver process control systems and a part of this system is
>> logging of alarms and events to an SQL alarms and events database.
>> We have just moved from Access to SQL Server and may have some tuning and
>> architecture problems, I think..
>>
>> There is one application writing to the database and several applications
>> that will read from the database.
>> Existing records in the database will not be edited but new one will be
>> added as soon there is a not normal situation in the process control.
>>
>> The old database layout is not fully normalized.  Many of the text fields
>> can be moved to their own tables.
>> I am experimenting with tables, relations and indexes in different new
>> layout to make the database so fast as possible both for writinig and
>> reading.
>> It is very important that the writer application always have access to
>> the database.  The main table must not be locked for a longer time be the
>> readers.
>> Also the readers must have fast access to the data in the database even
>> when the main table grow to houndred millions of records.
>>
>> To make the testing as complite as possible the database must be filled
>> with relevant data.  I have got a database from one of our customers
>> where the main table has several million records with relevant data from
>> their process.  I want to copy these data into a now layout and test
>> reading and writing.
>>
>> The requrement is that it shall be possible to write bursts of data into
>> the database, up to 8000 records a minute.
>> This will not be the normal situation.  In a good tuned process control
>> there will be less than one record each ten minute.
>> -But during configuration and equipment tuning there will be a lot of
>> situations that will cause an alarm or event.
>> I will try to write 2000 - 8000 alarms and events each minute to the
>> database, at same time as I am reading records from the database
>> filtering on different fields.
>> The repsons from the SQL Server shall be fast and the load on the SQL
>> Server shall not be high.
>> "Fast" and "High" is not fully defined yet.  What is a fast respons,
>> what is high load on the SQL Server?
>> Typically I can say that the user will not want to wait for a respons for
>> more than 10 seconds.
>> The load on the SQL Server should not be so high that the writing
>> application does not get access to it.
>>
>> I think normailizing the the database by putting fields that will have
>> much duplicated data into their own tables should be done.
>> The clustered primary key in the main table will be a combination of an
>> auto number desc, time desc, milliseconds desc, sequence desc, station
>> desc.
>>
>> I do not know about database statistics setup.   Is this important?
>>
>> Regards
>> Kjell Arne Johansen
>>
>>
>> "DJP" <d**@snotmail.com> skrev i melding
>> news:pNkhe.2481$E7.1770@news-server.bigpond.net.au...
>>> Hello Kjell
>>>
>>> To copy the data, probably the best thing to do is use Data
>>> Transformation Services from within Enterprise Manager. Have you also
>>> considered adding indexes to the old database instead of creating a new
>>> one. Also, is your database setup to automatically update statistics? If
>>> the database has been in use for a while, the statistics that the server
>>> uses for your database may be out of date which may be causing the slow
>>> queries.
>>>
>>> HTH
>>>
>>> D.
>>>
>>> "Kjell Arne Johansen" <kjell***@online.no> wrote in message
>>> news:SXihe.10020$SL4.226519@news4.e.nsc.no...
>>>> Hi
>>>>
>>>> The old database layout is very slow on queries so I have created a new
>>>> layout and want to copy the data from the old database into the new
>>>> layout.
>>>> How can this best be done?
>>>> Do I need an application do this or can it be done by the SQL Server
>>>> running a stored procedure located in one of the databases?
>>>>
>>>> Can you give me an example of a script copying data from one table in
>>>> one database to a table in another database?
>>>>
>>>> Most of the tables are the same as before but there is one table that
>>>> have been normalized.  Text fields have been moved to their own tables
>>>> and referenced from the main table.
>>>>
>>>> Thank You for your help.
>>>>
>>>> Regards
>>>> Kjell Arne Johansen
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"Kjell Arne Johansen" <kjell***@online.no> wrote in message
news:A6phe.9699$ai7.233873@news2.e.nsc.no...
> Hi
>
> Yes, I know that the autonumber is unique. The millisecond field is also
> microseconds from our process stations.
>
> I need an index with the combination of time, and two other fields.  An
> Oracle database man in our company says I should make them clustered.
> It looks like You can have only one clustered index in the table.  Should
> the PK be the clustered index or can I decide to use the combination of
> fields as a clustered index?
>
> I am concidering hiring a database expert haveing a look at our existing
> layout.
>
> Thank You.
>
>
> Regards
> Kjell Arne Johansen
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> skrev i melding
> news:ODz6QoIWFHA.3840@tk2msftngp13.phx.gbl...
>> Sounds like you just need to hire a competent databasedesigner to work
>> with you for a few days to get the schema correct.  What you are asking
>> SQL Server to do is quite reasonable and should not be a problem with
>> proper normalization and indexing.  Your PK sounds a bit much.  If you
>> have an autonumber then it should be unique on its own.  And SQL Server
>> only has a Datetime datatype which includes values down to the
>> millisecond so you don't need several columns for this.
>>
>> --
>> Andrew J. Kelly  SQL MVP
>>
>>
>> "Kjell Arne Johansen" <kjell***@online.no> wrote in message
>> news:lllhe.9690$ai7.234663@news2.e.nsc.no...
>>> Hi
>>>
>>> My company deliver process control systems and a part of this system is
>>> logging of alarms and events to an SQL alarms and events database.
>>> We have just moved from Access to SQL Server and may have some tuning
>>> and architecture problems, I think..
>>>
>>> There is one application writing to the database and several
>>> applications that will read from the database.
>>> Existing records in the database will not be edited but new one will be
>>> added as soon there is a not normal situation in the process control.
>>>
>>> The old database layout is not fully normalized.  Many of the text
>>> fields can be moved to their own tables.
>>> I am experimenting with tables, relations and indexes in different new
>>> layout to make the database so fast as possible both for writinig and
>>> reading.
>>> It is very important that the writer application always have access to
>>> the database.  The main table must not be locked for a longer time be
>>> the readers.
>>> Also the readers must have fast access to the data in the database even
>>> when the main table grow to houndred millions of records.
>>>
>>> To make the testing as complite as possible the database must be filled
>>> with relevant data.  I have got a database from one of our customers
>>> where the main table has several million records with relevant data from
>>> their process.  I want to copy these data into a now layout and test
>>> reading and writing.
>>>
>>> The requrement is that it shall be possible to write bursts of data into
>>> the database, up to 8000 records a minute.
>>> This will not be the normal situation.  In a good tuned process control
>>> there will be less than one record each ten minute.
>>> -But during configuration and equipment tuning there will be a lot of
>>> situations that will cause an alarm or event.
>>> I will try to write 2000 - 8000 alarms and events each minute to the
>>> database, at same time as I am reading records from the database
>>> filtering on different fields.
>>> The repsons from the SQL Server shall be fast and the load on the SQL
>>> Server shall not be high.
>>> "Fast" and "High" is not fully defined yet.  What is a fast respons,
>>> what is high load on the SQL Server?
>>> Typically I can say that the user will not want to wait for a respons
>>> for more than 10 seconds.
>>> The load on the SQL Server should not be so high that the writing
>>> application does not get access to it.
>>>
>>> I think normailizing the the database by putting fields that will have
>>> much duplicated data into their own tables should be done.
>>> The clustered primary key in the main table will be a combination of an
>>> auto number desc, time desc, milliseconds desc, sequence desc, station
>>> desc.
>>>
>>> I do not know about database statistics setup.   Is this important?
>>>
>>> Regards
>>> Kjell Arne Johansen
>>>
>>>
>>> "DJP" <d**@snotmail.com> skrev i melding
>>> news:pNkhe.2481$E7.1770@news-server.bigpond.net.au...
>>>> Hello Kjell
>>>>
>>>> To copy the data, probably the best thing to do is use Data
>>>> Transformation Services from within Enterprise Manager. Have you also
>>>> considered adding indexes to the old database instead of creating a new
>>>> one. Also, is your database setup to automatically update statistics?
>>>> If the database has been in use for a while, the statistics that the
>>>> server uses for your database may be out of date which may be causing
>>>> the slow queries.
>>>>
>>>> HTH
>>>>
>>>> D.
>>>>
>>>> "Kjell Arne Johansen" <kjell***@online.no> wrote in message
>>>> news:SXihe.10020$SL4.226519@news4.e.nsc.no...
>>>>> Hi
>>>>>
>>>>> The old database layout is very slow on queries so I have created a
>>>>> new layout and want to copy the data from the old database into the
>>>>> new layout.
>>>>> How can this best be done?
>>>>> Do I need an application do this or can it be done by the SQL Server
>>>>> running a stored procedure located in one of the databases?
>>>>>
>>>>> Can you give me an example of a script copying data from one table in
>>>>> one database to a table in another database?
>>>>>
>>>>> Most of the tables are the same as before but there is one table that
>>>>> have been normalized.  Text fields have been moved to their own tables
>>>>> and referenced from the main table.
>>>>>
>>>>> Thank You for your help.
>>>>>
>>>>> Regards
>>>>> Kjell Arne Johansen
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"Kjell Arne Johansen" wrote:

> Hi
>
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new layout.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server running
> a stored procedure located in one of the databases?
>
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
>
> Most of the tables are the same as before but there is one table that have
> been normalized.  Text fields have been moved to their own tables and
> referenced from the main table.
>
> Thank You for your help.
>
> Regards
> Kjell Arne Johansen
>
>
>