Copy to SQL2000 made command slow?

Hi,
I took a copy of a SQL7-Database with DTS to an SQL2000 Server.
The new copy of the SQL2000 runns fine. Also the application useing the new
DB.
But some of the SQL Commands became very solw (<1s => >200s).
Is it neceserry to optimize all the SQL-Commands - what e horror.
Thanks a lot
Andreas
Here the commands and the tabledef:
SELECT AG.GuidID, AG.Bezeichnung1 FROM
(Artikel A INNER JOIN StrukturArtikel SA ON A.GuidID = SA.ArtikelID)
INNER JOIN Attributgruppen AG ON A.AttributGruppenID = AG.GuidID
GROUP BY AG.GuidID, AG.Bezeichnung1, SA.StrukturKatalogID
HAVING SA.StrukturKatalogID = '5269C032AB1011D787D20000CB531E4D' ORDER BY
AG.Bezeichnung1
=== Aufbau Tabelle A======== CREATE TABLE [dbo].[Artikel] (
[GuidID] [char] (32) NOT NULL ,
[ArtikelNr] [varchar] (32) NULL ,
[AttributGruppenId] [char] (32) NULL ,
[VariationsAttribut] [char] (32) NULL ,
[Varianz] [varchar] (50) NULL ,
[Bezeichnung1] [varchar] (80) NULL ,
[BeschreibungLang1] [text] NULL ,
[VariationsBlockID] [char] (32) NULL ,
[Bezeichnung2] [varchar] (80) NULL ,
[Bezeichnung3] [varchar] (80) NULL ,
[Bezeichnung4] [varchar] (80) NULL ,
[Bezeichnung5] [varchar] (80) NULL ,
[BeschreibungLang2] [text] NULL ,
[BeschreibungLang3] [text] NULL ,
[BeschreibungLang4] [text] NULL ,
[BeschreibungLang5] [text] NULL ,
[anlage_datum] [datetime] NULL ,
[anlage_zeichen] [char] (16) NULL ,
[aender_datum] [datetime] NULL ,
[aender_zeichen] [char] (16) NULL ,
[ArtikelNrLief1] [varchar] (32) NULL ,
[Lieferant1] [varchar] (8) NULL ,
[ArtikelNrLief2] [varchar] (32) NULL ,
[Lieferant2] [varchar] (8) NULL ,
[ArtikelNrLief3] [varchar] (32) NULL ,
[Lieferant3] [varchar] (8) NULL ,
[Freigabe1] [tinyint] NULL ,
[Freigabe2] [tinyint] NULL ,
[Freigabe3] [tinyint] NULL ,
[Tag1] [varchar] (16) NULL ,
[Tag2] [varchar] (16) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE INDEX [ArtikelNr] ON [dbo].[Artikel]([ArtikelNr]) WITH FILLFACTOR
= 90 ON [PRIMARY]
CREATE INDEX [VariationsAttribut] ON [dbo].[Artikel]([VariationsAttribut])
WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [AttributGruppenId] ON [dbo].[Artikel]([AttributGruppenId])
WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [ArtikelNrLief1] ON [dbo].[Artikel]([ArtikelNrLief1]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [ArtikelNrLief2] ON [dbo].[Artikel]([ArtikelNrLief2]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [ArtikelNrLief3] ON [dbo].[Artikel]([ArtikelNrLief3]) WITH
FILLFACTOR = 90 ON [PRIMARY]
=== Aufbau Tabelle AG======== CREATE TABLE [dbo].[AttributGruppen] (
[GuidID] [char] (32) NOT NULL ,
[Reihenfolge] [int] NULL ,
[Ebene] [int] NULL ,
[Bezeichnung1] [varchar] (1024) NULL ,
[Bezeichnung2] [varchar] (1024) NULL ,
[Bezeichnung3] [varchar] (1024) NULL ,
[Bezeichnung4] [varchar] (1024) NULL ,
[Bezeichnung5] [varchar] (1024) NULL ,
[anlage_datum] [datetime] NULL ,
[anlage_zeichen] [char] (16) NULL ,
[aender_datum] [datetime] NULL ,
[aender_zeichen] [char] (16) NULL ,
[AttributGruppenNummer] [varchar] (15) NULL
) ON [PRIMARY]
CREATE INDEX [Ebene] ON [dbo].[AttributGruppen]([Ebene]) WITH FILLFACTOR
= 90 ON [PRIMARY]
=== Aufbau Tabelle SA======== CREATE TABLE [dbo].[StrukturArtikel] (
[StrukturID] [char] (32) NOT NULL ,
[StrukturKatalogID] [char] (32) NOT NULL ,
[ArtikelID] [char] (32) NOT NULL ,
[Katalogspezifisch] [bit] NULL ,
[Reihenfolge] [int] NULL ,
[Parent] [char] (32) NULL ,
[Ebene] [int] NULL ,
[anlage_datum] [datetime] NULL ,
[anlage_zeichen] [char] (16) NULL ,
[aender_datum] [datetime] NULL ,
[aender_zeichen] [char] (16) NULL
) ON [PRIMARY]
CREATE INDEX [ArtikelID] ON [dbo].[StrukturArtikel]([ArtikelID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [Parent] ON [dbo].[StrukturArtikel]([Parent]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [StrukturID] ON [dbo].[StrukturArtikel]([StrukturID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [StrukturKatalogID] ON
[dbo].[StrukturArtikel]([StrukturKatalogID]) WITH FILLFACTOR = 90 ON
[PRIMARY]Did you update stats after copying everything over? When
you say new application, you mean it was only converted
for SQL2K? or are there logic changes also? Compare
that all indexing is carried over, if it was supposed to
be!?! Beyond that, give SQL Profiler a shot to see the
exact slow statements and take it from there... Bruce
>--Original Message--
>Hi,
>I took a copy of a SQL7-Database with DTS to an SQL2000
Server.
>The new copy of the SQL2000 runns fine. Also the
application useing the new
>DB.
>But some of the SQL Commands became very solw (<1s =>
>200s).
>Is it neceserry to optimize all the SQL-Commands - what
e horror.
>Thanks a lot
>Andreas
>
>Here the commands and the tabledef:
>SELECT AG.GuidID, AG.Bezeichnung1 FROM
>(Artikel A INNER JOIN StrukturArtikel SA ON A.GuidID =SA.ArtikelID)
>INNER JOIN Attributgruppen AG ON A.AttributGruppenID =AG.GuidID
>GROUP BY AG.GuidID, AG.Bezeichnung1, SA.StrukturKatalogID
>HAVING SA.StrukturKatalogID
= '5269C032AB1011D787D20000CB531E4D' ORDER BY
>AG.Bezeichnung1
>
>=== Aufbau Tabelle A========>CREATE TABLE [dbo].[Artikel] (
> [GuidID] [char] (32) NOT NULL ,
> [ArtikelNr] [varchar] (32) NULL ,
> [AttributGruppenId] [char] (32) NULL ,
> [VariationsAttribut] [char] (32) NULL ,
> [Varianz] [varchar] (50) NULL ,
> [Bezeichnung1] [varchar] (80) NULL ,
> [BeschreibungLang1] [text] NULL ,
> [VariationsBlockID] [char] (32) NULL ,
> [Bezeichnung2] [varchar] (80) NULL ,
> [Bezeichnung3] [varchar] (80) NULL ,
> [Bezeichnung4] [varchar] (80) NULL ,
> [Bezeichnung5] [varchar] (80) NULL ,
> [BeschreibungLang2] [text] NULL ,
> [BeschreibungLang3] [text] NULL ,
> [BeschreibungLang4] [text] NULL ,
> [BeschreibungLang5] [text] NULL ,
> [anlage_datum] [datetime] NULL ,
> [anlage_zeichen] [char] (16) NULL ,
> [aender_datum] [datetime] NULL ,
> [aender_zeichen] [char] (16) NULL ,
> [ArtikelNrLief1] [varchar] (32) NULL ,
> [Lieferant1] [varchar] (8) NULL ,
> [ArtikelNrLief2] [varchar] (32) NULL ,
> [Lieferant2] [varchar] (8) NULL ,
> [ArtikelNrLief3] [varchar] (32) NULL ,
> [Lieferant3] [varchar] (8) NULL ,
> [Freigabe1] [tinyint] NULL ,
> [Freigabe2] [tinyint] NULL ,
> [Freigabe3] [tinyint] NULL ,
> [Tag1] [varchar] (16) NULL ,
> [Tag2] [varchar] (16) NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> CREATE INDEX [ArtikelNr] ON [dbo].[Artikel]
([ArtikelNr]) WITH FILLFACTOR
>= 90 ON [PRIMARY]
> CREATE INDEX [VariationsAttribut] ON [dbo].[Artikel]
([VariationsAttribut])
>WITH FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [AttributGruppenId] ON [dbo].[Artikel]
([AttributGruppenId])
>WITH FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [ArtikelNrLief1] ON [dbo].[Artikel]
([ArtikelNrLief1]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [ArtikelNrLief2] ON [dbo].[Artikel]
([ArtikelNrLief2]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [ArtikelNrLief3] ON [dbo].[Artikel]
([ArtikelNrLief3]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
>
>=== Aufbau Tabelle AG========>CREATE TABLE [dbo].[AttributGruppen] (
> [GuidID] [char] (32) NOT NULL ,
> [Reihenfolge] [int] NULL ,
> [Ebene] [int] NULL ,
> [Bezeichnung1] [varchar] (1024) NULL ,
> [Bezeichnung2] [varchar] (1024) NULL ,
> [Bezeichnung3] [varchar] (1024) NULL ,
> [Bezeichnung4] [varchar] (1024) NULL ,
> [Bezeichnung5] [varchar] (1024) NULL ,
> [anlage_datum] [datetime] NULL ,
> [anlage_zeichen] [char] (16) NULL ,
> [aender_datum] [datetime] NULL ,
> [aender_zeichen] [char] (16) NULL ,
> [AttributGruppenNummer] [varchar] (15) NULL
>) ON [PRIMARY]
> CREATE INDEX [Ebene] ON [dbo].[AttributGruppen]
([Ebene]) WITH FILLFACTOR
>= 90 ON [PRIMARY]
>
>=== Aufbau Tabelle SA========>CREATE TABLE [dbo].[StrukturArtikel] (
> [StrukturID] [char] (32) NOT NULL ,
> [StrukturKatalogID] [char] (32) NOT NULL ,
> [ArtikelID] [char] (32) NOT NULL ,
> [Katalogspezifisch] [bit] NULL ,
> [Reihenfolge] [int] NULL ,
> [Parent] [char] (32) NULL ,
> [Ebene] [int] NULL ,
> [anlage_datum] [datetime] NULL ,
> [anlage_zeichen] [char] (16) NULL ,
> [aender_datum] [datetime] NULL ,
> [aender_zeichen] [char] (16) NULL
>) ON [PRIMARY]
> CREATE INDEX [ArtikelID] ON [dbo].[StrukturArtikel]
([ArtikelID]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [Parent] ON [dbo].[StrukturArtikel]
([Parent]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [StrukturID] ON [dbo].[StrukturArtikel]
([StrukturID]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [StrukturKatalogID] ON
>[dbo].[StrukturArtikel]([StrukturKatalogID]) WITH
FILLFACTOR = 90 ON
>[PRIMARY]
>
>.
>

Copy table Structure including primary keys, index etc.

Hi all,

I was wondering if there is a SQL command to copy the table structure of a table that includes primary keys, foreign keys, indexes, etc.

Thanks and have a nice day to all

Not a SQL command, but you can script this stuff out using the tools by right clicking the table, or programatically using SMO.

|||can you post a sample script or SMO please or send me a link discuss this matter thanks|||

Hi,

The easiest way to create the script is to right click the original table within SQL Server Management Studio and select "Script Table As ...\ Create To\ ..." . This will create a script for the table and its indexes.

Then you need to run the script but with the new tablename. After that, you need to copy the records using a insert/select command. (it is best to set the constraints/indexes afterwards).

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

I was thinking to use that script in my SP, On my SP I add a linked server then i want to copy all the tables exactly the same

and on the linked server the table names and table count change everyday, but i dont have any problem with that.

I was thinking if there is a way to copy exactly the same table inside an SP in that case.

Thanks

|||You can use sys tables/views inside your SP|||

can you post your samples script please.

thanks

|||

SELECT * INTO NewEmployee FROM Employee WHERE 1 = 0

above query will create same structure table called NewEmployee with structure of Employee. But will not have triggers and primary keys etc. you can create them by using follwing scripts

SELECT *

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee')

SELECT *

FROM syscomments

WHERE id IN ( SELECT id

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee') )

|||thanks for the reply Dinesh. Nice sql Stmt, does this work when you have a MS Access linked Server, is there a sysobjects table on the linked server?