generating database scripts - VERY SLOW

Has anybody else experienced how long it takes to generate scripts from
Management Studio? I'm launching the wizard (right click database/ tasks /
generate scripts). The wizard takes me through a couple of screen to set my
options. I finally get to the window where it's processing the objects.
After 10 minutes, only 3 objects have been completed. A database with
hundreds of tables and views will take hours.
Is there any way to speed this up? Right clicking on a table or view and
generating the DDL for that one object only takes a few seconds.
It was never this slow in SQL Server 2000.The original release of SSMS was, yes, very slow. I think it is a bit
better in more recent builds, have you applied SP2 to your client tools?
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:7923D6FA-827E-4C50-85EE-64CCCEE21270@.microsoft.com...
> Has anybody else experienced how long it takes to generate scripts from
> Management Studio? I'm launching the wizard (right click database/ tasks
> /
> generate scripts). The wizard takes me through a couple of screen to set
> my
> options. I finally get to the window where it's processing the objects.
> After 10 minutes, only 3 objects have been completed. A database with
> hundreds of tables and views will take hours.
> Is there any way to speed this up? Right clicking on a table or view and
> generating the DDL for that one object only takes a few seconds.
> It was never this slow in SQL Server 2000.|||Not sure what version I'm at, don't know if this is SP2 or not?
Microsoft SQL Server Management Studio 9.00.3042.00|||That's SP2.
You can always find the service pack level by running
Select ServerProperty('ProductLevel')
Tom
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>|||> That's SP2.
> You can always find the service pack level by running
> Select ServerProperty('ProductLevel')
(But that doesn't tell you the version of client tools...)|||Several connect items I have reviewed seem to indicate that Microsoft thinks
they have solved this problem (or in a couple of cases, they mention some of
the issues brought up in the item, but not the speed). It is unclear after
reading them all whether they think they have fixed this in SP2 or the
typically vague "future release(s)"...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125313
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=228719
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126912
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262072
There are probably others; I did not perform an exhaustive search. Just
wanted to show you that you are not alone, and that some in Redmond seem to
think that it is fixed. :-)
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>|||True. Thanks for catching that.
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJK5jXBnIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> That's SP2.
>> You can always find the service pack level by running
>> Select ServerProperty('ProductLevel')
> (But that doesn't tell you the version of client tools...)

generating database scripts - VERY SLOW

Has anybody else experienced how long it takes to generate scripts from
Management Studio? I'm launching the wizard (right click database/ tasks /
generate scripts). The wizard takes me through a couple of screen to set my
options. I finally get to the window where it's processing the objects.
After 10 minutes, only 3 objects have been completed. A database with
hundreds of tables and views will take hours.
Is there any way to speed this up? Right clicking on a table or view and
generating the DDL for that one object only takes a few seconds.
It was never this slow in SQL Server 2000.
The original release of SSMS was, yes, very slow. I think it is a bit
better in more recent builds, have you applied SP2 to your client tools?
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:7923D6FA-827E-4C50-85EE-64CCCEE21270@.microsoft.com...
> Has anybody else experienced how long it takes to generate scripts from
> Management Studio? I'm launching the wizard (right click database/ tasks
> /
> generate scripts). The wizard takes me through a couple of screen to set
> my
> options. I finally get to the window where it's processing the objects.
> After 10 minutes, only 3 objects have been completed. A database with
> hundreds of tables and views will take hours.
> Is there any way to speed this up? Right clicking on a table or view and
> generating the DDL for that one object only takes a few seconds.
> It was never this slow in SQL Server 2000.
|||Not sure what version I'm at, don't know if this is SP2 or not?
Microsoft SQL Server Management Studio 9.00.3042.00
|||That's SP2.
You can always find the service pack level by running
Select ServerProperty('ProductLevel')
Tom
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>
|||> That's SP2.
> You can always find the service pack level by running
> Select ServerProperty('ProductLevel')
(But that doesn't tell you the version of client tools...)
|||Several connect items I have reviewed seem to indicate that Microsoft thinks
they have solved this problem (or in a couple of cases, they mention some of
the issues brought up in the item, but not the speed). It is unclear after
reading them all whether they think they have fixed this in SP2 or the
typically vague "future release(s)"...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125313
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=228719
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126912
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262072
There are probably others; I did not perform an exhaustive search. Just
wanted to show you that you are not alone, and that some in Redmond seem to
think that it is fixed. :-)
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>
|||True. Thanks for catching that.
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJK5jXBnIHA.1212@.TK2MSFTNGP05.phx.gbl...
> (But that doesn't tell you the version of client tools...)

generate report very slow?

I have posted about slow performance on report
now i create the secondary filegroup(2nd harddisk)--Account Ledge table
and put one table(684846 rows of data) from primary to secondary filegroup
but seems not improved a lot
any other idea '?You should check your query and indexes first. Maybe you are ordering
according to some expression that is not supported by an index? Maybe your
Where and Join expressions are not supported by indexes? Just by the info
you gave us it is hard to tell where the problem lies. You should include
DDL statements (Create table etc.), sample data and query you are using.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:ejom%23G2KEHA.1612@.TK2MSFTNGP12.phx.gbl...
> I have posted about slow performance on report
> now i create the secondary filegroup(2nd harddisk)--Account Ledge table
> and put one table(684846 rows of data) from primary to secondary filegroup
> but seems not improved a lot
> any other idea '?
>

generate report very slow?

I have posted about slow performance on report
now i create the secondary filegroup(2nd harddisk)--Account Ledge table
and put one table(684846 rows of data) from primary to secondary filegroup
but seems not improved a lot
any other idea '?You should check your query and indexes first. Maybe you are ordering
according to some expression that is not supported by an index? Maybe your
Where and Join expressions are not supported by indexes? Just by the info
you gave us it is hard to tell where the problem lies. You should include
DDL statements (Create table etc.), sample data and query you are using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:ejom%23G2KEHA.1612@.TK2MSFTNGP12.phx.gbl...
> I have posted about slow performance on report
> now i create the secondary filegroup(2nd harddisk)--Account Ledge table
> and put one table(684846 rows of data) from primary to secondary filegroup
> but seems not improved a lot
> any other idea '?
>

generate report very slow?

I have posted about slow performance on report
now i create the secondary filegroup(2nd harddisk)--Account Ledge table
and put one table(684846 rows of data) from primary to secondary filegroup
but seems not improved a lot
any other idea ??
You should check your query and indexes first. Maybe you are ordering
according to some expression that is not supported by an index? Maybe your
Where and Join expressions are not supported by indexes? Just by the info
you gave us it is hard to tell where the problem lies. You should include
DDL statements (Create table etc.), sample data and query you are using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:ejom%23G2KEHA.1612@.TK2MSFTNGP12.phx.gbl...
> I have posted about slow performance on report
> now i create the secondary filegroup(2nd harddisk)--Account Ledge table
> and put one table(684846 rows of data) from primary to secondary filegroup
> but seems not improved a lot
> any other idea ??
>

generate a xml file from sql server 2000?

hi there.

i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?

generate xml and cache it and use the xml file rather going to sql server database?

has anybody have any help on this?

the steps invloved:

1) first generate a xml file from table something like this:

select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?
SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?
SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?

which one should i use and how do i access after i gnerate a xml file

thanks.

Do you have a PrimaryKey (or clustered index) on that table? Yes, access such a large table in SQL2000 database would be slow, but you can speed up query by using clustered index seek, which I believe should be much quicker than accessing XML file. You can take a look at this link to learn more about clustered index:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp

|||

i do have primary keys and culstered index

but is that true that, if i access xml file it will boost my access speed?

i can cache my xml file and use from cache?

|||

My recomendation is run some test and look for these two System stored procs in the Master database sp_xml_preparedocument and sp_xml_removedocument because you can use them to do a lot of things with XML in SQL Server 2000. The links below show you several options including the Dataset.ReadXml method. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

http://forums.asp.net/thread/1024186.aspx

|||

after i run the below code:

DECLARE @.hdoc int
DECLARE @.doc varchar(2000)
SET @.doc = '
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE root
[<!ELEMENT root (Customers)*>
<!ELEMENT Customers EMPTY>
<!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]>
<root>
<Customers CustomerID="ALFKI" ContactName="Maria Anders"/>
</root>'

EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc

what do i do after that?

|||The second page of the thread I gave include a sample using repeater and the ReadXml method of the dataset. Hope this helps.